none
SQL Server Agent Job And Database Mail - Error formatting query, probably invalid paramters

    Question

  • We have a stored procedure that we are trying to call with a SQL Server Agent Job. The stored procedure calls Database Mail. This is SQL 2008. The execute task is simply:

     

    USE [Production]
    GO
    
    EXEC [dbo].[JOB_EMAIL_SCHEDULED_REPORTS]
    
    GO

     

    When the job runs, it is erroring out immediately, with the error:

    Execute as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid paramters [SQLSTATE 42000] (Error 22050). The step failed.

    When we go into SQL Server Manager and just execute the SQL query, it works though. Something within the SQL Server Agent job,  and Database Mail is causing problems. Any ideas? Thanks.

     

     

    Tuesday, May 18, 2010 12:06 AM

Answers

  • Ok got this working here are the steps:

    Go create a user (call whatever you want) and assign them to the groups: USERS, SQLSERVERMSSQLUSER and SQLSERVERSQLAGENTUSER. Make sure you check password never expires.

    Go into SQL Server Configuation Manager

    Double click SQL Server Agent

    Change Log On from build in account NETWORK SERVICE to this account and browse and select the user you created above. Provide the password as well. Restart the SQL Server agent service.

    All good.

     

     

    • Marked as answer by JustinK101 Tuesday, May 18, 2010 9:14 PM
    Tuesday, May 18, 2010 9:13 PM

All replies

  • Justin,

    If you use SSMS then the procedure is executed under a different user when compared to running using a SQL Server Agent Job.

    Isolate the problem by running a SQL Server agent job that sends a mail. Also check if the NT Authority\Network Service account is in good standing. Look at the below thread with a similar problem that had an expired account.

    http://social.msdn.microsoft.com/Forums/en/sqltools/thread/bac7ecee-a156-4313-b532-ba15813a0700


    http://SankarReddy.com/
    Tuesday, May 18, 2010 4:30 AM
  • It is  you SQL Agent is configured running under NT AUTHORITY\NETWORK SERVICE account which is a limited service account, replace it with power domain account
    Tuesday, May 18, 2010 5:17 AM
  • Ok got this working here are the steps:

    Go create a user (call whatever you want) and assign them to the groups: USERS, SQLSERVERMSSQLUSER and SQLSERVERSQLAGENTUSER. Make sure you check password never expires.

    Go into SQL Server Configuation Manager

    Double click SQL Server Agent

    Change Log On from build in account NETWORK SERVICE to this account and browse and select the user you created above. Provide the password as well. Restart the SQL Server agent service.

    All good.

     

     

    • Marked as answer by JustinK101 Tuesday, May 18, 2010 9:14 PM
    Tuesday, May 18, 2010 9:13 PM
  • Thanks for the post.  I was also trying to run a job that executed a stored procedure that included a database mail step.

    When I granted the service account exec rights to the xp_sysmail_format_query proc, the job ran and sent me the email. 

    (I was able to identify the proc by impersonating the service account in SSMS and executing the stored procedure called by the job.)

     

    • Proposed as answer by Moinu_SQL Sunday, March 17, 2013 11:53 PM
    Monday, October 11, 2010 3:27 PM
  • Thanks for the post.  I was also trying to run a job that executed a stored procedure that included a database mail step.

    When I granted the service account exec rights to the xp_sysmail_format_query proc, the job ran and sent me the email. 

    (I was able to identify the proc by impersonating the service account in SSMS and executing the stored procedure called by the job.)

    This worked fine for me.

    1) Find if SQL server Agent runs on a domain account. If so, get the domain account details from SQL Server Configuration Manager.

    2) Create the domain account from  step (1) in SQL server and grant Sysadmin access to it.

    3) Run this command on master database.

    GRANT EXECUTE ON [xp_sysmail_format_query] TO [Give your SQL Agent Service account]

    4) Rerun the job. It would run successfully.


    MM


    • Proposed as answer by Moinu_SQL Monday, March 18, 2013 12:40 AM
    • Edited by Moinu_SQL Monday, March 18, 2013 9:59 PM
    Monday, March 18, 2013 12:38 AM