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 |
|
rocksteer
Yak Posting Veteran
69 Posts |
 Posted - 2008-09-19 : 06:26:10
|
| (I am using SQL Server 2000, but maybe the 2005 solution may apply to 2000 vsn. I want to reach those who might have a solution for me) I need to visit servers in about 30 locations. They all have the same database and logins. I am running the same query at each location. (I only have read access at the remote servers)I see at least two solutions. 1 - Using an Active X script with ADO, and looping though each location in turn supplying the login, password, and query. If I do that, could you supply and example of how to login to the remote servers, run the query, and dump the results into a table on the local server. I believe that this would be the preferred solution.Something like looping with query (INSERT INTO LocalTable SELECT ... FROM SQL STATEMENT) 2 - Create sql server icons (about 30) each pointing to a server at a location), create a query icon, point each to the local server icon.I see that the second may perform better because it can query in parallel, but has the disadvantage of needing all the multiple copies of the pocessing paths.In either case I need to dyamically supply the date in the where clause...If Mondayget data for previous Fridayelseget data for previous dayendifHow do I do this? Would a UDF have scope to be used in DTS? (I believe that it is limited to the database containing it.) Would a UDF be global if I created it in Master? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-19 : 07:33:10
|
| Use a variable to hold date in DTS and then find required dates from it using date functions. |
 |
|
|
rocksteer
Yak Posting Veteran
69 Posts |
Posted - 2008-09-29 : 08:25:20
|
| I had trouble assigning a value to the DTS global variable that I created, or to create one dynamically in the ActiveX Script.I was looking for a total solution to visit the various servers and store the results in a database table on my local server, peferably and ActiveX solution. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-29 : 10:05:57
|
quote:
Originally posted by rocksteer
I had trouble assigning a value to the DTS global variable that I created, or to create one dynamically in the ActiveX Script.I was looking for a total solution to visit the various servers and store the results in a database table on my local server, peferably and ActiveX solution.
you can use a table to store the values for variable and then use a stored procedure to retrive value and assign it to variables. then using dynamic properties task you can map variables to various task properties. |
|
|
|
|
|
|
|
|
DTS - to visit may servers with same query