Use Xp_send mail to run Store Proc

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)

Use Xp_send mail to run Store Proc

Author

Topic

khana04
Starting Member

4 Posts

Posted - 2007-01-22 : 00:24:28

I am using xp_sendmail to send the result of store procedure
dbo.Preview_JE to e-mail as and excel attachment but I am getting an error
"ODBC error 2812 (42000) Could not find stored procedure 'dbo.Preview_JE'. not sure what I am doing wrong.below is my syntax

EXEC master.dbo.xp_SendMail @recipients = 'Azeem.khan29@t-mobile.com'
, @message = 'See attached file.'
, @query = 'exec dbo.Preview_JE'
, @attachments = 'JEPreview.xls'
, @subject = 'Journal Entry'
, @attach_results = 'true'

Thanks for help in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 01:09:15

Most probably that stored procedure does not exist in the current database.
Prefix the dbo.Preview_JE stored procedure with the right database, like this

EXEC master.dbo.xp_SendMail @recipients = 'Azeem.khan29@t-mobile.com'
, @message = 'See attached file.'
, @query = 'exec xyz.dbo.Preview_JE'
, @attachments = 'JEPreview.xls'
, @subject = 'Journal Entry'
, @attach_results = 'true'

Or try this approach

CREATE TABLE ##texttab (c1, c2, c3 ...)
INSERT ##texttab
exec xyz.dbo.Preview_JE

DECLARE @cmd varchar(56)
SET @cmd = 'SELECT * FROM ##texttab'

EXEC master.dbo.xp_SendMail @recipients = 'Azeem.khan29@t-mobile.com'
, @message = 'See attached file.'
, @query = @cmd
, @attachments = 'JEPreview.xls'
, @subject = 'Journal Entry'
, @attach_results = 'true'
, @no_header= 'TRUE'

DROP TABLE ##texttab

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources