Accessing a table on a different server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Accessing a table on a different server

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-19 : 11:29:08

I am running SQL20008R2 and need to access a table on a different machine.

The SQL Server I am trying to link to is called CQR\PState lovated on the server CQR

How can i link to this server and select from the table batch ?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:10:33

Create a linked Server...either through SSMS or for example


USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'<servername\instance>', @srvproduct=N'SQL Server'

GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<servername\instance>', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<servername\instance>', @locallogin = NULL , @useself = N'True'
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-19 : 12:18:55

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:48:04

Now just reference the table on the other server as a 4 part name

SELECT *
FROM [SERVERNAME\INSTANCE].[DBNAME].[OWNER].[TABLE]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources