Can Execute Procedure But Job That Runs Procedure Errors?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Can Execute Procedure But Job That Runs...

Can Execute Procedure But Job That Runs Procedure Errors?

Rate Topic

Display Mode

Topic Options

Author

Message

guerillaunit

Posted Tuesday, November 06, 2012 3:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329

I have a procedure that pulls data from my database and uses MSDB.DBO.sp_send_dbmail to send out e-mails. The procedure itself runs without a hitch when I use 'EXEC myprocedure'. But when I set up a job for the procedure, the job fails with the following error

'Error formating query, probably invalid parameters [SQLState 42000](Error 22050). The step failed'

There is one step in the job with the TSQL statement 'EXEC myprocedure', using the database that my procedure is stored on. Does anyone know what could be causing this error?

Post #1381728

Stewart "Arturius" Campbell

Posted Wednesday, November 07, 2012 2:28 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 3,129, Visits: 4,312

Try adding the following commands to you job step before the EXEC command

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON
SET XACT_ABORT ON;
EXEC ....

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1381840

guerillaunit

Posted Wednesday, November 07, 2012 7:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329

I've narrowed the problem down. It's something with the Exchange server I use. I was using a domain address (ie. mail.mycompany.com) as the 'Server name' under Database Mail's account configuration wizard. I was unable to send e-mails to listservs and external users using this domain address. I talked to our Exchange guy and he recommended using the actual IP address of one of the mail servers (ie. 10.123.53.53). That fixed the problem with listservs and external users, but now I am unable to send e-mails when I run my procedure using a job (the procedure itself executes properly when I manually run it). Does anyone know what criteria on our Exchange server I will need to change to fix this?

Thanks!

Post #1381997

Stewart "Arturius" Campbell

Posted Wednesday, November 07, 2012 8:21 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 3,129, Visits: 4,312

Check that the SQL agent user running the job has the neccessary permissions to create mail on the relevant exchange server(s)

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1382016

« Prev Topic | Next Topic »

Permissions