INSERT INTO results_table EXEC sproc from within a scheduled job

Question

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.

Answer 1

Check if you are inserting in the Master database or the the database that you want to insert. Or call the SP with database instanse inside the job step

Insert Into Results_Table
EXEC <DBNAME>.<SchemaName>.<ProcedureName>

Answer 2

Have you tried inserting the results of the stored procedure into a temp table first then inserting them into your results_table? I would suggest that as well as this article which reviews this concept in-depth: http://www.sommarskog.se/share_data.html

Answer 3

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