Share via


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

Question

Tuesday, May 18, 2010 12:06 AM

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.

 

 

All replies (8)

Tuesday, May 18, 2010 9:13 PM ✅Answered

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.

 

 


Tuesday, May 18, 2010 4:30 AM

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 5:17 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


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

 


Monday, March 18, 2013 12:38 AM | 1 vote

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


Friday, February 13, 2015 3:54 AM

I encountered the same issue.

Granted xp_sysmail_format_query, problem resolved! Thank you Moinu!


Tuesday, August 6, 2019 8:45 PM

This solution above did not solve my problem.

When i execute the following query it works perfectly but not through SQL Agent 2008 R2

DECLARE @tab char(1) = char(9)
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile',
    @recipients = 'myemail',
    @query = 'EXEC [dbname].[dbo].[SP_NAME]',  
    @subject = 'my subject',
    @body_format = 'TEXT',
    @importance = 'High',
    @attach_query_result_as_file = 1,
    @query_attachment_filename='file.csv',
    @query_result_separator=@tab,
    @query_result_no_padding=1,
    @query_result_width = 32767,
    @query_result_header=0

But all other 24 jobs  works perfectly... Only DBMail does'nt work and it's also not allowing imporsonation there for i had to create another linked server to be able to execute the above query in query window.


Tuesday, August 6, 2019 9:31 PM

Don't piggyback on nine year old threads. It is a lot better to start a new thread to describe your problem from start to end.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se