June 22, 2008 at 11:13 am
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.
June 22, 2008 at 11:37 am
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;
GO
CREATE SYNONYM LinkedServer.ObjectName FOR LinkedServer.Database.schema.object;
GO
Then, 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;
GO
Now using 'SELECT * FROM LinkedServer.ObjectName' will select data from LinkedServer2 instead of LinkedServer.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2008 at 11:51 am
Jeffrey Williams (6/22/2008)
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).
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?
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
This is an advantage, but not when compared to Views, because you can do the same thing with them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2008 at 12:07 pm
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
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2008 at 12:43 pm
Jeffrey Williams (6/22/2008)
I am not sure where synonyms cannot be used that views can. Any examples?
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.
I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.
Views do exactly the same thing, only with more options and more options for the future.
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.
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.
So, yeah - I think it is one step further because it adds additional options.
??? 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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2008 at 2:04 pm
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 2
Remote 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 2
Synonyms 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
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 1, 2009 at 3:21 am
How can we make index on view which are accessing different server through link server?
April 1, 2009 at 6:41 am
pranav.shukla (4/1/2009)
How can we make index on view which are accessing different server through link server?
I don't think that you can do that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 2, 2009 at 10:51 am
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.yyy
I need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)
Can anyone help me?
October 2, 2009 at 11:03 am
forerolui (10/2/2009)
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.yyy
I need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)
Can anyone help me?
You should post this as a separate question in order to get more exposure for it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 8, 2009 at 11:06 am
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..
October 8, 2009 at 11:24 am
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'
March 3, 2011 at 1:49 pm
Sopheap Suy (6/8/2007)
one more note: Link server does not support access on SQL Server 2005 64 bit <img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>
mom
I am pretty sure it does. Did you mean something else?
February 16, 2012 at 2:26 am
Good Topic.
The great basic information for Linked server handling
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply