March 3, 2004 at 6:40 am
I use SQL server 2000 as a back end to access 2000.
I have a VBA script in access that uses the transferdatabase function to transfer a database to SQL server.
When I have more than 120,000 records in access, I get this error in access:
>[Microsoft][ODBC SQL Server Driver][SQL Server] Timeout Expired
Is there any parameter that I could set to change the timeout value . If so, where is this setting done.
Any ideas , suggestions would be appreciated.
March 3, 2004 at 6:58 am
Are you using ADO to connect? If so you can set both the Connection and the command timeouts as follows:
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30
March 3, 2004 at 7:09 am
Use the ConnectionTimeout property of the Connection Object and set an appropriate value for it.
Cheers!
Arvind
March 3, 2004 at 7:17 am
Actually I am not sure if it is ADO or DAO. I have taken over this code and am relatively new to access. The problem code is as follows
Call DoCmd.TransferDatabase(acExport, "ODBC Database", _
"ODBC;DSN=Volume_Control;UID=sa;PWD=xxxx!", _
acTable, "tblFilename" & processPane.ListItems(ctr), "tblTemperooeste", False, False)
There is a SQL server sproc being executed using ADO in the same sub as follows, but no connection before the transferdatabase function.
cmd.ActiveConnection = "DSN=Volume_Control;database=Volume_Control;UID=sa;PWD=xxxx!"
cmd.CommandText = "dbo.migrateTempToSeries 'tblTemperooeste'"
cmd.Execute , , ADODB.adExecuteNoRecords
Could I use the timeout function directly or do I have to setablish a connection before I do that ?
Thanks for any help.
March 3, 2004 at 9:50 am
Docmd.TransferDatabase does not have timeout option and neither does your DSN you are using to see database.
Are you willing to recode the solution? Quickist is to make DTS package and call from SQLDMO in your access code.
Just set a reference to Microsoft DTSPackage Object Library and ...
Dim oDtsPackage As New DTS.Package
oDtsPackage.LoadFromSQLServer "YOURSERVERNAME", , , DTSSQLStgFlag_UseTrustedConnection, , , , "YOURDTSPACKAGENAME"
oDtsPackage.Execute
oDtsPackage.UnInitialize
Set oDtsPackage = Nothing
There is a cool article on this at http://www.sqldts.com/default.aspx?208
The previous post refer to using ADODB.Connection and ADODB.Command to open connection and execute command on database like this...
'// First set reference to ADO 2.5 or better
'// Now define a connection string and don't show us the sa password!!!
Public Const CONN_STRING = "PROVIDER=SQLOLEDB;SERVER=MYSERVERNAME;UID=SA;PWD=NOTTELLINGYOU;DATABASE=THEDATABASE"
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oPrm As ADODB.Parameter
Set oConn = New ADODB.Connection
With oConn
.ConnectionTimeout = "15"
.CommandTimeout = "60"
.CursorLocation = adUseServer
.Mode = adModeRead
.Open CONN_STRING
End With
Set oCmd = New ADODB.Command
With oCmd
.CommandTimeout = "360"
.CommandText = "P_YOURPROC"
.CommandType = adCmdStoredProc
Set oPrm = .CreateParameter("Return", adInteger, adParamReturnValue, , -1)
.Parameters.Append oPrm
Set oPrm = .CreateParameter("YOURPARAMETERS", adVarChar, adParamInput, 10, YOURVALUE)
.Parameters.Append oPrm
End With
Set oCmd.ActiveConnection = oConn
Set oRs = New ADODB.Recordset
With oRs
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.CacheSize = 500
End With
oRs.Open oCmd
but you have to define the stored procedure to do the work you need done. You can create stored procedures to bcp or dts data around.
Otherwise I recommend using stored procedure to do all the moving around of data you need. If you want to get at your Access MDB from SQL SERVER first define the MDB as a linked server and then use OPENQUERY fourpart names to get at the data.
Good Luck.
March 3, 2004 at 11:40 am
jhouston, thanku for your time and patience in explaining this to me. I will try this and post my results.
March 3, 2004 at 12:06 pm
Call DoCmd.TransferDatabase(acExport, "ODBC Database", _
"ODBC;DSN=Volume_Control;UID=sa;PWD=xxxx!", _
acTable, "tblFilename" & processPane.ListItems(ctr), "tblTemperooeste", False, False)
The above uses The Jet engine to retrieve remote data to override the behavior for a particular query you modify the ODBCTimeout property of the particular QueryDef object.
Because you are not using any query object you have modify the ENGINE GLOBALLY
And all you have to do is goto the following key on the registry
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC\
and modify
QueryTimeout = X (seconds)
HTH
* Noel
March 4, 2004 at 2:40 am
does there anyone using these tables (select , update, ....) when you insert your data ... if yes .. then you have a locking problem from the users that uses this data
you have to read more in using (with (nolock) ) .. in table hint when using select statment from userts to get data.
you have to transfer in time there is no users using this SQL database.. or adjust your other User select statement
Alamir Mohamed
Alamir_mohamed@yahoo.com
March 4, 2004 at 8:43 am
noeld, thanku very much for your suggestion. I have made the registry changes and the data will be coming in next week for me to test it on. It will be simpler than changing to ADO.
March 4, 2004 at 11:18 am
noeld, I just got new data and tested with the changed registry values. The process did not timeout, and ran successfully. Thanku very much for your suggestion.
March 22, 2005 at 9:17 am
Hi Triveni, I have the same problem. What value did you change your registry QueryTimeout value to? 0 - infinite or something higher like 300?
Thank you!
March 22, 2005 at 10:26 am
Serge,
I set mine to 480 which is enough for the maximum amount of data that I transfer.
thriveni
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply