Tuesday, August 30, 2005 8:53 AMI'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:
@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...
Wednesday, August 31, 2005 11:01 PM
Can you try specifying the connection string in the provstr parameter ( rather than the datasrc ) of sp_addlinkedserver?
@server = N'JANET01',
@srvproduct = N'',
@provider = N'SQLNCLI',
@provstr = N'SERVER=192.168.100.150;Integrated Security=True'
Thursday, August 03, 2006 7:07 PM
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
Tuesday, September 26, 2006 9:27 AMI 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.
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.
Tuesday, December 19, 2006 6:49 AM
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.
Wednesday, September 17, 2008 11:40 PMHiI am having issue with the linked serverexec sp_tables_ex localserver1 "this works perfectly"but when I do the select query on objects it fails with the messageOLE 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 1Cannot 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 helpthanks
Monday, September 29, 2008 9:44 PMYou 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!