Login timeout expired when updating tables from sql 2005 to sql 2000
We moved our database from SQL 7 (32 bit) to SQL 2005 (64 bit) our latest issue is we receive a login time out expired anytime we run the VBA script that is updating the tables between two databases. What used to happen is the SQL 7 database would update a table in a SQL 2000 (32 bit) database now we are trying to do the same except we are going from a SQL 2005 database updating the same SQL 2000 database.
Exact error message:
Run-time error '-214217900 (80040e14):
OLE DB provider "SQLNCLI" for linked server "Server_Name" returned message "Login timeout expired".
Exact error message:
Run-time error '-214217900 (80040e14):
OLE DB provider "SQLNCLI" for linked server "Server_Name" returned message "Login timeout expired".
ASKER
That would seem extremely unlikely. Our Sql Server 2005 database is sitting alone on a 4 processor 6 cores server with 16 gb ram (yeah it's overkill). Not to mention the updating process has not worked sonce we moved to the new server. That would mean it is always overloaded. I am not dismissing it just seems unlikely or my understanding is way off (not impossible either).
Typed wrongly earlier, It was
You obtained this error because OBDC connection was attempted to sql 2005 when 2000 server is not ready to process a new local connection, possibly due to overload
Since you try to update SQL Server 2000, connection will be established from 2005 to 2000 server.
You obtained this error because OBDC connection was attempted to sql 2005 when 2000 server is not ready to process a new local connection, possibly due to overload
Since you try to update SQL Server 2000, connection will be established from 2005 to 2000 server.
ASKER
That also does not seem correct as this issue never popped up before we moved to sql 2005.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Update
I tried to just perform a simple lookup from our sql 2005 database to the sql 2000 (
select * from remote_server_name.databas
and received the following errors
OLE DB provider "SQLNCLI" for linked server "server_name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "server_name" 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.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "server_name" returned message "Invalid connection string attribute".
I tried to just perform a simple lookup from our sql 2005 database to the sql 2000 (
select * from remote_server_name.databas
and received the following errors
OLE DB provider "SQLNCLI" for linked server "server_name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "server_name" 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.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "server_name" returned message "Invalid connection string attribute".
Check for the following:
1. In Surface Area Configuration of SQL Server 2005, check whether Remote connections using Both TCP / IP and Named Pipes are enabled.
2. In SQL Server Configuration Manager, Check whether TCP / IP Dynamic Ports are set to Blank and TCP / IP Ports are assigned to default port 1433 or something else.
3. Check whether Mixed mode Authentication are enabled in SQL Server 2005.
http://kbase.gfi.com/showarticle.asp?id=KBID002804
4. Firewall is disabled in that SQL Server 2005 machine.
5. MSDTC is configured as mentioned in
http://support.microsoft.com/kb/839279
6. There was some hotfix required for SQL Server 2005 for Linked server to function properly. Hence a minimum of SP2 or SP3 will help to fix that bug in 2005.
Hope this helps.
1. In Surface Area Configuration of SQL Server 2005, check whether Remote connections using Both TCP / IP and Named Pipes are enabled.
2. In SQL Server Configuration Manager, Check whether TCP / IP Dynamic Ports are set to Blank and TCP / IP Ports are assigned to default port 1433 or something else.
3. Check whether Mixed mode Authentication are enabled in SQL Server 2005.
http://kbase.gfi.com/showarticle.asp?id=KBID002804
4. Firewall is disabled in that SQL Server 2005 machine.
5. MSDTC is configured as mentioned in
http://support.microsoft.com/kb/839279
6. There was some hotfix required for SQL Server 2005 for Linked server to function properly. Hence a minimum of SP2 or SP3 will help to fix that bug in 2005.
Hope this helps.
After you moved all the data, did you update statistics?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
More info below:
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx