using CDOSYS to send a query? - 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)

using CDOSYS to send a query?

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2003-01-03 : 14:25:11

Good afternoon,

I am using CDOSYS to send messages since I don't have an exchange server(we use Groupwise)and since I upgraded to SQL2k I haven't been able to send messages via SQLMail anymore. SQL2k won't play nice with groupwise. So I have tried to use CDOSYS with limited results. I want to send a query as the text in the body of an email like I was doing before or as a text attachment at worst. But what I get is the actual query: select * from campus6_train.dbo.ChangeStatusVW

This is the code I am using:

sp_send_cdosysmail
declare @Body varchar(4000)
select @Body = 'select * from campus6_train.dbo.ChangeStatusVW'
exec sp_send_cdosysmail 'someone@landmark.edu','someoneelse@landmark.edu','Students who have left',@Body

Thanks in advance

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-01-03 : 14:36:43

I have never used CDOSYS before, but maybe this will work for you:

declare @Body varchar(4000)
declare @Results varchar(4000)
SELECT @Body = 'select * from sysobjects'
EXEC @Results = sp_sqlexec @Body
exec sp_send_cdosysmail 'someone@landmark.edu','someoneelse@landmark.edu','Students who have left', @Results

By doing it this way, @Results contains the results. Let us know if this works for you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-03 : 16:09:36

You can also give this a try.

exec sp_SMTPMail @SenderName='Joe', @SenderAddress='joe@xyz.com',
@RecipientName = 'Joe2', @RecipientAddress = 'joe2@xyz.com',
@Subject='SQL Mail Test',
@Body='This is a test message from SQL Server. Smile! It worked.',
@Path = 'c:\pathtofilegoeshere',
@FileName = 'myfile.txt'

Create Procedure sp_SMTPMail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@Path varchar(128),
@FileName varchar(128)
AS
SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'AttachFile' , @path, @filename

EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off
GO

lmayer4
Starting Member

33 Posts

Posted - 2003-01-08 : 09:33:41

I tried the first way:

declare @Body varchar(4000)
declare @Results varchar(4000)
SELECT @Body = 'select * from sysobjects'
EXEC @Results = sp_sqlexec @Body
exec sp_send_cdosysmail 'someone@landmark.edu','someoneelse@landmark.edu','Students who have left', @Results

I wasn't getting anything so I checked on the microsoft website for the sp_sqlexec. According to Microsoft its been "Removed; no longer available [in SQQL 2000]. Remove all references to sp_sqlexec."

I'm still working on the second way.

cwightman
Starting Member

6 Posts

Posted - 2003-03-25 : 08:23:01

Has anyone had any luck with this for this is something I'm needing to look at as the company does not want outlook on the Sql Server system.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources