Solved

Login timeout expired when updating tables from sql 2005 to sql 2000

Posted on 2009-05-20
9
695 Views
Last Modified: 2012-05-07
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".
0
Comment
Question by:kikimayhay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24432700
You obtained this error because OBDC connection was attempted to sql 2000 when 2005 server is not ready to process a new local connection, possibly due to overload:  

More info below:
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
0
 

Author Comment

by:kikimayhay
ID: 24432833
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).
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24432877
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.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:kikimayhay
ID: 24432947
That also does not seem correct as this issue never popped up before we moved to sql 2005.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24433008
0
 

Author Comment

by:kikimayhay
ID: 24433259
Update
I tried to just perform a simple lookup from our sql 2005 database to the sql 2000 (
select * from remote_server_name.database_name.dbo.table_name)
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".
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24433596
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24434468
After you moved all the data, did you update statistics?
0
 

Accepted Solution

by:
kikimayhay earned 0 total points
ID: 24436596
We called Microsoft. To correct issue we first deleted the linked database found in SSMS --> Server Objects --> Linked Servers instead of doing it via direct hard coded sql (the method apparently preferred by sql guru's that blog). We then created a new linked server using the ssms console (right click on "linked server" from above select new)
We also had to run the sql code from article : http://support.microsoft.com/kb/906954
after that no more issues.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query for timesheet application 3 24
Pivot tables in SQL 1 37
SQL - Simple Pivot query 8 27
Powershell finalizing the end of an array. 4 24
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question