Linked server from SQL2005 to SQL2000

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

--

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

What do you mean by "I am not able to see the tables under it"... have you tried:

SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?

|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||

Hey u can't see the link server objects in 2005 as u used to see in sql2000.

But u can query an object as u wish.

if u know the name of any table.

Try to query the link server like

Select * from LinkServerName...tablename.

You can also use openquery statement.

Good Luck

|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager

You can also get information about linker servers usingsp_linkedservers system procedure.