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-18 : 13:05:43
|
| 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 Monday get data for previous Fridayelse get 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-18 : 13:09:27
|
| For passing date declare a variable in dts and pass the value of date through variable. Then you can use date functions to get required date from variable value. |
 |
|
|
rocksteer
Yak Posting Veteran
69 Posts |
Posted - 2008-09-29 : 08:27:10
|
| 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:15:50
|
| continued herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110972 |
|
|
|
|
|
|
DTS - to visit may servers with same query