sp_send_dbmail fails in SQL Server Agent Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » sp_send_dbmail fails in SQL Server Agent Job

19 posts, Page 1 of 2

1 2 »»

sp_send_dbmail fails in SQL Server Agent Job

Rate Topic

Display Mode

Topic Options

Author

Message

JayK

Posted Thursday, December 30, 2010 8:52 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:49 PM
Points: 366, Visits: 689

Hi,

I have read I think every post on the web about this problem but still no luck with a solution. I keep getting the error below when executing this code in a SQL AGENT Job in 2008 R2

CODE:

BEGIN
declare @servername nvarchar(150)
set @servername = @@servername
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'

DECLARE @SQLQuery nvarchar(250)

SET @SQLQuery = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'xxxxx@xxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @SQLQuery,
@query_result_width = 600,
@attach_query_result_as_file = 1
END

ERROR:
Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

The domain account running SQL Agent execute permissions on the msdb but that make no difference.

Any ideas? Its driving me crazy by now. Its NYE and I want to be out in the sun :-(

Post #1041262

crazy4sql

Posted Thursday, December 30, 2010 11:48 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731

Are you able to send test mail using :-
@profile_name = 'default' and
@recipients = 'xxxxx@xxxxxxx',

And I am sure you have enabled mail in advance configration.
If the above testing works then it mean mail is working with mentioned profile and need to find out the problem in rest code.

----------
Ashish

Post #1041283

Adiga

Posted Thursday, December 30, 2010 11:50 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:05 AM
Points: 1,618, Visits: 20,899

Please confirm if the SQL Server Agent service account has "sysadmin" privileges on the instance.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Post #1041284

crazy4sql

Posted Thursday, December 30, 2010 11:54 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731

Also I beleive you have table 'ERRORLOG' in database because I tried it and able to generate the text in @SQLQuery

----------
Ashish

Post #1041286

JayK

Posted Monday, January 03, 2011 3:43 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:49 PM
Points: 366, Visits: 689

I have checked and the account that is running the SQL AGENT has sysadmin priviledges on the sql server instance and also the code run Ok if I run it in the QA.

So still not too sure why this job is failing....

Post #1042094

Adiga

Posted Monday, January 03, 2011 8:14 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:05 AM
Points: 1,618, Visits: 20,899

Does changing the job owner to a "sysadmin" login help?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Post #1042131

JayK

Posted Tuesday, January 04, 2011 2:41 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:49 PM
Points: 366, Visits: 689

Unfortunately not :-( I have changed the job owner to the the same account that runs the SQL AGENT which has sysadmin privileges. The same error still persists. The whole script is shown below:

IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog

BEGIN
declare @servername nvarchar(150)
set @servername = @@servername
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'

DECLARE @SQLQuery nvarchar(250)
SET @SQLQuery = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'xxxxxxx@xxxxxxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @SQLQuery,
@query_result_width = 600,
@attach_query_result_as_file = 1
END

--== Clean up our process by dropping our temporary table. ==--
DROP TABLE tempdb.dbo.ErrorLog

When I run in QA everything works prefectly - I get an email as expected - but once I but it into a job I get the errror below:

Message
Executed as user: xxxx\xxxxxx. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

The executed as user has a server role of sysadmin and is also the owner of the job.

Reallty stumped by this one and can't seem to find a solution online....

Post #1042711

David Webb-CDS

Posted Tuesday, January 04, 2011 4:21 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 825, Visits: 5,698

Has the 'executed as' user's password expired?

And then again, I might be wrong ...
David Webb

Post #1042755

Evil Kraig F

Posted Tuesday, January 04, 2011 4:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

~~Sorry to ask the 'is the power on' question, but can you run this script from a query window and do you get the same error if you do?~~

You answered this, sorry. I missed it in my first pass through your second reply.

Break the script down command by command and find the specific point that it fails on. If it fails with a simple declare, something's wrong with the logon. If the declare works and further on it breaks, we can delve into that specific script breaking point.

Need to find out where SQL is chewing on this.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1042757

JayK

Posted Tuesday, January 04, 2011 4:53 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:49 PM
Points: 366, Visits: 689

thanks for the replies....

The Job falls over when it hits the

EXEC msdb.dbo.sp_send_dbmail
profile_name = 'default',
@recipients = 'xxxxxxx@xxxxxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @SQLQuery,
@query_result_width = 600,
@attach_query_result_as_file = 1

code. I have tried replacing the @query = @SQLQuery line with the actual SQL string but it made difference - still getting the same error message.....

Post #1042761

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions