Re: SQL/ADO (2.8) Timeout Error - Can't resolve
From: JL Gates (JL.Gates.178uxq_at_mail.mcse.ms)
Date: 06/02/04
- Next message: News.microsoft.com: "nice new SQL Server tool available"
- Previous message: Dana Richardson: "SQL Error: Text file size of zero"
- Next in thread: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Reply: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Jun 2004 18:30:57 -0500
Sanchan Sahai Saxena wrote:
> *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://tinyurl.com/2jq9j
>
> 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.)
-- JL Gates ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message609837.html
- Next message: News.microsoft.com: "nice new SQL Server tool available"
- Previous message: Dana Richardson: "SQL Error: Text file size of zero"
- Next in thread: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Reply: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
