Accessing data on "Foreign" Server
-
In Query Analyzer, I need to query data that resides on a different SQL Server.
I know that I can use <DatabaseName>.<owner>.<TableName> to get data from a different database. Is there a way to query a different server?
Thanks, as always,
Bryan
-
bryan - you would really have to read up on linked servers and distributed queries and transactions - there are some simple "how to"s in BOL on creating a linked server using EM...and using "openrowset" to query a linked server...
**ASCII stupid question, get a stupid ANSI !!!** -
I had posted a similiar question back in March, and when I searched the forums, they pointed me in the correct direction (I think).
One of things that I will have to do is to add a "Linked Server" using sp_AddLinkedServer.
Do I need to do this on both SQL Servers? Or is putting it on one enough?
Do I need to run the sp for each database, or do I just run it in "Master"?
Any and all help would be greatly appreciated!
-
Do I need to do this on both SQL Servers? Or is putting it on one enough?
Linked server needs to be present on the server where you are going to submit the four part name query!!
select * from LINKEDSERVERNAME.DBNAME.DBO.TABLENAME
Do I need to run the sp for each database, or do I just run it in "Master"?
Linked servers are GLOBAL (Meaning instance specific) they can be accesed from any database in that instance! the sp_addlinked sever can be run from any DB and it needs to be run only ONCE
hth
* Noel -
OPENROWSET
New Information - SQL Server 2000 SP3.Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.
Syntax
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
-
One of the things I don't like about open row sets is the login information is in the query... and, no, I prefer not to encrypt queries especially on development servers.
I like the idea of setting up a linked server once and never having to do more than use the 4 part naming convention. The idea of using something that requires a plain text login everytime I want to use it just doesn't seem to be efficient in my mind... and what if the server login changes? With OpenRowSet, you have to go back and change all the code that uses it. With a linked server, you only need to change the login in one spot and all broken code is suddenly repaired.
I suppose you could accomplish nearly the same thing by creating a common View that uses OpenRowSet (haven't tried so don't know if it works) but there's that plain text login thingy again AND you may have added one more View in a chain of Views... that can get kinda slow.
Even with the occasion connection problems using the ODBC based linked servers, I've found that the simplicity of their use in code and the very low maintenance makes them a better choice of OpenRowSet. Just an opinion... I expect that others will disagree and wouldn't mind hearing more about peoples personal experiences with either or both...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I don't like about open row sets is the login information
You could use integrated security instead of SQL and then you don't need to embed loging info!!!
select * from openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVERNAME', 'select * from Northwind.dbo.Employees') dt
* Noel -
Cool... Thanks Noeld. It's been so long since I've used a trusted connection, I almost forgot they existed.
I'll probably keep using linked servers, though, because...
SELECT *
FROM linkedserver.Northwind.dbo.Employees
...seems a whole lot easier than...
select * from openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVERNAME', 'select * from Northwind.dbo.Employees') dt
...especially when it comes to joins from multiple tables on the remote.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply