Hello,



We are a small/medium sized manufacturing facility that uses a home grown

data collector on our shop floor to communicate with our SQL server that we

do our logistics off of.



Now I haven't been with the company all that long, I just maintain the

current data collector now, and these problems existed before I got here,

However I am trying to resolve an error we are coming up with on a fairly

regular (and rather annoying) basis.



What happens is when a query is executed it causes a Timeout Expired error

sometimes and sometimes it causes an automation error.  The automation error

appears to be caused by Deadlocks, which is fine, I can deal with that.

It's the timeout expired error we are struggling with.



We have completly replaced our SQL Server with a new machine, and this isn't

anything small by any means.  It never achieves more than 1 percent

utilization according the SQL Profiler and does about 3 transactions a

second.  So it's hardly that we are taxing it.



We checked our network connections, even re-ran new cabling to help combat

it, however that wasn't an issue.   Our admin ran attenuation tests to see

if we were losing signal in the cable, nothing.



I checked with the software and wanted to make sure we were releasing

resources properly (i.e. properly tearing down connections associated with

the SQL server).  nothing.



Finally, googling and that got me nothing.



The error we are getting is error #-2147217871 which is timeout expired.

I've added more error checking on the ADODB.Connection.Errors collection but

nothing that has helped thus far.



Any guidance/suggestions would be appreciated.



Thanks,

CJ

Did you try setting the 'CommandTimeout' property to 0, this will disable ADO time out.



Just a suggestion.



Hedi

Do you get anything like this?



"Status no 2147217871 (Microsoft) (ODBC SQL SERVER DRIVER) timeout expired"



If yes, then there could be several potentialities that we could explore.



a) This message means that the statement took longer than the default

timeout limit. Possibly, this might be due to resource shortage on the

server, or the code itself or various other issues.



b) To start with, you might want to increase the timeout interval in your

code. Also, start with this KB

http://support.microsoft.com/default...en-us%3B190606



c)You might want to verify that you are actually hitting the destination as

well. You can either use Network Monitor to get a sniff of network traffic,

OR you can turn on auditing on the SQL Server and monitor for that user

account. It could turn out to be a network resource issue if you are not

even getting to the point of logging in.



d) If you have upgraded your server recently, then there could be a problem

with MDAC. You could look into updating the MDAC.



Just a FEW suggestions out of the POTENTIAL MANY :-)







sanchans@online.microsoft.com



This posting is provided "AS IS" with no warranties, and confers no rights.

	
Quote:
	

	

		

			
				Originally posted by Sanchan Sahai Saxena 

Do you get anything like this?



"Status no 2147217871 (Microsoft) (ODBC SQL SERVER DRIVER) timeout expired"



If yes, then there could be several potentialities that we could explore.



a) This message means that the statement took longer than the default

timeout limit. Possibly, this might be due to resource shortage on the

server, or the code itself or various other issues.



b) To start with, you might want to increase the timeout interval in your

code. Also, start with this KB

http://support.microsoft.com/default...en-us%3B190606



c)You might want to verify that you are actually hitting the destination as

well. You can either use Network Monitor to get a sniff of network traffic,

OR you can turn on auditing on the SQL Server and monitor for that user

account. It could turn out to be a network resource issue if you are not

even getting to the point of logging in.



d) If you have upgraded your server recently, then there could be a problem

with MDAC. You could look into updating the MDAC.



Just a FEW suggestions out of the POTENTIAL MANY :-)







sanchans@online.microsoft.com



This posting is provided "AS IS" with no warranties, and confers no rights. 
			
		
	
	


I would like to revisit Error 2147217871 with some new twists:



First, I have tried unsuccessfully to implement each of Sanchan Sahai Saxena’s recommendations in the previous posting.  At the risk of getting overly detailed, I would like to comment on each one to perhaps establish where I am going wrong.  Actually, I have been dealing with this particular error off and on for months now.  I am spending a lot (a lot!) of time coming up with workarounds that avoid the error --- that is until it pops up in a new context.  Seems like it might be more expedient just get to the core of what is causing it.



Here’s my environment.  Access 2002 Project (.adp) front end / SQL Server 2000 back end.  I am using a code module to launch a stored procedure that will backup a SQL Server database using two input parameters (db name and backup path).  Very simple code and sp. 



Addressing Sanchan’s recommendations:



a) This message means that the statement took longer than the default

timeout limit. Possibly, this might be due to resource shortage on the

server, or the code itself or various other issues.



This is a development app that I am running locally on my own desktop until it is grown up enough to go play on the big servers.  I have closed all other programs, I have a ton of RAM and I just can’t see how I could be causing a shortage of resources in the local environment.



b) To start with, you might want to increase the timeout interval in your code.



I’ve done this over and over again.  In Access I have increased the OLE/DDE timeout to indefinite wait (0 value).  That never has any effect whatsoever.  And I’ve used SQL Query Analyzer to run the configuration sp, thus:



sp_configure 'remote login timeout', 300

go 

reconfigure with override 

go



sp_configure 'remote query timeout', 0 

go 

reconfigure with override 

go



No effect.



c)You might want to verify that you are actually hitting the destination as well. You can either use Network Monitor to get a sniff of network traffic, OR you can turn on auditing on the SQL Server and monitor for that user account. It could turn out to be a network resource issue if you are not even getting to the point of logging in.



Yeah, I’m hitting it.  Here’s how I know --- and something that I think might indicate what is actually going on.  As I indicated above, I am running a stored procedure that will backup a SQL Server database to a local folder (from a local folder).  If I run the sp with the two required parameters (db name and backup path) written explicitly into the stored procedure code, then launch the sp from the Access database object, it runs properly.  However, if I pass the two parameters from a code module, using a cmd.execute, that is when I get the timeout error.  Logically, it doesn’t seem like it should matter.  Either way the stored procedure is being launched from within the Access .adp environment.



Even more weird: Even if I launch the stored procedure with the explicitly coded parameters from code, it causes the error.  Double-click on the same procedure in the database window and it runs fine.    



I have had this error occur in other contexts when I have tried to perform certain stored procedures by launching them from code.  And I especially get this error if I ever try to do anything non-trivial by using ADO recordsets within code.



And finally...



d) If you have upgraded your server recently, then there could be a problem with MDAC. You could look into updating the MDAC.



My employer is a Fortune 500 company with close contacts with Microsoft.  I know that they get Microsoft security and other updates on a daily (if not hourly basis) which are passed to my machine by Marimba pushes.  So, I’m thinking that I should have the latest iteration of patches and service packs currently available.  



Any help that you could provide will be greatly appreciated!!  (I really need to move on from 2147217871 --- at least to a smaller error number.)

	
Quote:
	

	

		

			
				Originally posted by JL Gates 

I would like to revisit Error 2147217871 with some new twists:



First, I have tried unsuccessfully to implement each of Sanchan Sahai Saxena’s recommendations in the previous posting.  At the risk of getting overly detailed, I would like to comment on each one to perhaps establish where I am going wrong.  Actually, I have been dealing with this particular error off and on for months now.  I am spending a lot (a lot!) of time coming up with workarounds that avoid the error --- that is until it pops up in a new context.  Seems like it might be more expedient just get to the core of what is causing it.



Here’s my environment.  Access 2002 Project (.adp) front end / SQL Server 2000 back end.  I am using a code module to launch a stored procedure that will backup a SQL Server database using two input parameters (db name and backup path).  Very simple code and sp. 



Addressing Sanchan’s recommendations:



a) This message means that the statement took longer than the default

timeout limit. Possibly, this might be due to resource shortage on the

server, or the code itself or various other issues.



This is a development app that I am running locally on my own desktop until it is grown up enough to go play on the big servers.  I have closed all other programs, I have a ton of RAM and I just can’t see how I could be causing a shortage of resources in the local environment.



b) To start with, you might want to increase the timeout interval in your code.



I’ve done this over and over again.  In Access I have increased the OLE/DDE timeout to indefinite wait (0 value).  That never has any effect whatsoever.  And I’ve used SQL Query Analyzer to run the configuration sp, thus:



sp_configure 'remote login timeout', 300

go 

reconfigure with override 

go



sp_configure 'remote query timeout', 0 

go 

reconfigure with override 

go



No effect.



c)You might want to verify that you are actually hitting the destination as well. You can either use Network Monitor to get a sniff of network traffic, OR you can turn on auditing on the SQL Server and monitor for that user account. It could turn out to be a network resource issue if you are not even getting to the point of logging in.



Yeah, I’m hitting it.  Here’s how I know --- and something that I think might indicate what is actually going on.  As I indicated above, I am running a stored procedure that will backup a SQL Server database to a local folder (from a local folder).  If I run the sp with the two required parameters (db name and backup path) written explicitly into the stored procedure code, then launch the sp from the Access database object, it runs properly.  However, if I pass the two parameters from a code module, using a cmd.execute, that is when I get the timeout error.  Logically, it doesn’t seem like it should matter.  Either way the stored procedure is being launched from within the Access .adp environment.



Even more weird: Even if I launch the stored procedure with the explicitly coded parameters from code, it causes the error.  Double-click on the same procedure in the database window and it runs fine.    



I have had this error occur in other contexts when I have tried to perform certain stored procedures by launching them from code.  And I especially get this error if I ever try to do anything non-trivial by using ADO recordsets within code.



And finally...



d) If you have upgraded your server recently, then there could be a problem with MDAC. You could look into updating the MDAC.



My employer is a Fortune 500 company with close contacts with Microsoft.  I know that they get Microsoft security and other updates on a daily (if not hourly basis) which are passed to my machine by Marimba pushes.  So, I’m thinking that I should have the latest iteration of patches and service packs currently available.  



Any help that you could provide will be greatly appreciated!!  (I really need to move on from 2147217871 --- at least to a smaller error number.) 
			
		
	
	
Okay.  I'm going to answer my own question.  After spending many more hours on this thing, I finally have stumbled across the solution to the persistent Timeout Expired problem.  (I wish I could bill Microslop for all the time I spend trying to get their junk to work at a minimal level.)  I hope this saves someone out there some time.



To restate the basic problem:



Certain stored procedures (usually those working against large SQL Server tables or with complex logic) would timeout before completion in an Access project (.adp).  I have tried all of the usual recommendations including setting the Access Project Connection Timeout value to 0 (unlimited timeout time).  BTW: this can be set from File - Connection - Advanced.



My mistake was in thinking that this was actually working.  Wrong.



Here is the basic code:



    Dim cmd As ADODB.Command

    Dim rstExecute As New ADODB.Recordset

    

    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = CurrentProject.Connection

    

    cmd.CommandTimeout = 0

    cmd.CommandText = "sp_BackupDatabase"

    cmd.CommandType = adCmdStoredProc

    cmd.Parameters.Append cmd.CreateParameter(Name:="@SQLDBName", Type:=adVarChar, Direction:=adParamInput, Size:=100)

    cmd.Parameters.Append cmd.CreateParameter(Name:="@BackupPath", Type:=adVarChar, Direction:=adParamInput, Size:=500)

    

    'Execute the stored procedure using the passed parameters.

    cmd.Parameters("@SQLDBName").Value = "DataSanitizerSQL"

    cmd.Parameters("@BackupPath").Value = "c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DataSanitizerSQL_1.dat"

    

    Set rstExecute = cmd.Execute



Notice that I have explicitly set the cmd object CommandTimeout property to 0 (even though this is set to the CurrentProject.Connection which CommandTimeout property has already been set to zero).  Well, that would be using sloppy non-Microsoft logic.  Without the line (cmd.CommandTimeout = 0), this procedure will incur a timeout expired error.  With it, everything runs properly.  Bill!  Put some of your 50 billion $ reserve in QA.

Just a suggestion... I am also running out of the same issue here in my production box.



I tried running the same application from my staging environment pointing to the production database box. it is working fine. It is absalutly environmental issue nothing to do with the connection timout parameters.



Regards,

Vimal David



Posting in this is just a suggestion..