sp_addlinkedserver question

[FALCONSEYE]

I have a question and will appreciate if somebody has a link or a suggestion. The intention is to use sp_addlinkedserver and create a link between a SQL Server 2000 and SQL 2005 instance. When I run the following:


EXEC sp_addlinkedserver
@server = N'pcmenu_SQL2000',
@srvproduct = N'',
@provider = N'SQLNCLI',
@provstr = N'SERVER=serverNamel;UID=sa;PWD=myPass;'
[/color ]

I receive no error. However, when I use a query like :


SELECT 1 FROM pcmenu_SQL2000.pcmenu.dbo.rmdLookUp


I get an error telling me :
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "pcmenu_SQL2000" does not contain the table ""pcmenu"."dbo"."rmdLookUp"". The table either does not exist or the current user does not have permissions on that table.

The sql server 2000 instance, indeed, has the rmdLookUp table. The provstr parameter uses a valid sa + password combination, so the permission should not be a problem. What to do?
Hilfe!

http://www.jetansolutions.com

 

[FALCONSEYE]

oi, when i view the pcmenu_sQL2000 in SQL Mgmt Studio on the server, I see bunch of tables. It seems like it created a link to itself. How is this possible?

http://www.jetansolutions.com

 

[FALCONSEYE]

Nevermind. The following solved my problem:

EXEC sp_addlinkedsrvlogin 'pcmenu_SQL2000', 'false', NULL, 'sa', 'password'

thanks

http://www.jetansolutions.com