Post reply

Database mail job failure notification

  • March 24, 2009 at 12:53 pm

    #202560

    My question is related to Database mail job failure notification. The below job step used to run successfully until last week when any job fails. I added this step has the last step, i.e when the job fails.

    sp_send_dbmail @profile_name = 'Sample'

    , @recipients = 'abc.com'

    , @copy_recipients = 'manager.com'

    , @subject = 'Nightly job has failed'

    , @body = 'Nightly business job has failed.

    Attached is the output file of the job '

    , @importance = 'HIGH'

    , @query = 'xp_cmdshell type "P:\Userdb\Output\OD.out"'

    , @attach_query_result_as_file = 1 ;

    But now, don't know for what reason, the job is not able to send any notification due to this error message.

    Message

    Executed as user: svc.servername. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

    But from QA, the above query is running find and infact I am getting noticiation. Could you please advice any reason for this. I know the job is failing at the @query option. How to correct this as I didn't face any issue until last week. Please advice.

    M&M

  • March 25, 2009 at 12:12 am

    #965811

    If you got the security settings already right only mistake I see is the quotes are in wrong place ...

    Change

    @query = 'xp_cmdshell type "P:\Userdb\Output\OD.out"'

    to

    @query = 'xp_cmdshell ''type P:\Userdb\Output\OD.out"'

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • February 10, 2010 at 3:15 pm

    #1117239

    I had the same issue with sp_send_dbmail and the @query parameter called from a SQL job. I could successfully execute sp_send_dbmail/@query param' from the Query Analyzer but it would fail when I put inside a job. Drove me nuts ! I eventually discovered a solution where if I explicitly added to SQL Server the domain account the SQL Agent was running under, xp_loginfo would now return a result set for the SQL Agent account and my problem with sp_send_dbmail executing successfully from a job was fixed. The SQL Agent account was originally configured to access SQL Server through a windows domain group; that doesn't work. I had to explicitly add our SQL Agent account to SQL Server and assign Admin privileges. Very frustrating problem. Hope this helps all my fellow DBAs ! -CqlBoy

    CQLBoy

  • February 10, 2010 at 4:57 pm

    #1117284

    Blake,

    Thanks for sharing. I will try this on my server.

    In fact, I had removed the @query parameter. Will try it out again. 😀

    M&M

  • April 30, 2010 at 9:32 am

    #1160530

    Thanks blake... this did the trick... had to add the domain account as a sysadmin to get this to work... its a dev box, but what a pain in the bum.

  • November 16, 2010 at 8:48 am

    #1251377

    CQLBoy,

    Thanks for your post, it solved my issue. I posted your solution as a work around on microsoft connect site.

    https://connect.microsoft.com/SQLServer/feedback/details/361954/sp-send-dbmail-fails-when-query-parameter-and-ole-automation-is-used-in-same-batch?wa=wsignin1.0#

    Thanks,

    Srini

  • November 30, 2010 at 2:32 pm

    #1257051

    This is because you're using xp_cmdshell. The Agent service needed sysadmin privileges in order to run xp_cmdshell.

  • January 4, 2011 at 5:05 am

    #1268869

    Hi Kindly let me know 'how to add domain account used to calll the SP (calling sp_send_dbmail) to the Server as a login'

    I dont understand what should be done ... please let me know...

  • January 4, 2011 at 11:54 am

    #1269150

    I think it is lass wasteful of people's time, and you would get better responses by asking a detailed question in a new thread instead of latching on to a 2 year old thread.

  • February 11, 2011 at 8:06 am

    #1285731

    Adding the sql agent account fixed the problem for me. I had to add sysadmin privileges ...

  • March 16, 2011 at 3:35 pm

    #1299688

    service acct as sysadmin worked for me as well. Does anyone know if there are lesser priv's that work?

  • May 31, 2011 at 12:14 pm

    #1332189

    Adding the account that sql server agent runs under to the databaseMailUserRole in msdb and the db_datareader role of the db (or dbs) where sp_send_dbmail is pulling data has always worked for me.

  • June 24, 2011 at 8:50 am

    #1343724

    I just wanted to add my 2 cents to this thread for others.

    Whilst adding the account as sysadmin will get it to work, it is not a very good solution. If that does solve the problem for you, then it is a permissions issue. Whilst the error you get in SQL Agent does not tell you where the problem is, there is a fairly easy way to determine the problem.

    Do a 'run as' on SQL Management Studio, using the account that SQL Agent is running under to launch it. This will give you access to the database as SQL Agent sees it. Then, simply run your code and look at the error. It will tell you where the problems is, then you can correct the issue.

    Obviously if your code uses something that is only granted to sysadmin, such as xp_cmdshell then you don't have much choice, but if it is a simple issue with table, view, stored proc, function permissions, it is very easy to address without granting sysadmin.

    cheers, Mark

  • August 9, 2012 at 2:36 pm

    #1524122

    Fixed my problem after reading this post. ..

    thank you guys for sharing thoughts...

    Thanks thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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