SQL Server agent job not working

Question

SQL Server agent job not working

IQ1

Right there with Babe

Points: 765
More actions
March 28, 2018 at 7:23 pm

#328124

Hi All
I am using Windows 2012 and SQL Server 2012. I am trying to execute a job in SQL Server 2012 using SQL Server agent. This job has two steps
Step 1) Calls a stored procedure
Code: exec perform_exe_proc
Step2) Calls sp_dbmail package to send an email with the output results. The code I am using for step 2) is
EXEC sp_send_dbmail
@recipients='YourName@domain.com',
@Profile_name = 'Profile you have access to',
@subject='whatever you want for the subject',
@body='whatever you want for the text in the body of the email',
@file_attachments='X:\PathTo\File.csv';
In step1) I am defining an output file path by going into the advance properties of the step, but some how when this is invoked, it fails on this step. I have tried creating a empty csv file in the location I specify, so it just puts the results into the file, but everything I have tried fails, I get an error invalid file type or unable to open file error message.
How can I make this to work, please explain.
Thanks
IQ

Viewing 6 posts - 1 through 6 (of 6 total)

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

IQ1 - Wednesday, March 28, 2018 7:23 PM
Hi All
I am using Windows 2012 and SQL Server 2012. I am trying to execute a job in SQL Server 2012 using SQL Server agent. This job has two steps
Step 1) Calls a stored procedure
Code: exec perform_exe_proc
Step2) Calls sp_dbmail package to send an email with the output results. The code I am using for step 2) is
EXEC sp_send_dbmail
@recipients='YourName@domain.com',
@Profile_name = 'Profile you have access to',
@subject='whatever you want for the subject',
@body='whatever you want for the text in the body of the email',
@file_attachments='X:\PathTo\File.csv';
In step1) I am defining an output file path by going into the advance properties of the step, but some how when this is invoked, it fails on this step. I have tried creating a empty csv file in the location I specify, so it just puts the results into the file, but everything I have tried fails, I get an error invalid file type or unable to open file error message.
How can I make this to work, please explain.
Thanks
IQ

Post the error messages please!
😎

IQ1 Right there with Babe Points: 765 More actions · Answer 2

The error messages are shown below as per request.

03/28/2018 15:37:43,test,Error,2,QSQL4TR,test,exec sp_send_dbmail,,Executed as user: ORG\mssql_service. Attachment file c:\backup\test.csv is invalid. [SQLSTATE 42000] (Error 22051)Unable to open Step output file. The step failed.,00:00:00,16,22051,,,,0
03/28/2018 15:33:12,test,Success,1,QSQL4TR,test,exec perform_Exe,,Executed as user: ORG\mssql_service. Unable to open Step output file. The step succeeded.,00:04:31,0,0,,,,0
03/28/2018 15:26:54,test,Error,,QSQ4TR,test,,,The job failed. The Job was invoked by User web1. The last step to run was step 2 (exec sp_send_dbmail). The job was requested to start at step 1 (exec perform_Exe).,00:04:03,0,0,,,,0

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

IQ1 - Thursday, March 29, 2018 12:42 AM
The error messages are shown below as per request.
03/28/2018 15:37:43,test,Error,2,QSQL4TR,test,exec sp_send_dbmail,,Executed as user: ORG\mssql_service. Attachment file c:\backup\test.csv is invalid. [SQLSTATE 42000] (Error 22051)Unable to open Step output file. The step failed.,00:00:00,16,22051,,,,0
03/28/2018 15:33:12,test,Success,1,QSQL4TR,test,exec perform_Exe,,Executed as user: ORG\mssql_service. Unable to open Step output file. The step succeeded.,00:04:31,0,0,,,,0
03/28/2018 15:26:54,test,Error,,QSQ4TR,test,,,The job failed. The Job was invoked by User web1. The last step to run was step 2 (exec sp_send_dbmail). The job was requested to start at step 1 (exec perform_Exe).,00:04:03,0,0,,,,0

The first step fails to produce the output file, make certain that the credentials used by the Sql Server Agent for running the job are sufficient for creating (r/w/d) the file.
😎

IQ1 Right there with Babe Points: 765 More actions · Answer 4

Thanks for letting me know that, How do I check that ?

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

IQ1 - Thursday, March 29, 2018 5:01 AM
Thanks for letting me know that, How do I check that ?

Check which credentials are used and then grant the permissions necessary to the credentials in the output folder
😎

Don't use administrator credentials!