April 13, 2007 at 3:01 pm
I'm a newbie to MS SQL Server - I'm trying to get 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 from within the stored procedure, or when I try to run a query from Windows command line by using OSQL, 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 me to avoid creating linked servers - Is there a way to accomplish this without the use of linked servers? The query runs fine on its own server, but it cannot connect to others and retrieve the same information.
Any help on this would certainly be appreciated.
Thanks in advance.
April 13, 2007 at 4:20 pm
Check out OPENQUERY in books on line, this way you can make the connection on the fly.
Andrew
April 13, 2007 at 6:06 pm
Andrew, I did use the OpenQuery method and I got the same result when I tried to run the query on a different server that's not defined as a linked server in the sysservers table. It told me that I needed to run sp_addlinkedserver, which is something they don't want me to do.
Any other suggestions?
April 14, 2007 at 2:40 pm
Michael,
My mistake, meant to say OPENROWSET:
select whatever from
OPENROWSET
('SQLoledb',
'server=YourServerName;uid=YourUser;pwd=YourPassword'
,
'select whatever
from databasename.dbo.tablename'
)
April 15, 2007 at 11:28 pm
Are you able to get this from one server , if yes then do this.
Make linked server , to all other server and then u can easily access that remote server as ur local server databases.
April 16, 2007 at 7:51 am
If you are considering the use of third party tools, then the company I work for (SQL Farms) has a product with which you can query all servers / databases without linked servers, openrowsets, and such - you can just write your query as you would for a single server and get one result set from all servers.
If you are not considering 3rd party tools- then there are 3 techniques to perform cross-server operations:
- Linked servers
- OpenRowSet calls
- RPC calls
The posting above cover the first two. You can learn more about RPC calls in books online. You can enable RPC or establish linked servers using the sp_serveroption command. Please refer to
http://msdn2.microsoft.com/en-us/library/ms178532.aspx
and other links at the bottom of this page for additional information.
April 16, 2007 at 9:37 am
You will have fewer security issues if you create this as a SSIS package running on your desktop, rather than insisting on doing it in a stored procedure. A package running on your client PC can connect to as many servers as you like, and uses permissions associated with your login. When you want to use OPENDATASOURCE or linked servers, you need rights to tell the SQL Server to make remote connections on your behalf. This is not difficult to do, but it requires server configurations and permissions that your network admins may not want to deal with.
April 16, 2007 at 6:25 pm
Thanks for the tips everyone, I'll more than likely end up using the OPENROWSET function.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply