Technical Article

Select, Update, Delete, Insert etc.. in many servers using 1 query

,

What if your boss wants you to insert or updated a record in a table in several servers? lets say 50 server what would you do? Open each and every servers and run the insert or update? that will take time if you have several servers. Let this script do the job for you..... All you have to do is to linked that server to the server where you will run the script,create this procedure, run this script and whalaaaaa done....

NOTE: for you to use this script first modify the query by changing single quotes with double quotes

If your query is

select name from master..sysdatabases where name = 'master'

when applying it to the script it must be

select name form master..sysdatabase where name = ''master''

example query :

syntax usp_linked_query 'db_name','db_owner','query'

operation: usp_linked_query 'master','dbo','select name from sysdatabases where name = ''master'''

update example

usp_linked_query 'db_name','db_owner','update table_name set col_name = ''value'' where condition'

*Always change single quotes to double quotes and enclose the whole query with ' '

/**********************************************************************
Created by : Lester A. Policarpio
Date : January 4, 2008
Email : policarpiol@asiaunited.com
Use : Run queries in all linked servers
**********************************************************************/CREATE PROCEDURE usp_linked_query
(@dbname nvarchar(500),@dbowner nvarchar(500),@statement nvarchar(4000))
AS
DECLARE @linkedserver varchar(800)
DECLARE @linked varchar(8000) 
DECLARE @command nvarchar(500)

SET @statement = REPLACE(@statement,'''','''''')
SET @command = 'DECLARE @statement nvarchar(4000)
SET @statement = '+''''+@statement+''''


DECLARE linkedserver CURSOR FOR
SELECT srvname FROM master..sysservers WHERE srvstatus = 1249 
OPEN linkedserver
FETCH NEXT FROM linkedserver INTO @linkedserver
WHILE (@@FETCH_STATUS = 0)
BEGIN
Print 'Linked Server Is : '+@linkedserver
print '-----------------------------------------------------------------'
SET @linked = 'exec '+'"'+@linkedserver+'"'+'.'+@dbname+'.'+@dbowner+'.sp_executesql @statement'
exec (@command+' '+@linked)
Print ' '

FETCH NEXT FROM linkedserver INTO @linkedserver
END
CLOSE linkedserver
DEALLOCATE linkedserver




GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.71 (7)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.71 (7)

You rated this post out of 5. Change rating

Related content