Linked Server Has Run Out Of Memory

Question

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?

Answer 1

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

EXECUTE AS LOGIN='Domain\ServiceAccount';

before executing your code. That might reveal additional error detail and should make troubleshooting easier.

Answer 2

Developed a workaround to call the stored procedure from a batch file and execute it through the command line. Details can be found here, summary below:

---

I researched this profusely, but to no avail. The few solutions I found were too risky to attempt on a production machine, so I conjured an alternative method. I created a batch file containing the SQL Commands to be executed, then plugged its execution into the Job Schedule already existing in the server. I did something like this:

1. In a standard text file, I used the following code to execute the Stored Procedure to be called: sqlcmd -Q “exec ServerName.DatabaseName.dbo.ProcedureName” timeout /t 5
   This allows the execution of the stored procedure from the system, through the command line. The timeout was for testing purposes to see the logs, but is not necessary.

2. In the Job commands, I wrote the following line to look for the batch file and execute accordingly: exec master..xp_cmdshell ‘filePath\batchFileName.bat
   This once again called from the system level, to execute the batch file at the given path with the given name.

3. In the Stored procedure, I put print statements, which get logged in the Job History to properly log the execution details for auditing purposes. It hasn't failed since.