Lee-
There are also some settings that affect this in Access Options / Advanced that could be overriding what you're trying to do directly in the connection.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On May 22, 2014, at 6:30 PM, leevt99@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi,
I've got an SQL server back end connected to access front end. Recently I've started to receive the following error:
-2147217871 [Microsoft][ODBC SQL Server Driver]Query timeout expired
All the research I've done points me to updating the timeout expiration, but I've done that (as I describe below) and it doesn't help.
I do a RPC to run a stored proc, what I've noticed is when the proc processes over 450,000 rows (it's stored on the server so it's not "returning" it as far as I know) I get the timeout error. It works fine with less rows (which are also processed faster). I figured the amount of time COULD have something to do with it, because I know the timeouts default to 30 seconds, but even after I change the timeout to 0 (unlimited) I get the same error. Any ideas? Could this have anything to do with the AMOUNT of data? Or would optimizing the query in SQL server to speed things up be the solution? Let me know if you need other info (the stored proc is basically 1 delete query to clear the table, 1 insert query, and an update query to tack on a few values and remove a join in the insert query - nothing too complicated)
Thanks
- Lee
Here's the code in Access (it bombs on cmd.execute)
Private Sub UpdateExportTableSQLServerRPC(ReportName As String, WeekEndDate As Variant)
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
'==================================================================
On Error GoTo DealWithErrors
Set conn = New ADODB.Connection
'get connection string from one of the tables in case the dsn changes
conn.ConnectionString = CurrentDb.TableDefs("tblCoBuDept").Connect
'test----------------------------------------------
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
'end test----------------------------------------
conn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "UpdatePayrollDetailReport"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@ReportName", adVarChar, adParamInput, 255)
cmd.Parameters("@ReportName").Value = ReportName
cmd.Parameters.Append cmd.CreateParameter("@WeekEnding", adDBTimeStamp, adParamInput)
cmd.Parameters("@WeekEnding").Value = WeekEndDate
cmd.Execute
conn.Close
Exit_UpdateExportTableSQLServerRPC:
On Error GoTo 0
Set conn = Nothing
Set cmd = Nothing
Exit Sub
DealWithErrors:
MsgBox Err.Number & " (" & Err.Description & ")", vbCritical, "Error!!!"
Resume Exit_UpdateExportTableSQLServerRPC
End Sub
I've got an SQL server back end connected to access front end. Recently I've started to receive the following error:
-2147217871 [Microsoft][ODBC SQL Server Driver]Query timeout expired
All the research I've done points me to updating the timeout expiration, but I've done that (as I describe below) and it doesn't help.
I do a RPC to run a stored proc, what I've noticed is when the proc processes over 450,000 rows (it's stored on the server so it's not "returning" it as far as I know) I get the timeout error. It works fine with less rows (which are also processed faster). I figured the amount of time COULD have something to do with it, because I know the timeouts default to 30 seconds, but even after I change the timeout to 0 (unlimited) I get the same error. Any ideas? Could this have anything to do with the AMOUNT of data? Or would optimizing the query in SQL server to speed things up be the solution? Let me know if you need other info (the stored proc is basically 1 delete query to clear the table, 1 insert query, and an update query to tack on a few values and remove a join in the insert query - nothing too complicated)
Thanks
- Lee
Here's the code in Access (it bombs on cmd.execute)
Private Sub UpdateExportTableSQLServerRPC(ReportName As String, WeekEndDate As Variant)
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
'==================================================================
On Error GoTo DealWithErrors
Set conn = New ADODB.Connection
'get connection string from one of the tables in case the dsn changes
conn.ConnectionString = CurrentDb.TableDefs("tblCoBuDept").Connect
'test----------------------------------------------
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
'end test----------------------------------------
conn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "UpdatePayrollDetailReport"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@ReportName", adVarChar, adParamInput, 255)
cmd.Parameters("@ReportName").Value = ReportName
cmd.Parameters.Append cmd.CreateParameter("@WeekEnding", adDBTimeStamp, adParamInput)
cmd.Parameters("@WeekEnding").Value = WeekEndDate
cmd.Execute
conn.Close
Exit_UpdateExportTableSQLServerRPC:
On Error GoTo 0
Set conn = Nothing
Set cmd = Nothing
Exit Sub
DealWithErrors:
MsgBox Err.Number & " (" & Err.Description & ")", vbCritical, "Error!!!"
Resume Exit_UpdateExportTableSQLServerRPC
End Sub
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar