Problem in executing msdb.dbo.sp_send_dbmail

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 2005 Forums

Transact-SQL (2005)

Problem in executing msdb.dbo.sp_send_dbmail

Author

Topic

rahul170
Starting Member

3 Posts

Posted - 2009-02-18 : 07:59:53

We have an automated job scheduled at clients’s databases on Sql Server 2005. The job runs some validation scripts and sends results of the scripts via email. For one client the job is running perfectly but on another client, it is giving following error:

Msg 22050, Level 16, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter

Below is the script which runs as scheduled job:

declare @mail_list varchar(100)
set @Mail_list = 'abc@xyz.com'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email_ProfileName',
@recipients = 'abc@xyz.org',
@copy_recipients = @Mail_list,
@query = 'EXEC TPRO.dbo.Data_Check_Notification_Procedure_SP',
@subject = 'Subject',
@body = 'Body Text',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Validation_Results.txt' ;

I tried running the following script:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EMAIL_ProfileName',
@recipients='abc@xyz.com',
--@query = 'select getdate()',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Subject'

If I comment @query parameter in the query above, it runs fine. But running the query with @query parameter gives the same error.

We have tried to run the job using both Windows authentication and SQL authentication

Is there any permissin related problem?

Any help on this topic will be highly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 10:04:00

have you tried with hardcoded date value? something like:-


EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'EMAIL_ProfileName', 
@recipients='abc@xyz.com', 
@query = 'select ''20090217'' AS dateval', 
@body = 'Hello', 
@body_format = 'TEXT', 
@subject = 'Subject'

rahul170
Starting Member

3 Posts

Posted - 2009-02-18 : 10:36:51

If i ran query with hard coded value, if I do not give @query parameter, its runs fine but specifying @query gives the same error

asgast
Posting Yak Master

149 Posts

Posted - 2009-02-18 : 11:08:59

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EMAIL_ProfileName',
@recipients='abc@xyz.com',
@query = 'select getdate()',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Subject'

works fine on my server

if i put SELECT GETDATE() into a separate procedure everything works fine

so i guess the problem is in your stored procedure, may be you can share its code here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:54:32

quote:
Originally posted by rahul170

If i ran query with hard coded value, if I do not give @query parameter, its runs fine but specifying @query gives the same error

nope. i think the problem is with using getdate() directly. did it work fine with @query when you passed the hardcoded value?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources