Dynamic server connections
-
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 !!
-
you can build the synamic queries using osql utility to achieve the task.
-
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
next
.Execute
End With
next
of course, i would add option explicit and clean it up some, but that may help you out.
-
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."
-
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
-
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."
-
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
-
Thanks Jas81864
Just what I wanted!!
Martin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply