We have a SQL 2000 stored procedure to send notification emails using CDOSYS and OLE Automation. It has been happily sending out emails for quite a while now from both of our dev and prod machines.
The other day I added a line of code to format the message body variable. I tested the change in a T-SQL script in dev, then added the line into the procedure and recompiled it in dev using an ALTER PROC script. I then called the dev proc and everything is still good. The change has no impact to the sp_OA* commands.
So then I used the same ALTER PROC script and pointed it to production. There is no difference between the dev and prod procs so this was OK. The script ran OK and the proc was updated with the change. However, now only the prod proc doesn't work. Further, the same code in a T-SQL script also fails. But everything remains fine in the dev environment.
We restored the database that had the email SP to a point prior to the change, but the problem persists. It is as if recompiling the proc has disabled the CDOSYS capability from SQL server. CDOSYS still works from VBscript on the server.
The error message:
Msg 50000, Level 18, State 3, Procedure usp_SendEmail, Line 154
Error in Email Object: Source: CDO.Configuration.1 . Description: A dynamic link library (DLL) initialization routine failed.
(EOLIAN.Tools.dbo.usp_SendEmail)
Here's a bit of the code:
DECLARE @.iMsg int,
@.hr int
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
IF @.HR <> 0 GOTO Error_Handling
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @.HR <> 0 GOTO Error_Handling
I encountered a similar problem a few months ago when we collocated our DB server (Win2K, SQL 2005) and changed the domain it was in. At the time, granting the login that runs the SQL Server service access to the System32/InetSrv directory fixed the problem (seemed to be a metabase access issue). The one difference is that we use the Pickup directory (SendUsing=1).
A few weeks later, things stopped working again. Like you, I've tested using a VBScript logged in as the same account that's running the SQL Server service and the emails gets generated without difficulty. But using the stored proc or a pared-down SQL script generates the same DLL initialization error in CDO.Configuration.1.
This proc has been in use since we converted to SQL 2000 and has not been altered for some time (>12mo). The only change is the domain change which clearly introduced a number of security implications. However, that doesn't explain why it worked and then just stopped working.
If I figure out the problem, I'll post again. If you figure out hte problem, please post as well as we may be chasing the same issue.
|||Moving to the T-SQl group.|||The problem is resolved, although not understood. We rebooted the server and restarted SQL and all is well again. Not sure what caused the problem, or what the problem was.|||The solution was short lived. The DLL initialization failure message is back. :-(