News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



How do I create and access another sql server db using linked server in SQL Server?

For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference:

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

Here it is in a nutshell:  If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made using this security context" , the remote login/password combo (usually a service account living in active directory) and (3) set the server options rpc,rpdc out,data access, remote collation to True. That's it!   No need to worry about provider, connection strings,etc.

Here's an example on how to retrieve data from a table using the linked server reference once it is established from an interactive query window in SMS:

select * from [MYSQLSERVERHOSTNAME].[DBInstanceName].[SchemaName].[TableName]

 

 

Monday, October 31, 2011 2:42 PM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: