August 20, 2013 at 3:11 pm
I am trying to run dynamic SQL, which will kill all sessions with particular login on remote server. I have following now, but how could I do "exec(@dsql)" part against the remote SQL server?
declare @dsql nvarchar(max)=''
select @dsql = 'kill ' + cast(spid as varchar(20)) + ';' from [linkedserver].sys.sysprocesses
where loginame='XXXX'
exec(@dsql)
August 20, 2013 at 4:07 pm
EXEC (@dsql) AT linked_server
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 9:14 am
I got following error when I tried to run through query window. I am the sysadmin on both servers.
Could not connect to server 'linkservername' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .
and got this when I try to put in SQL agent. The service account is the sysadmin on both servers.
domain\serviceaccount. Incorrect syntax near 'linkservername'. [SQLSTATE 42000] (Error 102). The step failed.
any idea on this?
August 21, 2013 at 1:07 pm
I got this dynamic query working on query window, but not working in SQL agent now.
Executed as user: US\serviceaccount. Incorrect syntax near 'linkservername'. [SQLSTATE 42000] (Error 102). The step failed.
August 21, 2013 at 3:02 pm
Could you post the exact text of the job step? Please do not mask any names. And please wrap the text in the code tags you see left of the posting window.
What do you ultimately want to achieve? Why don't you run the job on the server where you want to kill the process? And why do you want to kill it in the first place? There may be better solutions.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 3:18 pm
I have a snapshot replication to populate latest data to read_only server for reporting. But some user start running reports before replication starts. This cause replication fail with can not drop table error. So I want to add a step before pushing data over, which will kill all processes with a particular login name on remote server. I got this dynamic query working on query window, but not working in SQL agent now.
Declare @dsql nvarchar(max)=''
select @dsql = COALESCE(@dsql,'')+ 'kill ' + cast(spid as varchar(20)) + ';' from [linkedservername].master.sys.sysprocesses
where loginame='xxxx'
exec(@dsql) AT "linkedservername"
Executed as user: US\serviceaccount. Incorrect syntax near 'linkedservername'. [SQLSTATE 42000] (Error 102). The step failed.
August 21, 2013 at 3:27 pm
I would probably use ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE followed by setting it back to MULTI_USER instead. Your KILL thing will not work if there are multiple users accessing the database.
The reason the job fails in Agent is that you use "" to delimit the identifier, and this only works when QUOTED_IDENTIFIER is ON. Which its should be in all contexts. However, Microsoft has in their aim to achieve maximum confusion decided that in Agent QUOTED_IDENTIFIER is off by default. Use brackets instead.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply