Error 7221: Could not relay results of procedure from remote server

I know it’s been a while since I posted anything in my blog so I thought I would put something up today that gave me a little more trouble today then I thought it would.   There wasn’t much help on the internet for it either so perhaps this post will help others that have the same issue as I did today.  I was investigating a SQL Agent job that simply executed a stored procedure on a remote server via a linked server call.  The job was extremely sporadic.  It was running fine for weeks and then other times it would fail every day.  The query in the job when run manually in SSMS executes just fine in about 5 minutes.  The only consistent thing I was seeing was that it would only fail if it was running over 5 minutes and 37 seconds.  The error I was getting in the job failure was as follows:

Could not relay results of procedure ‘SSP_SAMPLE’ from remote server ‘LNKTEST’. [SQLSTATE 42000] (Error 7221)   [SQLSTATE 01000] (Error 7312).  The step failed.

At first glance I thought it must have been the remote query timeout setting for the server.   So in order to check this I took a look by running  sp_configure

The setting for remote query timeout (s) was set at 600.  This tells me I have the default setting which is a 10 minute timeout period for remote queries.  The query when ran manually runs for 5 minutes so that’s almost half the time of  the 10 minute timeout period so that wasn’t it.  What on earth could it have been then? Maybe it was missing a setting in the linked server or permissions, so I took a look at the security for the linked server.  Unfortunately it wasn’t going to be that easy for me either.  The login for the linked server was given the appropriate rights to execute the proc in the database and again, it was running manually just fine so it couldn’t have been permissions.  HMMMMM.  Maybe it was the RPC, or RPC Out permissions.  By default SQL sets those to false.  As you can see below though, they are set to true and again when run manually, it was working just fine.

I started investigating further and then I thought maybe it could be something else but that wasn’t it either.  I banged my head on my desk three times and then it hit me…..it was right there in front of my face.  Do you see it?  Look again?  It was the Query Timeout!  Someone set this to 337 in the linked server itself.  Normally by default this is set to 0.  I always keep it at 0 myself so I never thought to check it there.  Sometimes its just the silliest things that give us troubles, huh?  Funny thing was that there was no where on the internet where it was stated that this may be the cause.  It was just the fix for me, so if you are scowering the internet looking for a fix for the error above, and your using linked servers and can’t seem to find the issue…. just check the query timeout in the linked server itself.  It may be just the thing you were looking for.