starting out with Linked Servers?

Question

Hello, I would like to know more about setting up a Linked Server please?  I've google'd around a bit but haven't found too much from soup-to-nuts kind of links.  Does anyone have any helpful links to explain Linked Servers (setting up, configuring and running queries against)?

 

Thanks..

Answer 1

Hi,

what about:

Linked Servers on MS SQL: Part 1

Linked Servers on MS SQL Part 2

Linked Severs on MS SQL Part 3 

 

Answer 2

Unfortuately not.  Thank you for the suggestion, but with sql server 2k5 Management Studio, I'm unable to see anything underneath the Linked Server node in the Object Explorer tree under the Server Objects/Linked Servers node.  I do not see any tables in there (as illustrated by Part 2).

 

Has anyone done this with sql server 2k5?  Thanks.

Answer 3

Hi,

did you do something like this:

 

EXEC sp_addlinkedserver
@server        = N'LONDON2',
@srvproduct = N'',
@provider     = N'SQLNCLI',
@provstr       = N'SERVER=192.168.100.150;Integrated Security=True'

EXEC sp_tables_ex @table_server = 'LONDON2',
@table_catalog = 'AdventureWorks',
@table_schema = 'HumanResources',
@table_type = 'TABLE'

 

 

CU

tosc

Answer 4

In SQL Server Management studio, right click on Server Objects | Linked Servers node. Open New Linked Server dialog.Give the server name you want to connect to and choose SQL Server radio button if you are connecting to a SQL Server. (You can connect to other servers also by using the other radio button) Go to the security page and choose appropriate option.

Create linked server by pressing OK.  You can browse through the databases, tables, views on the linked server after you create one (you need SQL Server 2k5 SP2 to browse through databases, tables etc from UI) using Object Explorer Linked Server node.

Check out how to create linked server using stored proc:

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

 

Hope this helps,

Sravanthi

Answer 5

I believe I might not have the SP2 for Mangement Studio which might be hindering my efforts.  I'll try to locate the SP2 now.

Answer 6

Ok, I'm one step closer, I can now see the tables under the Catalog in the Linked Servers, however, when I open up that node, I see ALL the databases on that server.  I want to be able to limit it down to just one database.

 

Can this indeed be done?  Everywhere I've looked they say it can be done (by supplying the Catalog property) but then then continue to use that catalog name in the four part naming for the query.  The whole key to this process is to not have to know the remote databases name ahead of time, I would like to use the Linked Server as my reference/pointer to the remote DB.  Is this possible?

 

Thanks.

Answer 7

The four part notation requires the existance of the catalog name. For your question of security and catalog visibility, if the linked server is a SQL Server 2005, the user will only see the database where he has access to.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---