Specify instance in Select statement

Something like:

SELECT * FROM [WINXP\CORP].[Northwind].[dbo].Employees

cannot be done. So what we can do is create something called a "linked server" like this:

select * from sys.servers

EXEC sp_addlinkedserver @server='CORP'

, @srvproduct=''

, @provider='SQLOLEDB'

, @datasrc='WINXP\CORP'

select * from sys.servers

and now we can perform a select:

SELECT * FROM CORP.Northwind.dbo.Eployees

Note that the CORP in @datasrc is the instance name in the WINXP server. If you want to access the default instance you only need to specify the server name.

To delete the linked server use:

exec sp_dropserver 'CORP'

Here are another examples:


EXEC sp_addlinkedserver @server='SQLV02' -- the "friendly name will use in our queries"
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='SQL-V02' -- the actual name of the server

EXEC sp_addlinkedserver @server='INC' -- same as above

, @srvproduct=''

, @provider='SQLOLEDB'

, @datasrc='SQL-V01\Inc' -- the server name and the db instance (Inc in this example)