Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-09-06 : 10:22:22
|
I set up a nightly job to catch a common error and change a field to the correct value. I also wanted it to email me any changes made so I created the stored procedure below and set a job to call it each night. Testing it last night, the SP made the change to the value, but I didn't get an email. If I run the email section, I get an email and the attachment, if I run the update part, it updates. If I run them together in SSMS I get an email and the order is updated. But the stored procedure runs and I get no email but the update happens. The job history gives me an error:"Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed." CREATE PROCEDURE dbo.SET_XIN AS if (select count(ordernum) from orders where orderbillto = 'XXXXXX' and orderstatus <> 'XIN' and orderdate >= '20120101')>0 BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@domain.com', @query = 'SELECT ordernum FROM DB.dbo.orders WHERE orderbillto = ''XXXXXX'' and ordertatus <> ''XIN'' and orderdate >= ''20120101''', @subject = 'Not Invoiced', @attach_query_result_as_file = 1 ; update orders set orderstatus = 'XIN' where orderbillto = 'XXXXXX' and orderstatus <> 'XIN' and orderdate >= '20120101' END |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-08 : 17:25:11
|
whats the datatype of ordernum? what type of values does it contain?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-09-19 : 16:47:33
|
Sorry I didn't check back sooner.ordernum is an integer.It looks like it's a mail permission issue with the agent and the job owner vs my local account. So when I run the SQL from SSMS, it's using my credentials and can send the email, but when the job runs via the agent, it's failing to send the email because whatever account the agent is running under can't execute the dbmail SP to send the mail.I just ran EXEC dbo.SET_XIN and the order was updated, but no email is sent, so maybe it's something with the stored procedure permissions...?I right clicked on the stored procedure and added a user role that had access to the msdb and granted execute permission and now if I EXEC dbo.SET_XIN, I get an email and the order is updated.Still a permissions problem running it via the agent, I still get: Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-20 : 11:50:25
|
why not configure a proxy account and configure sql agent job to use it instead. give required permissions to the proxy account------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-09-25 : 10:12:33
|
***SOLVED*** See *** belowTook a while, I've been reading up on proxy accounts (never used one before). In the current job, in the job step properties the type is Transact-SQL script (T-SQL) and that's all it's doing, just running some SQL, no IS/ActiveX/Replication/etc, so the msdn site listed below says: "Job steps that execute Transact-SQL do not use SQL Server Agent proxies. Transact-SQL job steps run in the security context of the owner of the job. To set the security context for a Transact-SQL job step, use the database_user_name parameter in the sp_add_jobstep stored procedure."***So it looks like I need to change the permission for the job owner. The owner was already a domain admin, but I changed the owner to my own login. Then I gave my login execute and connect rights to the msdb database, and execute rights to the stored procedure and now it works. I'll probably set up a generic user that we can use for any emailing SP's in the future and grant the same level of permissions so it's not tied to my name, but for now, seems to be working.Thankshttp://msdn.microsoft.com/en-us/library/ms189064%28v=sql.105%29.aspx |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-25 : 12:11:00
|
cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|