SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP to send email and update record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
51 Posts

Posted - 09/06/2012 :  10:22:22  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/08/2012 :  17:25:11  Show Profile  Reply with Quote
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

USA
51 Posts

Posted - 09/19/2012 :  16:47:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/20/2012 :  11:50:25  Show Profile  Reply with Quote
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

USA
51 Posts

Posted - 09/25/2012 :  10:12:33  Show Profile  Reply with Quote
***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

India
52317 Posts

Posted - 09/25/2012 :  12:11:00  Show Profile  Reply with Quote
cool

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000