An Introduction To Linked Servers

Question

Post reply

An Introduction To Linked Servers

kmsonti

Old Hand

Points: 361
More actions
May 27, 2004 at 7:03 pm

#163669

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kso

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Edinei Grasshopper Points: 19 More actions · Answer 1

Edinei

Grasshopper

Points: 19

August 10, 2004 at 8:01 pm

#518398

It's very interesting.

ND-215452 SSCrazy Points: 2037 More actions · Answer 2

Can you link to more than one (eg. sybase) server?

Navin-291399 Grasshopper Points: 17 More actions · Answer 3

wow, it is very useful to me.. thank you buddys.... .

stunelp SSC Rookie Points: 38 More actions · Answer 4

A great article, but something worth noting is that when a query is executed against a linked server using "select stuff from linkedServer.dbName.owner.objectName" SQL Server actually returns all the rows from the table(s) in the linked server and the executes the query locally (using T-SQL syntax), whereas when you use the OpenQuery() syntax the query is passed down to the linked server and only the result set is returned, this means that OpenQuery() needs to use the native syntax of the linked server rather than t-sql (assuming it's not a SQL Server!). This was my experience at least and if I'm slightly off the mark then I'd welcome further comment.

Thanks for an interesting article

Hideyuki Fujisawa SSC Enthusiast Points: 116 More actions · Answer 5

Very good article.

Okay, so when using OPENROWSET, it is using OLE DB. How about using LINKEDSERVER itself? Is it still OLEDB based connection? or ODBC ?

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 6

one more note: Link server does not support access on SQL Server 2005 64 bit

mom

Jerry Tienter-162701 Say Hey Kid Points: 694 More actions · Answer 7

Jerry Tienter-162701

Say Hey Kid

Points: 694

June 8, 2007 at 8:45 am

#711712

Very good article.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

Good introduction. I expect more in-depth articles on this subject to follow.

I have used this to attach, remote SQL servers, Access databases, and even text files. I have even heard of attaching DB-400 through Client Access.

Somebody asked about multiples. Yes you can.

ATBCharles Kincaid

Roy Latham SSC Enthusiast Points: 183 More actions · Answer 9

Yes - it can link to multiple servers.

Perhaps this snippet from Books On Line will help:

Relational Database Engine Architecture Overview

Database Engine Components

The relational database server of Microsoft® SQL Server™ 2000 has two main parts: the relational engine and the storage engine. One of the most important architectural changes made in SQL Server version 7.0 was to strictly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other.

So, in a sense, you have been using a linked server already.

The local SQL server has the local relational engine “linked to” the local storage engine.

You can choose whether the linkage to the linked server is over ODBC or OLEDB.

It depends which driver you use/how you define the datasource.

For each linked server in my care I have a folder with setup scripts and basic test scripts.

Sample contents:

sp_linkedserver

go

sp_tables_ex 'servername'

go

sp_columns_ex 'servername'

go

select from a sample table using four part name

Remember that in query analyzer that the selected/highlighted area is executed (F5) not the whole script, so I can pick out the line(s) of interest or just let the whole thing rip.

Also, I set up a database with views so that an SQL query/script that will run in query analyzer on the target linked server host can run directly. Obviously, if the target linked server is not SQL server, then I have to be selective about what is included.

I like to have a table in each database just for test purposes. This is nothing fancy, couple of columns and three or four rows.

All in all, I can quickly verify if everything is working.

Alternatively, I have actual proven syntax in front of me if something is wrong and my in the middle of the night mental table name, database name and syntax index search isn't quite functioning properly.

On my PC I have a test database that is mirrored in MS Excel, MS Access, MS SQL server 2000, MS SQL Server 2005, DB2 9, MySQL, Oracle 10g, Sybase ASE 15 and Unisys ClearPath DMSII 51.1.

Both the SQL Server 2000 and SQL Server 2005 have these databases as linked servers.

The setup is scripted and so is the basic functionality testing.

Roy

ChrisG-374657 Say Hey Kid Points: 686 More actions · Answer 10

Good article!

Can anyone tell me if you can link from SQL Server 2000 to 2005 (not 64 bit) and run queries from SQL Server 2000 against the 2005 server?

Thanks

Lashams Ten Centuries Points: 1026 More actions · Answer 11

Good article, I learned something about OPENQUERY, which I had not used before.

A few things I have identified while doing linked server queries.

Queries run over the local server linking to tables on the linked server take a performance hit timewise. A query I have took 41 minutes to complete over the link, while when executed locally on the linked server took only 35 seconds. It does depend on your tables and how much data is travelling across the link.
The OPENQUERY statement cannot include parameters or temp tables from the local server as part of the OPENQUERY statement.
You cannot specify an index or locking hint for a remote data source (i.e. "WITH(NOLOCK)" for example).
You must specify SET ANSI_NULLS ON and SET ANSI_WARNINGS ON

Thanks for the article, some useful stuff.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 12

Just tried it. Seems to work. Use the cautions that the others have suggested here. Pretty much if you can pull the data through ODBC it should work.

ATBCharles Kincaid

borge SSC Journeyman Points: 83 More actions · Answer 13

Hi, This is my first post to this Forum.... I am trying to get my head around linked server, stored procedures .... I'd like to see an example of a stored procedure that: 1) links to 'remote' SQL2005 server db 2) performs an update or insert on a linked table and a local table with rollback of the whole transaction if one of the updates / inserts fails (and if possible: give appropriate error message as to which of the updates / inserts in the transaction failed)..... Can someone please help out with this? thanks....

Roy Latham SSC Enthusiast Points: 183 More actions · Answer 14

chinanihc says:

I am trying to get my head around linked server,

The short answer is get dotted.:)

This script fragment reviews how one can access a table with increasing dottiness.

Use Pubs

Select au_lname, au_fname

from Authors

-- Include owner:

Select au_lname, au_fname

from dbo.Authors

Use Northwind

-- Accessing table in a different database:

Select au_lname, au_fname

from Pubs.dbo.Authors

All selects return the same data.

Let us assume that the server that ran the above script has now been connected to another server as the linked server Lippman.

Select au_lname, au_fname

from Lippman.Pubs.dbo.Authors

So, all you need to do is to use four-part naming:

LinkedServer.Dbname.Owner.tablename

instead of:

tablename

in Select, Insert, Update and Delete statements.

Let me add that it may help both you and your users if a view is defined to simplify the above.

For example:

Create view Lippman_Pubs_dbo_Authors

as

Select au_lname, au_fname

from Lippman.Pubs.dbo.Authors

go

Now the table on the linked server looks like a table on the local server.

This is how to access it:

Select au_lname, au_fname

from Lippman_Pubs_dbo_Authors

Now, my mechanical replacement of dots by underscore leaves room for improvement/consolidation into your site's naming conventions.

I often create a separate database consisting of the views to the linked server. I'd be likely to call that database Lippman_Pubs and the view Authors.

Access is then

Select au_lname, au_fname

from Lippman_Pubs.dbo.Authors

So, now this looks like a simple access to another database.

If you didn't include the servername as part of the database name (in my example: Pubs instead of Lippman_Pubs), maybe some people woulld never realise the data was on another server.