Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

An Introduction To Linked Servers Expand / Collapse
Author
Message
Posted Sunday, June 22, 2008 2:04 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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
Posted Wednesday, April 1, 2009 3:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 7, 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
Posted Wednesday, April 1, 2009 6:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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
Posted Friday, October 2, 2009 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 8, 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
Posted Friday, October 2, 2009 11:03 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #797091
Posted Thursday, October 8, 2009 11:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 19, 2013 7:43 AM
Points: 11, Visits: 114

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
Posted Thursday, October 8, 2009 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 8, 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
Posted Thursday, March 3, 2011 1:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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
Posted Thursday, February 16, 2012 2:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:10 AM
Points: 4, Visits: 75
Good Topic.

The great basic information for Linked server handling
Post #1252938
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse