error in linked server - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

error in linked server

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-27 : 04:30:18

Dear All,
we've two servers
server1 sql server 2000 sp1 32 bit ipaddress
server2 sql server 2005 64 bit.

at server2,
i've created linked server between these two like this
sp_addlinkedserver 'server1'
go
sp_addlinkedsrvlogin 'server1',false,null,'sa','pwd'
go

at server1,
i've created linked server between these two like this
sp_addlinkedserver 'server2'
go
sp_addlinkedsrvlogin 'server2',false,null,'sa','pwd'
go

i'm now able to get the data of server2 from server1.
but i'm getting error while trying to get the data of server1 from server2

the error is like this

OLE DB provider "SQLNCLI" for linked server "192.168.1.200" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "192.168.1.200" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 5
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "192.168.1.200". The provider supports the interface, but returns a failure code when it is used.

the ipaddress belongs to server1 (192.168.1.200)

Arnav
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 04:46:05

May be this:-

http://support.microsoft.com/kb/906954

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-27 : 05:19:43

thank you very much visakh...
i got the same solution in sqlauthority.com....
thank you very much for the prompt reply

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-27 : 05:21:14

still i've a doubt that how to know wether it is named instance or default instance?

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 08:10:26

For this issue: you have to install Instcat.sql for it to work.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-27 : 11:08:13

sodeep,
can you tell me jow can i install this Instcat.sql file?

actually i've followed the osql commands, but i didn't get the file executed.

i've also installed sql server2000 sp4 on server1. still i'm getting the same error

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 11:11:31

Its clearly explained in link provided by visakh about inscat.sql.
Also reboot the server after you do that. Otherwise you can use openquery for it.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-07-01 : 01:06:37

can anyone tell me on which server i need to run the script? on 32 bit? or on 64 bit?
because i'm unable to get the data from 32 bit to 64 bit.
i'm getting the data from 64 bit to 32 bit.

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-07-01 : 03:56:33

i've run the script on 32 bit server. and restarted the services also. but still the same problem. can anyone help me here

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-01 : 09:18:44

Are you sure? I have applied in so many servers and its working fine. You have to apply in 32 bit server and also delete/recreate linked server after you reboot. Make sure you script returns successful . If not , Openquery will work . Check books online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources