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

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.aspx?scid=kb%3Ben-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.

[quoted text, click to view]


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

First, I have tried unsuccessfully to implement each of Sanchan Saha
Saxena’s recommendations in the previous posting. At the risk o
getting overly detailed, I would like to comment on each one to perhap
establish where I am going wrong. Actually, I have been dealing wit
this particular error off and on for months now. I am spending a lo
(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 mor
expedient just get to the core of what is causing it.

Here’s my environment. Access 2002 Project (.adp) front end / SQ
Server 2000 back end. I am using a code module to launch a store
procedure that will backup a SQL Server database using two inpu
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 deskto
until it is grown up enough to go play on the big servers. I hav
closed all other programs, I have a ton of RAM and I just can’t see ho
I could be causing a shortage of resources in the local environment.

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

I’ve done this over and over again. In Access I have increased th
OLE/DDE timeout to indefinite wait (0 value). That never has an
effect whatsoever. And I’ve used SQL Query Analyzer to run th
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 th
destination as well. You can either use Network Monitor to get a snif
of network traffic, OR you can turn on auditing on the SQL Server an
monitor for that user account. It could turn out to be a networ
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 thin
might indicate what is actually going on. As I indicated above, I a
running a stored procedure that will backup a SQL Server database to
local folder (from a local folder). If I run the sp with the tw
required parameters (db name and backup path) written explicitly int
the stored procedure code, then launch the sp from the Access databas
object, it runs properly. However, if I pass the two parameters from
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 th
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.)



--
JL Gates
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message609837.html

[quoted text, click to view]

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.



--
JL Gates
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message609837.html