Linked Server Problem SQL Server 2005 to SQL Server 2000

I've been trying to add a linked server om my SQL Server 2005 Beta 2 install. The Linked Server i'm trying to add is a MS SQL Server 2000 with Service Pack 4 installed on it.

The code i've run on the SQL Server 2005 server to add the linked server is the following:

EXEC sp_addlinkedserver

@.server = N'JANET01',

@.srvproduct = N'',

@.provider = N'SQLNCLI',

@.datasrc = N'SERVER=192.168.100.150;Integrated Security=True'
The Error i'm getting is the following:

OLE DB provider "SQLNCLI" for linked server "JANET01" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "JANET01" returned message "Client unable to establish connection".

Msg 126, Level 16, State 1, Line 0

VIA Provider: The specified module could not be found.

Is there someone who can help me out with this error?

I have all protocols enabled on the MS SQL Server 2005 server and have used the Surface Area Configurator to enable remote connections and adhoc remote queries.

I really don't know what to do next :-(

Thanks in advanced...

Kind regards,

Robertino

Can you try specifying the connection string in the provstr parameter ( rather than the datasrc ) of sp_addlinkedserver?

EXEC sp_addlinkedserver
@.server = N'JANET01',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.provstr = N'SERVER=192.168.100.150;Integrated Security=True'

Thanks,
Rakesh

|||

I had this problem also. You need to load the InstCat software on your SQL2000 server. Please look in MS KB articles for InstCat.

Also, once you establish your linked server on the SQL2005 server, if it is I64, do not view it with Mgmt Studio as it be destroyed...use sp code from that point to review and modify...its a i64 bug

|||I added a link server to my SQL Server. But I cannot execute a select statement with openquery. After lot of googling I presume there is some issue- has it to do something with Service Packs.
I am currently running a 180 days trial version.

Thanks in Advance.

Chandrakant Karale

Code:

select * from openquery ([localserver1],'select * from dummyobjects')

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "localserver1" supplied inconsistent metadata for a column. The column "DummyObjectID" (compile-time ordinal 1) of object "select * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.|||

Try to execute

exec sp_tables_ex localserver1

to test connection. If it works, you will get the list of tables available on localserver1.

Then you can execute

select * from localserver1..dbo.dummyobjects (two dots before dbo) and hopefully get the data out.