Dynamic server connections

Question

Post reply

Dynamic server connections

Martin-202776

SSC Journeyman

Points: 84
More actions
April 12, 2005 at 4:35 am

#60251

Hi
I am trying to think of a way to create a DTS job that runs the same task on multiple servers in sucession.
I have a central server that contains details of approx 150 servers spread about the country. I want to design a generic method of connecting to each one in turn performing a task and writing the results back to my central server then stepping onto the next server to repeat the process.
So far I have got to the idea of creating a parent process to read data from the central server and dynamically update the connection properties then call a child process that does the work.
Anyone done this sort of thing before? any ideas or suggestions?
Code sample would be great !!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Daljit-208782 SSChasing Mays Points: 623 More actions · Answer 1

you can build the synamic queries using osql utility to achieve the task.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 2

assuming nothing else needs to change but the connections with a datasource of "oldservername" the following might do what you want.

strPackageName = "my package name"

arrServerNames = array("s1","s2","s3","s4")

for each strServerName in arrServerNames

With CreateObject("DTS.Package")

.LoadFromSQLServer strServerName,,,256,,,,strPackageName

.FailOnError = TRUE

for each connection in .connections

if connection.datasource = "oldservername" then _

connection.datasource = strServerName

End With

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Have you considered the use of an 'MSX' server ? It can handle scheduling and execution (along with error reporting) for all 'enlisted' servers.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Martin-202776 SSC Journeyman Points: 84 More actions · Answer 4

Thanks Rudy

Yes considered a MSX server and might well go down that route, but what I was really after was some method of creating a generic process that I could use as a template for doing adhoc tasks on all the servers as well as routine jobs. At the moment I use OSQL in a great big batch file to connect to each server in turn a perform queries, updates etc but I would like to be able to do the same sort of thing via DTS.

Martin

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Good Day Martin. I also have utilized MSX and multi-server jobs for the same purpose. On a daily basis I monotor server and database healkth and run adhoc updates as well. However I've only 20+ servers at present. But in the past I'bve used the same mechanism for 60-100 servers. I understand the appeal of the GUI in DTS for complex tasks. As a DBA I try to keep it simple. If it's larger than one script, I make two (and two job steps as well.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

jas81864 Old Hand Points: 333 More actions · Answer 6

I have done something similar in the past with two dts jobs. Job one reads a list of servers from a table and creates a batch file of dtsrun statements with the server name as a global variable and executes the batch file. Job two uses the passed variable for the server connection and uses a transformation to copy the data to my central server. Here is the code to create the batch file:

SET NOCOUNT ON

SET ROWCOUNT 0

DECLARE @ODBC_Name VARCHAR(30),

@fetch_next INT,

@message VARCHAR(1024),

@Server_Num INT,

@server VARCHAR(124),

@Pull_Date DATETIME

SET @server = @@SERVERNAME

SET @Pull_Date = CONVERT(CHAR(12), GETDATE(), 1)

DECLARE cur_server_name CURSOR FOR SELECT DISTINCT datasource, Server_Num FROM EAI_MediaType

WHERE Status = 1

AND Batch_Num = 1

OPEN cur_server_name

FETCH cur_server_name INTO @ODBC_Name, @Server_Num

SELECT @fetch_next = @@FETCH_STATUS

SELECT @message = 'ECHO DTSRUN /S "' + @server + '" /N "Pull Field Info" /A "ODBC_NAME":"8"="' + @ODBC_Name + '" /A "PULL_DATE":"7"="' + convert(varchar, @Pull_Date, 1) + '" /A "SERVER_NUM":"3"="' + cast(@Server_Num as varchar) + '" /E > c:\temp\database1.bat'

EXEC master..xp_cmdshell @message, no_output

FETCH cur_server_name INTO @ODBC_Name, @Server_Num

SELECT @fetch_next = @@FETCH_STATUS

WHILE @fetch_next = 0

BEGIN

SELECT @message = 'ECHO DTSRUN /S "' + @server + '" /N "Pull Field Info" /A "ODBC_NAME":"8"="' + @ODBC_Name + '" /A "PULL_DATE":"7"="' + convert(varchar, @Pull_Date, 1) + '" /A "SERVER_NUM":"3"="' + cast(@Server_Num as varchar) + '" /E >> c:\temp\database1.bat'

EXEC master..xp_cmdshell @message, no_output

FETCH cur_server_name INTO @ODBC_Name, @Server_Num

SELECT @fetch_next = @@FETCH_STATUS

END

CLOSE cur_server_name

DEALLOCATE cur_server_name

Martin-202776 SSC Journeyman Points: 84 More actions · Answer 7

Martin-202776

SSC Journeyman

Points: 84

April 16, 2005 at 10:10 am

#553053

Thanks Jas81864

Just what I wanted!!

Martin