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 |
|
Magnum Mike
Starting Member
1 Post |
 Posted - 2007-04-13 : 15:45:05
|
| I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.Any help on this would certainly be appreciated.Thanks in advance. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-13 : 21:21:01
|
| Yes, you need linked server for that. You can create linked server with system sp or in enterprise manager, check books online for details. |
 |
|
|
HGHumphrey
Starting Member
1 Post |
Posted - 2007-04-26 : 15:46:30
|
| The best way to do this is with osql. I created a database with a table that lists every SQL server on our network. The table includes a username and password (sql authentication) to connect to every server.Then, I created a cursor to loop through the table with the servernames and dynamically create an osql statement (using xp_cmdshell) to connect to every server using the servername, username and password. Then, with osql you include an input file which houses the query you want to use to query the destination server. You can either view the results in the query analyzer window, or set the osql statement to return an output file, or not get any results at all.Hope this helpsHGHumphrey |
|
|
|
|
|
|
|
|
Quering Multiple Servers