OS: MS Server 2003 SO2
SQL: MS SQL 2005 SP2
SQL Server Management Studio: 9.00.3042.00
Summary: The stored procedure executes fine. When we set the stored procedure to execute periodically in a job agent, the agent fails. We selected the Agent to report on error (OS Event Viewer reports the standard error ID 208).
Detailed Description: In MS SQL Management Studio-SQL Server Agent-Jobs-'MyJobName'-RightClick-Properties I am configuring my SLQ job.
In the job properties under Steps-Step1-Edit-General I have following
Step name: Step 1
Type: Transact-SLQ scripts (T-SQL)
Run as: blank (!!!)
Database: Runtime (this is my custom database name)
Command: EXEC dbo._SubmersiblePumpRuntime (this is my stored procedure name)
When I click on PARSE I get a popup that tells me that parsing was successful.
In the job properties under Steps-Step1-Edit-Advanced under Run as user: I selected wide variety of users. I did try 'sa', local OS user who is also a member of the user group for the local OS user group 'SQL Server Agent'. Nothing works. When I select right click-Run from step ... and run from step 1, the job produces same error. It just fails with ID 208.
Again, the stored procedure works just fine. When we put the stored procedure to run in an agent, the agent cannot execute the procedure.
What is wrong with my setup?