I'm getting a time out issue when reading some data from an Access table into SQL Server 2005. The query I am running is this
Code: Select all
SELECT DISTINCT derivedtbl_1.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Program Files\Applix\Custom\TM1Data\Wayfarer\CurrentMDB\live.mdb'; 'Admin'; '', Trans) AS derivedtbl_1 CROSS JOIN dbo.updateMaxKey WHERE (derivedtbl_1.anTransKey > (SELECT MaxKey FROM dbo.updateMaxKey AS updateMaxKey_1 WHERE (TableName = 'Trans')))
Now there is a setting in the registry that controls the ODBC Timeout settings here HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout
and detailed on the M$ site
If I was using ADO to connect then I could modify this property at run time there are plenty of examples of something very similar to this on the web
I'm guessing that I can do the same in the SQL statement but I can't for the life of me figure the correct syntax for the provider string in the OpenRowSet or anywhere else in the SQL statement.
Has anyone done this before or can categorical say that this is not possible? Google is no longer my friend!