SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Introduction To Linked Servers


An Introduction To Linked Servers

Author
Message
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8005 Visits: 9971
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

pranav.shukla
pranav.shukla
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
How can we make index on view which are accessing different server through link server?
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15292 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
forerolui
forerolui
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 14
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?
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15292 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Srini-426919
Srini-426919
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 115
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..
forerolui
forerolui
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 14
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'
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2992 Visits: 3637
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?
madhoonu
madhoonu
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 78
Good Topic.

The great basic information for Linked server handling
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search