Re: SQL/ADO (2.8) Timeout Error - Can't resolve
From: JL Gates (JL.Gates.17cka9_at_mail.mcse.ms)
Date: 06/04/04
- Previous message: matmcl: "Windows 98 & Windows XP ODBC connection"
- In reply to: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 4 Jun 2004 16:36:01 -0500
JL Gates wrote:
> *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.
-- JL Gates ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message609837.html
- Previous message: matmcl: "Windows 98 & Windows XP ODBC connection"
- In reply to: JL Gates: "Re: SQL/ADO (2.8) Timeout Error - Can't resolve"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
