April 16, 2009 at 2:58 pm
Hello,
I have searched everywhere for a solution but no luck... This problem is preventing me from completing a major project.
I have 2 servers, SQL 2005 and SQL 2000, as described below. I need to set up a linked server from 2005 to 2000 in order to run distributed queries from stored procedures. Here is the error I get:
Named Pipes Provider: Could not open a connection to SQL Server [5]
OLE DB provider "SQLNCLI" for linked server "servername" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "servername" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 5)
SERVER DETAILS:
--> Need Linked Server from Server A to Server B
--> Servers are on the same network, no firewall between them
Server A:
SQL Server 2005 Standard SP2 (9.00.3042.00 (Intel X86))
Instance is the default instance
Windows Server 2003 Standard SP2
Remote connections allowed
SQL Browser service running
TCP enabled - Port # is *not* standard 1433 port
Named pipes enabled
Server B:
SQL Server 2000 Enterprise SP4 (8.00.2055 (Intel X86))
Instance is the default instance
Windows Server 2003 Enterprise SP2
Remote connections allowed
SQL Browser service running
TCP enabled, port 1433
Named pipes enabled
Other info:
- I can ping Server B from Server A.
- I tried to set up an ODBC connection using SQL Native Client to Server B. It failed with the same error as above but error # was 1326.
- I tried to set up an ODBC connection using the SQL Server driver instead and it worked. The test connection said it was successful.
- Tried creating linked server with GUI and with sp_addlinkedserver. Result is the same both ways.
SCRIPT TO CREATE LINKED SERVER:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER-SQL', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER-SQL', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVER-SQL', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'password'
GO
Thank you for any help!
April 16, 2009 at 8:58 pm
You may miss the parameter, @provider = 'provider', in executing your sp_addlinkedserver procedure.
In my memory, you should use MSDASQL for linking SQL 2000, and SQLOLEDB for SQL 2005. Please double check.
April 16, 2009 at 11:49 pm
Hi,
Instead of the Link server name, type the IP address of the server & then try.
Have a nice Day !!!
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
April 17, 2009 at 5:55 am
This could occur when SQL Server 2005 is not configured to accept remote connections. You need to allow remote connection on the server..
Here is the standard microsoft kb article, that will help you in setting up the remote connection : http://support.microsoft.com/kb/914277
-Rajini
April 17, 2009 at 6:35 am
use ip address instead of servername and make sure the DTC is running on the services.
If you go to component services i forget where but your see the transactions status.
Im not on laptop to find how you see this but im sure if you search for Component services your see this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply