sp_OA.../CDO - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_OA.../CDO

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2005-06-14 : 09:15:37

I have this procedure to create and send emails:

CREATE PROCEDURE dbo.KASPemailSendHTML
@from varchar(100),
@to varchar(100),
@body varchar(8000),
@subject varchar(200) = null,
@cc varchar(100) = null,
@bcc varchar(100) = null

AS
-- this procedure sends an email based on the passed parameters.

declare @MailID int, @hresult int

exec @hresult = sp_OACreate 'CDO.Message', @MailID OUT
if @hresult = 0
begin
exec @hresult = sp_OASetProperty @MailID, 'From',@from
exec @hresult = sp_OASetProperty @MailID, 'HTMLBody', @body
exec @hresult = sp_OASetProperty @MailID, 'BCC',@bcc
exec @hresult = sp_OASetProperty @MailID, 'CC', @cc
exec @hresult = sp_OASetProperty @MailID, 'Subject', @subject
exec @hresult = sp_OASetProperty @MailID, 'To', @to
exec @hresult = sp_OAMethod @MailID, 'Send', NULL
exec @hresult = sp_OADestroy @MailID
end
GO

I realise the sp_OA... extended procedures in the 'master' database need the right permissions. Normally this works fine but at a few sites (running windows 2000 and 2003) the procedure fails to create a file in inetpub/mailroot/pickup. Is there any way that CDO might not be installed correctly?

Any ideas?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-14 : 16:20:05

Depending on the Outlook client that you have installed on your SQL Server, it might not be installed at all. I believe it is normally not installed with Office 2003.

You can install it by hand:
1.Copy CDO.DLL to the C:\windows\system32 or C:\winnt\system32 directory on the target system.
2. Open a command prompt on that system
3. Run the following command:
regsvr32 C:\WINDOWS\system32\cdo.dll
or
regsvr32 C:\winnt\system32\cdo.dll

CODO ERGO SUM

iminore
Posting Yak Master

141 Posts

Posted - 2005-06-15 : 09:58:24

CDO is a basic part of Windows - it doesn't need Outlook. One of our clients is able to create an email by using CDO in an ASP page but not via the procedure above. It would seem to be a SQL problem.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-15 : 10:10:34

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp

Here is the lowdown on CDO.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources