August 14, 2008 at 2:36 pm
We've been using sp_SQLSMTPMail for months, and today it decided to die with this error:
Error setting Message configuraton field "smtpserver".
CDO.Configuration.1
A dynamic link library (DLL) initialization routine failed.
I've read this happens, and when it does it can be fixed by a sql start/stop, but it comes back...and of course, our DBA has been out on medical leave for 3+ months, and I've been filling in as best I can. I'm a developer, not a DBA - I know just enough to be dangerous. 🙂
So we're looking at 2005's Database Mail. It looks good, however I saw this:
Note:
Enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.
So that means that I have to stop the SQL Server Agent, so no jobs can run? How long does this usually take? Is anything besides the jobs affected by this? Hints & suggestions welcome!
Thanks all.
August 14, 2008 at 2:57 pm
Maquis (8/14/2008)
We've been using sp_SQLSMTPMail for months, and today it decided to die with this error:Error setting Message configuraton field "smtpserver".
CDO.Configuration.1
A dynamic link library (DLL) initialization routine failed.
I've read this happens, and when it does it can be fixed by a sql start/stop, but it comes back...and of course, our DBA has been out on medical leave for 3+ months, and I've been filling in as best I can. I'm a developer, not a DBA - I know just enough to be dangerous. 🙂
So we're looking at 2005's Database Mail. It looks good, however I saw this:
Note:
Enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.
So that means that I have to stop the SQL Server Agent, so no jobs can run? How long does this usually take? Is anything besides the jobs affected by this? Hints & suggestions welcome!
Thanks all.
SQL Agent restarts are normally harmless. Just make sure it's done at a time when no jobs are scheduled to run, and the agent will be back in no time.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 14, 2008 at 3:04 pm
I usually takes less than a second.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2008 at 1:35 pm
If I get a "1" as a result from the query
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';
then I don't even need to stop the SQL Server Agent, is that correct? I can just configure the Database Mail? And that wouldn't conflict with anything else on the server...
Thanks for answering dumb questions!
August 18, 2008 at 1:37 pm
Maquis (8/18/2008)
If I get a "1" as a result from the querySELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';
then I don't even need to stop the SQL Server Agent, is that correct? I can just configure the Database Mail? And that wouldn't conflict with anything else on the server...
Thanks for answering dumb questions!
Correct on all counts.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 18, 2008 at 3:30 pm
OK, thought I was all set...but of course, it's never that easy. I get this error in the log:
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The read on the database failed. Reason: Failed to load Msxmlsql.dll.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)
HelpLink: NULL
Source: DatabaseMailEngine
When I googled the error, it appears a hotfix is required (http://robcarrol.spaces.live.com/blog/cns!73A443C1CE184B41!125.entry). Has anyone had the same issue? Did the hotfix solve the problem? It says it won't require a reboot, is that true?
Thanks again.
August 18, 2008 at 6:38 pm
I don't know if this was mentioned already...
What version are you on?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 19, 2008 at 7:08 am
Marios Philippopoulos (8/18/2008)
I don't know if this was mentioned already...What version are you on?
We're on: Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2).
August 19, 2008 at 8:02 am
Maquis (8/14/2008)
We've been using sp_SQLSMTPMail for months, and today it decided to die with this error:Error setting Message configuraton field "smtpserver".
CDO.Configuration.1
A dynamic link library (DLL) initialization routine failed.
...
I also came accross that error with our mailing store proc a month ago. While looking for the problem I also saw that Database Mail wasn't working anymore and it was also cause by a dll it wasn't able to load.
So I trace back the problem to this : http://support.microsoft.com/kb/941105/en-us
I did not apply the hotfix but used the store proc that load and keep the dll in memory (cdosys.dll for us). It's been up since Sunday (2008-08-17) so it's a bit early to say if it actually fixed it or not, but I'm pretty sure it will.
August 21, 2008 at 2:53 pm
OK, just to tie up loose ends, we did end up applying the hotfix yesterday. Of course, despite what the documentation said, it wanted a reboot of the server. We tried using the database mail just in case it would work anyway, but it didn't...so for the 2nd day in a row we came in to work at 5 am, this time to reboot the production server. Voila! Database mail is now working like a champ! Of course, so is the original smtp mail procedure, but it will probably die again and we've converted now...I've gone through the 50+ procedures that utilize mail and updated them all, also added a mail archiving job that was suggested in BOL.
Thanks to all for the help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply