SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 kill sessions through linked server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heliang
Starting Member

35 Posts

Posted - 08/20/2013 :  16:58:31  Show Profile  Reply with Quote
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)

Edited by - heliang on 08/20/2013 17:07:11

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/20/2013 :  18:42:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The EXEC command is normally executed at the server hosting the current connection.
See section G here http://technet.microsoft.com/en-us/library/ms188332.aspx how to execute the KILL command at the linked server.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/20/2013 :  18:44:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Perhaps section L is more appropriate?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

heliang
Starting Member

35 Posts

Posted - 08/21/2013 :  11:13:16  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

Perhaps section L is more appropriate?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  14:46:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It's not the service account you are using when executing the statement.
It's the current logged on user.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2013 :  14:47:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
But why do this via linked server?
Why not connect directly to the server where you want to kill the process?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

heliang
Starting Member

35 Posts

Posted - 08/21/2013 :  15:06:09  Show Profile  Reply with Quote
quote:
Originally posted by heliang

quote:
Originally posted by SwePeso

But why do this via linked server?
Why not connect directly to the server where you want to kill the process?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



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 [linkedserver].master.sys.sysprocesses
where loginame='xxxx'
exec(@dsql) AT "linkedservername"

Executed as user: US\serviceaccount. Incorrect syntax near 'linkservername'. [SQLSTATE 42000] (Error 102). The step failed.




Edited by - heliang on 08/21/2013 16:21:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000