So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the Linked Server has Run out of Memory exception.
Why does the fact that it is running in a job environment make it differ from running as the raw SQL code? And how would I go about solving/fixing this?
Best Answer
That might be a permission issue. When executed as a job step the code runs under the SQL Server Agent Service Account. When you execute it yourself it runs under your account.
Make sure that the QSL Server Service Account has appropriate permissions on the linked server. You can also try to impersonate that account by using
before executing your code. That might reveal additional error detail and should make troubleshooting easier.