question

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 j.name = '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