SQLServerCentral / Discuss Content Posted by Krishna Sonti / Article Discussions / Article Discussions by Author / An Introduction To Linked Servers / Latest Posts

RE: An Introduction To Linked Servers

forerolui — Thu, 08 Oct 2009 11:24:48 GMT

Ok this is my solution to my question:/****** Objeto: LinkedServer [AS400] Fecha de la secuencia de comandos: 10/02/2009 17:46:59 ******/IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'AS400')EXEC master.dbo.sp_dropserver @server=N'AS400', @droplogins='droplogins'EXEC master.dbo.sp_addlinkedserver @server = N'AS400', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'MACHINE_NAME', --@provstr=N'Provider=DB2OLEDB;Password=pws;Persist Security Info=True;User ID=user;Initial Catalog=MACHINE_NAME;Network Address=IP;Package Collection=library', @catalog=N'MACHINE_NAME'

RE: An Introduction To Linked Servers

Srini-426919 — Thu, 08 Oct 2009 11:06:11 GMT

Hi,i was browsing through the net for some help regarding the linked servers issue, i have an access db which is on the process of migration to the sql server 2005, access db has some linked tables connecting from the oracle database, the same server has been linked in the sql server 2005 to retrieve the data, here is the issue.. queries which takes only 2 - 3 minutes in access db is taking more than 50 minutes to run on the sql server.. i have tried the queries running from SSIS as well, no use, regarding the indexes, we have all the tables indexed on the oracle server itself, i am just trying to join some tables and pull the data.. i ma using the OPENQUERY in the query.. please help..

RE: An Introduction To Linked Servers

RBarryYoung — Fri, 02 Oct 2009 11:03:28 GMT

[quote][b]forerolui (10/2/2009)[/b][hr]I have created and test an UDl file to connect to AS/400. After creating an testing the connection the result of the connection string is:Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=SQLODBC1;Data Source=10.254.xxx.yyyI need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)Can anyone help me?[/quote]You should post this as a separate question in order to get more exposure for it.

RE: An Introduction To Linked Servers

forerolui — Fri, 02 Oct 2009 10:51:09 GMT

I have created and test an UDl file to connect to AS/400. After creating an testing the connection the result of the connection string is:Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=SQLODBC1;Data Source=10.254.xxx.yyyI need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)Can anyone help me?

RE: An Introduction To Linked Servers

RBarryYoung — Wed, 01 Apr 2009 06:41:05 GMT

[quote][b]pranav.shukla (4/1/2009)[/b][hr]How can we make index on view which are accessing different server through link server?[/quote]I don't think that you can do that.

RE: An Introduction To Linked Servers

pranav.shukla — Wed, 01 Apr 2009 03:21:23 GMT

How can we make index on view which are accessing different server through link server?

RE: An Introduction To Linked Servers

Jeffrey Williams-493691 — Sun, 22 Jun 2008 14:04:55 GMT

Sorry, guess I was not clear enough. All I was saying was that using synonyms ADDS options, but using synonyms does not replace using views at all.Just one example of how synonyms would be benificial. Let's say our local server is SERVERA and we have a linked server SERVERB. Now let's say that the application for SERVERB is upgraded - and there are additional columns added to tables and views in the database we are referencing.If you just use views on SERVERA to access tables/views on SERVERB - you have to modify those views to include the new columns. Once that has been done - then, you can modify the procedures and other code that needs access to those new columns. This also brings up the question of which additional columns need to be added - all of them or just some?Using synonyms, we can just focus on modifying the stored procedures and code that need access to those new columns without having to worry about which columns actually need to be added to the views first.In both cases, I would hope that we have created views to filter the data, exclude columns we don't want exposed, etc...As for schema binding - when I try to bind to an object through a linked server I get:Msg 2014, Level 16, State 1, Procedure MyDepartment, Line 2Remote access is not allowed from within a schema-bound object.When trying to schema bind to an object through a synonym:Msg 2788, Level 16, State 1, Procedure MyDepartment, Line 2Synonyms are invalid in a schemabound object or a constraint expression.So - guess I can't schema bind in either case.And yes, I think using synonyms ADDS value - and again, I am not saying that using synonyms is a replacement for using views. So, I guess we have to agree to disagree on the benifits of using synonyms.Jeff

RE: An Introduction To Linked Servers

RBarryYoung — Sun, 22 Jun 2008 12:43:11 GMT

[quote][b]Jeffrey Williams (6/22/2008)[/b][hr]I am not sure where synonyms cannot be used that views can. Any examples?[/quote]Synonyms can not use Schema-binding nor be referenced by schema-bound objects. That means that if you decide to impose schema-binding on an application schema in the future, you will have to remove all of the synonyms. So that's one less option.[quote] I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.[/quote]Views do exactly the same thing, only with more options and more options for the future.[quote]One system I work with can be built to access a database locally or through a linked server. For instances where this database is remote, we have to run a script (provided by the vendor, of course) that modifies all views and stored procedures by adding in the reference to the linked server.Let's say we have three systems (I have more than this). The DEV system has all databases local, the QA system has this one database on a linked server and live also has this database on a linked server.The code on DEV uses 'database.schema.object' to reference the tables. The code in QA uses 'qalinkedserver.database.schema.object' and the code in live uses 'livelinkedserver.database.schema.object'.In this scenario - any changes to the code in DEV cannot be moved to QA or even on to LIVE as is. It must be modified in each environment for that environments specific linked server.Using schemas and synonyms makes the above a lot easier to manage because all code stays the same, regardless of whether or not the system is using a linked server. [/quote]This is no different from using views in your schema instead of synonyms. You just change a view defintion instead of changing a synonym defintion. No difference.[quote]So, yeah - I think it is one step further because it adds additional options.[/quote] ??? Huh? You still haven't named a single thing that a Synonym referencing tables & views can do that a View cannot do! You could replace "synonym" with "view" in everything that you said and it would still be true. How is that more options?As for views, A) They can be schema-bound and be referenced by schema-bound objects, and B) besides acting as an unfiltered alias, as Synonyms do, Views can also: 1) control the column list returned 2) filter the rowset returned 3) collapse relational references to other tables 4) control which columns are writeable 5) elaborate hiearchies etc., etc...And these options are available now and anytime in the future, so more flexible, more options and useable in more situations.Now I will grant you, that for all non-Table-valued objects that Synonyms work on they are the aliasing tool of choice, but for Views & Tables, I still cannot see one reason to prefer Synonyms over Views.

RE: An Introduction To Linked Servers

Jeffrey Williams-493691 — Sun, 22 Jun 2008 12:07:51 GMT

I am not sure where synonyms cannot be used that views can. Any examples? I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.One system I work with can be built to access a database locally or through a linked server. For instances where this database is remote, we have to run a script (provided by the vendor, of course) that modifies all views and stored procedures by adding in the reference to the linked server.Let's say we have three systems (I have more than this). The DEV system has all databases local, the QA system has this one database on a linked server and live also has this database on a linked server.The code on DEV uses 'database.schema.object' to reference the tables. The code in QA uses 'qalinkedserver.database.schema.object' and the code in live uses 'livelinkedserver.database.schema.object'.In this scenario - any changes to the code in DEV cannot be moved to QA or even on to LIVE as is. It must be modified in each environment for that environments specific linked server.Using schemas and synonyms makes the above a lot easier to manage because all code stays the same, regardless of whether or not the system is using a linked server. Now I know we can create the linked servers the same on each environment - but that also has it's own problems with additional management.The only place I see that views would work better is when you need a query that accesses multiple objects. In that case, you are either going to create the view in the source database and use a synonym to reference the view - or you are going to create the view in the local database and reference the view. If you reference the view locally - you have the option of accessing the objects through the linked server directly, or through the synonyms that you have created.So, yeah - I think it is one step further because it adds additional options.Jeff

RE: An Introduction To Linked Servers

RBarryYoung — Sun, 22 Jun 2008 11:51:34 GMT

[quote][b]Jeffrey Williams (6/22/2008)[/b][hr]In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and define synonyms for the objects on the linked server you use regularly (SQL Server 2005 and greater).[/quote]Actually, I would call that "one step less far", given that Synonyms are less flexible than Views and not usable in all of the cases that Views are. I am curious why you would recommend Synonyms over Views, as I honestly cannot think of a single advantage that they have versus Views?[quote]One advantage to this approach is that you could easily modify the synonyms and redirect them to a different linked server. For example:DROP SYNONYM LinkedServer.ObjectName;CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer2.Database.schema.object;GO[/quote]This is an advantage, but not when compared to Views, because you can do the same thing with them.

RE: An Introduction To Linked Servers

Jeffrey Williams-493691 — Sun, 22 Jun 2008 11:37:10 GMT

In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and define synonyms for the objects on the linked server you use regularly (SQL Server 2005 and greater). I would also create those synonyms in a different schema - just to separate things:CREATE SCHEMA LinkedServer AUTHORIZATION dbo;GOCREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer.Database.schema.object;GOThen, it can be used as:SELECT * FROM LinkedServer.ObjectName;One advantage to this approach is that you could easily modify the synonyms and redirect them to a different linked server. For example:DROP SYNONYM LinkedServer.ObjectName;CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer2.Database.schema.object;GONow using 'SELECT * FROM LinkedServer.ObjectName' will select data from LinkedServer2 instead of LinkedServer.Jeff

RE: An Introduction To Linked Servers

Roy Latham — Sun, 22 Jun 2008 11:13:22 GMT

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.[code]Use PubsSelect au_lname, au_fname from Authors-- Include owner:Select au_lname, au_fname from dbo.AuthorsUse Northwind-- Accessing table in a different database:Select au_lname, au_fname from Pubs.dbo.Authors[/code]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.[code]Select au_lname, au_fname from Lippman.Pubs.dbo.Authors[/code]So, all you need to do is to use four-part naming: LinkedServer.Dbname.Owner.tablenameinstead of: tablenamein 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:[code]Create view Lippman_Pubs_dbo_Authors as Select au_lname, au_fname from Lippman.Pubs.dbo.Authorsgo[/code]Now the table on the linked server looks like a table on the local server.This is how to access it:[code]Select au_lname, au_fname from Lippman_Pubs_dbo_Authors[/code]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[code]Select au_lname, au_fname from Lippman_Pubs.dbo.Authors[/code]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.

RE: An Introduction To Linked Servers

chinanihc — Wed, 18 Jun 2008 19:07:47 GMT

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....

RE: An Introduction To Linked Servers

Charles Kincaid — Wed, 13 Jun 2007 16:20:00 GMT

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.

RE: An Introduction To Linked Servers

Lashams — Wed, 13 Jun 2007 15:59:00 GMT

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.

RE: An Introduction To Linked Servers

ChrisG-374657 — Wed, 13 Jun 2007 15:19:00 GMT

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

RE: An Introduction To Linked Servers

Roy Latham — Fri, 08 Jun 2007 10:10:00 GMT

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_linkedservergosp_tables_ex 'servername'gosp_columns_ex 'servername'goselect 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

RE: An Introduction To Linked Servers

Charles Kincaid — Fri, 08 Jun 2007 09:08:00 GMT

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.

RE: An Introduction To Linked Servers

Jerry Tienter-162701 — Fri, 08 Jun 2007 08:45:00 GMT

Very good article.

RE: An Introduction To Linked Servers

Glen Sidelnikov — Fri, 08 Jun 2007 07:32:00 GMT

Very basic article. Even so it is explained in the article header, I would like to add that linked servers were available from SQL 7(or 6.5?) and did not change on introductory level since.

I would also assume that in SQL 7 processinf heterogenous queries had more flexibility than in SQL 2005. I would expect from SQLservercentral publications more indpeth, detailed coverage.

RE: An Introduction To Linked Servers

Sopheap Suy — Fri, 08 Jun 2007 07:19:00 GMT

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

mom

RE: An Introduction To Linked Servers

Hideyuki Fujisawa — Fri, 08 Jun 2007 06:01:00 GMT

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 ?

RE: An Introduction To Linked Servers

stunelp — Fri, 08 Jun 2007 02:20:00 GMT

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

RE: An Introduction To Linked Servers

Navin-291399 — Fri, 24 Mar 2006 04:15:00 GMT

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

RE: An Introduction To Linked Servers

ND-215452 — Thu, 02 Jun 2005 20:58:00 GMT

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

RE: An Introduction To Linked Servers

Edinei — Tue, 10 Aug 2004 20:01:00 GMT

It's very interesting.

An Introduction To Linked Servers

kmsonti — Thu, 27 May 2004 19:03:00 GMT

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