Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heliang
Starting Member
38 Posts |
 Posted - 2013-08-20 : 16:58:31
|
| 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.sysprocesseswhere loginame='XXXX'exec(@dsql) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-20 : 18:42:41
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-20 : 18:44:06
|
Perhaps section L is more appropriate?
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
heliang
Starting Member
38 Posts |
Posted - 2013-08-21 : 11:13:16
|
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? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-21 : 14:46:41
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-21 : 14:47:44
|
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 |
|
|
|
heliang
Starting Member
38 Posts |
Posted - 2013-08-21 : 15:06:09
|
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.sysprocesseswhere loginame='xxxx'exec(@dsql) AT "linkedservername"Executed as user: US\serviceaccount. Incorrect syntax near 'linkservername'. [SQLSTATE 42000] (Error 102). The step failed.
|
|
|
|
|
|
|
|
|
kill sessions through linked server