Linked Server problems | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Linked Server problems

Hi, I have a sql server 2005 box. I am trying to link to other servers. I have no problem connecting to other 2005 boxes, but when I try to connect to my sql server 2000 cluster, I get "an error occurred while establishing a connection". I have allow remote connections enabled. I have a good username and pass. The linked server is configured just like the 2005 linked servers. The only difference is that the 2000 cluster boxes don’t run on standard ports. How do you tell the linked 2000 server to use the non standard port? I’m essentially asking how to configure a sql server 2000 linked server on a non standard port. That may not be the problem, but it’s the only variable I can think of. Thanks.
Can you post the error… Try using the port number after the server name.. serverName;1433 Mohammed U.
The error is OLE DB provider "SQLNCLI" for linked server "namevsql1" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "namevsql1" 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 "namevsql1". The provider supports the interface, but returns a failure code when it is used. This is a server in a 2000 cluster. When I create a new linked server with the port as you suggested, I get an error that says instance can’t be found. I can’t figure out how to specify the port. I’m not sure how you specify the port, and based on the error above, I’m not sure it even matters. Thanks.
I found the answer. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Good to know you got the answer….<br /><br />It is 2005 64 bit issue not 2005 to 2000 <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Mohammed U.
Check this FAQhttp://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=143 too. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>