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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP to send email and update record

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2012-09-25 : 10:12:33
***SOLVED*** See *** below

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

Thanks

http://msdn.microsoft.com/en-us/library/ms189064%28v=sql.105%29.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-25 : 12:11:00
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -