An Introduction To Linked Servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Krishna Sonti » An Introduction To Linked Servers

29 posts, Page 3 of 3

«««1 2 3

An Introduction To Linked Servers

Rate Topic

Display Mode

Topic Options

Author

Message

Jeffrey Williams 3188

Posted Sunday, June 22, 2008 2:04 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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 opportunites brilliantly disguised as insurmountable obstacles.

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

Post #521452

pranav.shukla

Posted Wednesday, April 01, 2009 3:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 07, 2009 11:58 PM
Points: 1, Visits: 9

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

Post #687719

RBarryYoung

Posted Wednesday, April 01, 2009 6:41 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

Post #687831

forerolui

Posted Friday, October 02, 2009 10:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 08, 2010 6:48 AM
Points: 8, 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?

Post #797086

RBarryYoung

Posted Friday, October 02, 2009 11:03 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

forerolui (10/2/2009)

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

Post #797091

Srini-426919

Posted Thursday, October 08, 2009 11:06 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 12:12 AM
Points: 11, Visits: 110

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

Post #800207

forerolui

Posted Thursday, October 08, 2009 11:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 08, 2010 6:48 AM
Points: 8, 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'

Post #800219

curious_sqldba

Posted Thursday, March 03, 2011 1:49 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:13 PM
Points: 1,172, Visits: 2,687

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?

Post #1072899

madhoonu

Posted Thursday, February 16, 2012 2:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 14, 2013 11:01 PM
Points: 4, Visits: 71

Good Topic.

The great basic information for Linked server handling

Post #1252938

« Prev Topic | Next Topic »

29 posts, Page 3 of 3

«««1 2 3

Permissions