Pass through query timeout (1 Viewer)

llkhoutx

Registered User.
Local time
Today, 15:04
Joined
Feb 26, 2001
Messages
4,018
I have an Access pass thourgh query which trims data and reformats a column which timesout after 3 minutes. That same query runs in 30 seconds in a SQl Analyzer.

It has previously run numerous times in the 30 second range.

My ODBC connection is correct.

Any ideas?
 

mdlueck

Sr. Application Developer
Local time
Today, 16:04
Joined
Jun 23, 2011
Messages
2,631
Is it an interactive pass-through query, or a query driven by VBA?

Please provide the query. Please provide the VBA source code if run from VBA.

Do you have a DSN, or are you DSN'less via VBA code?
 

llkhoutx

Registered User.
Local time
Today, 15:04
Joined
Feb 26, 2001
Messages
4,018
The query is a pass through query name "ptTrim" and its SQL is:
PHP:
[QUOTE]The query is:

BEGIN TRY

UPDATE tdw 
SET 
tdw.tdwuid = LTRIM(RTRIM([tdw].[tdwuid])), 
tdw.carrier_name = upper(LTRIM(RTRIM([tdw].[carrier_name]))), 
tdw.seller_name = upper(LTRIM(RTRIM([tdw].[seller_name]))), 
tdw.buyer_name = upper(LTRIM(RTRIM([tdw].[buyer_name]))), 
tdw.origin_code = LTRIM(RTRIM([tdw].[origin_code])), 
tdw.origin_option = LTRIM(RTRIM([tdw].[origin_option])), 
tdw.destination_code = LTRIM(RTRIM([tdw].[destination_code])), 
tdw.dest_option = LTRIM(RTRIM([tdw].[dest_option])), 
tdw.mode_code = LTRIM(RTRIM([tdw].[mode_code])), 

manifest_date = convert(varchar(10),cast(cast(manifest_date as varchar(12)) as datetime),101)

WHERE 
tdw.report_state='IL' AND 
tdw.report_year=2012 AND 
tdw.report_month=7

END TRY

BEGIN CATCH

PRINT '   ERROR NUMER      : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT '   ERROR MESSAGE   : ' + ERROR_MESSAGE();
PRINT '   ERROR SEVERITY    : '+ CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT '   ERROR STATE         : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT '   ERROR LINE           ; ' + CAST (ERROR_LINE() AS VARCHAR(10));

END CATCH[/QUOTE]

The query's properties are:

PHP:
ODBC Connect Str: ODBC;DSN=ZYTAXPROD;Trusted_Connection=Yes;DATABASE=ZytaxProd
Return Records: No
Log Messages: No
ODBC Timeout: 240

Executing query "ptTrim" with the "Bang" icon and via VBA "DoCmd.OpenQery 'ptTrim'", both timeout.

"ptTrim" in another database excutes correctly.

I imported ptTrim from the database where it runs and it also timesout in the instant database.

ptTrim sql executes correctly in SQL Analyzer."

Other pass throughs execute properly.

I'm at a loss.

Thank you in advance for your courtesies.
 

mdlueck

Sr. Application Developer
Local time
Today, 16:04
Joined
Jun 23, 2011
Messages
2,631
Perhaps cook up one test record, have it be year 2013, and see if the query can update the one record.

What type of VBA code do you use to submit the query? ADO? DAO? May we see that code as well?
 

llkhoutx

Registered User.
Local time
Today, 15:04
Joined
Feb 26, 2001
Messages
4,018
I use DAO code:
Sub Execute_PassThrough_Update_Queries(sQueryPrefix As String)
Dim n As Integer
Dim v As Variant
Dim s As String
Dim qdf As QueryDef

' If Me.fDebug = True Then Stop

DoCmd.Hourglass True
DoCmd.SetWarnings False

On Error GoTo Err_Exit

'insert blank rows in debug frame to separate query names executed
Debug.Print vbCrLf

n = 0
For Each qdf In CurrentDb.QueryDefs
s = qdf.Name
If InStr(1, s, sQueryPrefix) > 0 Then
s = qdf.SQL
If InStr(1, s, "UPDATE") > 0 Then
n = n + 1
v = SysCmd(acSysCmdSetStatus, "(" & n & ") Query " & qdf.Name & " is executing.")
'qdf.Execute dbFailOnError
Debug.Print qdf.Name
DoCmd.OpenQuery qdf.Name
End If
End If
Next qdf

Exit_Exit:
v = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Sub

Err_Exit:
MsgBox "UNEXPECTED FATAL ERROR No. (" & Err.Number & ") " & Err.Description & _
vbCrLf & vbCrLf & _
"Error at Switchboard, btnExports, sequence " & n & "; query -prefix: [" & sQueryPrefix & "]"
Err.Clear
Resume Exit_Exit

End Sub

The routine argument is the pass through query name. The runtime is called with 190 various pass through query names. The timeout on the query previously posted.


Thanks again for your effort and courtesies.
I'm updating data, not adding new records.
 

mdlueck

Sr. Application Developer
Local time
Today, 16:04
Joined
Jun 23, 2011
Messages
2,631
I'm updating data, not adding new records.

I understood that. Thus my suggestion to cook up one special record for the query to UPDATE.

And then I would hot-wire the VBA code to the one specific DAO.QueryDef object. If it can not perform the update to the one record in the one table... I think finding that out would be significant.
 

llkhoutx

Registered User.
Local time
Today, 15:04
Joined
Feb 26, 2001
Messages
4,018
ptTrim makes the posted and other complex updates which I pulled out, thinking several pass through update queriers whould run faster that one big complex pass through update queries.

That statement is true for Access local update queries, by analogy is it true for pass through update queries?

I'll run a test, to see it that helpd.:banghead:
 

mdlueck

Sr. Application Developer
Local time
Today, 16:04
Joined
Jun 23, 2011
Messages
2,631
That statement is true for Access local update queries, by analogy is it true for pass through update queries?

Best is to use a Stored Procedure. Either use ADO objects to EXEC them, or if you need to download into a FE temp table then use nested DAO.QueryDef objects.

Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

Next best is to use a Pass-Through query. Personally I got annoyed with troubles getting large query strings to make it all the way to the SQL BE DB. The SQL needed to EXEC a SP is FAR more simple than managing to get the entire query string passed through successfully.

Worst is a non Pass-Through query as Access mess's with the query, then the SQL BE DB gets what Access messed with and has to make sense of a second hand query.
 

Users who are viewing this thread

Top Bottom