cmprathu avatar image
cmprathu asked

exec() at linked server failing

I have a monitoring job running the following query to kill a spid in the remote server if the job runs longer than 20 mins and this was running fine all these days and suddenly started failing with the following error: OLE DB provider "SQLNCLI11" for linked server "remoteserver" returned message "Command text was not set for the command object.". Msg 7215, Level 17, State 1, Line 21 Could not execute statement on remote server 'remoteserver'. This started happening only after the local server was rebooted day before.I even dropped and recreated the linked server ,still the same error,any thoughts? IF EXISTS(SELECT * FROM [msdb].[dbo].[sysjobactivity] A Join MSDB.DBO.sysjobs J On A.job_id = J.JOB_ID where = 'I3_Abandon_Rate_Update' and stop_execution_date is null and DATEDIFF(MINUTE,start_execution_date,CURRENT_TIMESTAMP) > 20) BEGIN declare @kill varchar(25) select --r.wait_resource, @kill = 'kill ' + substring(r.wait_resource, -- SVRCMRSQLCMF (SPID=66) charindex('=',r.wait_resource,0)+1, -- start from the char after the = charindex(')',r.wait_resource,0)-charindex('=',r.wait_resource,0)-1) -- end is the char number of ')' minus the start (incase the spid is 3 digits) from sys.dm_exec_sessions s join sys.dm_exec_requests r ON s.session_id=r.session_id where s.program_name = 'SQLAgent - TSQL JobStep (Job 0x361AD8FC7E64DF4AB4FE8F6D330E498E : Step 1)' -- SQLAgent JOB: I3_Abandon_Rate_Update --print @kill exec(@kill) at remoteserver EXEC msdb.dbo.sp_send_dbmail @recipients = 'XXXXX', @subject = 'I3_Abandon_Rate_Update Stopped', @body ='The Job I3_Abandon_Rate_Update on localserver was running longer than 20 minutes, so it was stopped.' END
sql server 2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
Can you re-post the query with the line feeds, please? The '--' style of commenting makes the rest of the current line into a comment. It's impossible to tell which bits are commented out at the moment.
0 Likes 0 ·

1 Answer

Tom Staab avatar image
Tom Staab answered
I see 2 possible causes of this problem. 1. Concatenating a null will make the entire string null. 2. If the WHERE condition is not met, the query will not set @kill. Perhaps you could try something like this to test my theory: IF @kill is null RAISERROR('Null string.', 16, 1); ELSE EXEC(@kill) AT remoteserver;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cmprathu avatar image cmprathu commented ·
Thank you for your response. My issue is resolved now,I found an orphaned row in the sysjobactivity table and after I deleted that the job is running fine.Thank you.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
Glad I could help. Please mark the answer as correct so others know your problem is solved.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.