March 21, 2016 at 11:02 am
I have a TSQL stored procedure that runs just fine when I execute via SSMS query window, however, when I run the stored procedure via SQL Job (whether it's on-demand or scheduled) it returns 0 results. All the "Select Into" data is coming from a linked server on the same server, different instance).
The job is technically "successful" according to the Job History, but it can't get data from the linked server. Again, the linked server is on the same SQL 2008 R2 server, but on different instances. Both instances have SQLadmin@mydomain.local set as the "Log On As" for SQL Server.
Any help would be greatly appreciated. Thanks!
Recap:
- Run stored procedure via SSMS query window, get thousands of results.
- Run same stored procedure via SQL Job, it runs successfully, but returns 0 results
- Using linked server to retrieve data (same server, different instance)
March 21, 2016 at 12:52 pm
Check to see what security context the SQL Agent uses to run jobs. If it's not the same as when you run in SSMS, that might be your problem...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 23, 2016 at 12:55 pm
Hello,
Thanks for your response. The issue was due to the SQL Agent not having permissions to Dynamics CRM 2011 filtered views.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply