从计划作业中 INSERT INTO results_table EXEC sproc
我正在尝试设置一项计划作业，只需一步即可将存储过程中的结果插入到表中。
INSERT INTO results_table EXEC sproc
该作业执行并报告成功。然而，没有任何内容被插入到表中。当我从 SSMS 执行相同的脚本时，结果将被插入。什么可能导致问题？
** 编辑该作业由 sa 所有，并且该步骤作为 dbo 执行。历史记录中的所有运行均报告为已成功完成。我尝试将步骤更改为
INSERT INTO results_table(field_names) (SELECT values FROM table GROUP BY column_name)
，它的行为方式类似
** 编辑仅当我从主数据库中选择时才会出现问题。从其他表中选择效果很好。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
检查您是否正在主数据库或要插入的数据库中插入。或者在作业步骤内使用数据库实例调用 SP
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
您是否尝试过先将存储过程的结果插入到临时表中，然后将它们插入到 results_table 中？我建议以及这篇文章深入回顾这个概念： http://www.sommarskog .se/share_data.html
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
问题在于，在计划作业中，存储过程不是在主数据库的上下文中执行的。
The problem was that in the scheduled job the stored procedure was executed not in the context of master database.