Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2007-04-25 : 08:06:15
|
Have a funny one that I've not yet resolved, and was wondering if I am the only one.SQL2005 SP1 server, Ent Ed, 64 bit, clustered machine.SP2a installed over Easter weekend.about 12 days after apparently succesful upgrade, the cluster was failed over. After that fail-over, an existing job started failing, with the following message:quote:
Error executing extended stored procedure: Invalid Parameter [SQLSTATE 42000] (Error 22050). The step failed.
All the job does is build a SQL query, then supply the query to sp_send_dbmail for execution:DECLARE @RetCode bit, @To varchar(200), @Subject varchar(100), @CC varchar(200), @Query varchar(max), @Debug smallint--cut section that has the query , but these are straight SQL --queries - no SP or XP usage whatsoeverEXEC @RetCode = msdb.dbo.sp_send_dbmail @profile_name = 'Email', @recipients = @To, @subject = @Subject, @copy_recipients = @CC, @query_result_header =0, @Query =@Query One 'funny' I noticed is that up until the failover, SSMS seemed to erport the version of the server as still the SP1 (2157). After failover, it now lists (3042). This may simply be a symptom of SSMS not auto-refreshing though. We have (I believe) failed back again since then, still no joy.Any Ideas?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 13:12:11
|
| Our current environment is "SQL2005 SP1 server, Ent Ed, 64 bit, clustered machine". I have upgraded to SP2 + GDR2 in our test and dev environments, but those aren't 64-bit and most aren't clustered. We don't use Database Mail in our non-prod environments. I'd be very interested in this as we use Databaser Mail in prod to send data to some employees via SQL jobs.Did you install sp2 on all nodes? If you didn't, the client tools wouldn't be updated on the node where sp2 wasn't running.We've got a 4-node cluster in production, so we have to launch it on one node to hit our 11 instances then also run it on the other 3 nodes to hit the tools.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-04-25 : 13:35:39
|
| Spoke to the clients senior DBA (sharp guy). Apparently there were issues installing the SP2a to the some of the live servers, so now we have 2 clusters that are in a mixed state - the active nodes are on SP2a, but the passive nodes see to be on SP1, yet some files have updated etc. They are planning to failover to the passive node for the reporting server, try and upgrade that again, and see if that fixes the issue. If it does, then they will tackle the other OLTP server (the one where my emailing of status of jobs is failing ).We are currently thinking that potentially somewhere in the failure to deploy completely, the underlying dll's used by sp_send_dbmail must have gotten out of synch with whatever is in SQL.The msn chat went along these lines:clientdba says:going to try to upgrade the passive nodeclientdba says:(SP1)clientdba says:mssqlsystemresource database is still SP1, the files are all still SP1 on A nodeclientdba says:but files have been updated to SP2 on B nodeI says:Is this one of the calls open with MS? clientdba says:nope, but I think I just need to fail over to SP1 node and re-apply the SPI says:ok - I thought you said you did that and it refused to SP because it was already done?...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 13:41:35
|
| I had all sorts of problems installing sp2 (and GDR2) on our test systems. I received all sorts of different errors. I repeatedly had to reboot, retry, review the logs... to get sp2 installed. I was about to open a case with MS PSS, but then finally it installed correctly. I think I did around 20 instances last week in our test environment and I'd say about 25% of them failed initially. A couple of them failed 2-3 times and then it finally installed correctly. I do remember that I had to fix file permissions for one of the instances. A few MDFs and LDFs had bad permissions on them somehow. I reviewed the permissions on the other files in that same directory and then applied the same permissions. I don't know how it got into that state but it did cause sp2 problems until I resolved that. I wouldn't have know to look at that except that I found the permissions error in the setup logs for sp2.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-04-26 : 11:02:15
|
| GDR2? Is that the SP2a re-release?I need tog et more detail from the clients lead DBA, but as I recall they have 1 or 2 open calls with MS regarding SP2. Anything you did to overcome would be good to know - have you per chance posted a SP2 lessons leanrt post anywhere here?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-26 : 12:52:36
|
| GDR2 is the critical hotfix for sp2 and sp2a. http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspxI don't have a lessons learned anywhere. My only recommendation would be to start in your dev/test environments. Also, be prepared for excessive downtime to do the installs. It took about an hour on each system to install. On the failed systems, it took up to 4 hours to finally get it installed successfully.Due to this, we are going to move our production applications to our disaster recovery site while we upgrade to sp2a and gdr2.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Possible issues with SP2 and sp_send_dbmail?