Search code examples
sql-serversql-server-2008t-sqlsql-agent-job

INSERT INTO results_table EXEC sproc from within a scheduled job

I'm trying to setup a scheduled job that with one step that would insert the results from a sproc into a table. 

INSERT INTO results_table EXEC sproc

The job executes and reports a success. Yet nothing gets inserted into a table. When I execute the same script from the SSMS the results are inserted. What might cause the problem?

** EDIT the job is owned by sa and the step is executed as dbo. All the runs in the history are reported as finished successfully. I've tried changing the step to 

INSERT INTO results_table(field_names) (SELECT values FROM table GROUP BY column_name)

and it behaves in a similar way

** EDIT the problem only occurs when I select from the master database. Selecting from other tables works fine.

Solution

  • The problem was that in the scheduled job the stored procedure was executed not in the context of master database.