Linked Server Problem SQL Server 2005 to SQL Server 2000

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

Hi

I am having issue with the linked server

exec sp_tables_ex localserver1 "this works perfectly"

but when I do the select query on objects it fails with the message

OLE DB provider "SQLNCLI" for linked server "prodscorecard" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "prodscorecard" 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 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "prodscorecard". The provider supports the interface, but returns a failure code when it is used.

can anybody help 

thanks

Answer 6

You can find a fix for this issue in this forum. http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=62887&SiteID=17

 

It's works great!