sp_send_dbmail fails in SQL Server Agent Job

  • 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 🙁

  • 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

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

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

    ----------
    Ashish

  • 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....

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • 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....

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


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

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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.....

  • I wonder if it's bogging up on the Profile name.

    From here: http://msdn.microsoft.com/en-us/library/ms190307.aspx

    [ @profile_name= ] 'profile_name'

    Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

    I wonder if the SQL Agent user doesn't HAVE a default profile. Can you doublecheck?


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Craig - thanks for the reply....

    In youe extract is says : "When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user."

    But in my script I am specifying an actual profile called 'default' which is set up on the server and is working OK to send out mails.....so going on that msdn extract this is the profile that should be used....or am I reading it wrong?

  • JayK (1/4/2011)


    Hi Craig - thanks for the reply....

    In youe extract is says : "When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user."

    But in my script I am specifying an actual profile called 'default' which is set up on the server and is working OK to send out mails.....so going on that msdn extract this is the profile that should be used....or am I reading it wrong?

    No, I don't think so, but it's easy enough to test. Try setting up a job to send yourself an email with no query involved but the rest of the parameters the same. I may have jumped to a conclusion there, apologies. If you can nail down that it's the actual query, then you'll have to look at the sql agent's ability to access those tables directly.


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Eureka! So I finally solved the issue - just by trail and error.

    When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error. :w00t:

    I can't explain why - but it works!

    Thanks for your help....

  • JayK (1/5/2011)


    Eureka! So I finally solved the issue - just by trail and error.

    When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error. :w00t:

    I can't explain why - but it works!

    Thanks for your help....

    :crazy: :blink: Appreciate you posting your solution. I'll keep that in mind if my jobs ever start acting in a similarly strange fashion. I'm not sure why that works... but hey, I'll roll with it. 🙂


    - 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply