Query on ODBC Timeout issues with OpenRowset

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 1790
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Query on ODBC Timeout issues with OpenRowset

Post by Steve Rowe » Thu Aug 27, 2009 7:00 am

Hi all,
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')))
Unfortunatly whilst this form works for many other Access tables it's not working for the table Trans since it is very large and it is taking a long time for the ODBC connection to open.
sqlerror.JPG
SQL Error
sqlerror.JPG (29.1 KiB) Viewed 2381 times
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!
Cheers,

David Usherwood
Site Admin
Posts: 1331
Joined: Wed May 28, 2008 9:09 am

Re: Query on ODBC Timeout issues with OpenRowset

Post by David Usherwood » Thu Aug 27, 2009 11:55 am

Hmmm....
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')))
I think you are trying to take in only the 'new' transactions from the Trans table, so the above query will be building a temp table in SQL Server to do the join. An alternative that might work better would be to take the whole of the trans table across, then do the join - or possibly just a select. Might take longer overall though.
If you could write the Maxkey _back_ to Access and join there that might work better but I'm guessing that you can't. And Access isn't a 'grownup' relational engine for that kind of job anyway.
How's Grace? Getting lots of sleep? But you're not, are you :)

User avatar
Steve Rowe
Site Admin
Posts: 1790
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Query on ODBC Timeout issues with OpenRowset

Post by Steve Rowe » Thu Aug 27, 2009 12:20 pm

Hi David,
Yeah you guess right I'm doing an incremental update of an Access table into SQL. I need to do this as I can query SQL from 64 bit TM1 but have not been able to get to Access from the same. The Access DB is an operational DB that I can't really interfer with.

I actually don't have the problem when I execute the query in a stored procedure in SQL 2005 but I do get the error when I execute the same SQL manually Hopefully this is not going to be a problem long term since I need to just leave the job running as a maintenance free overnight update.

Would be nice to know if there is a way to manipulate that parameter from SQL at run time though. Any have any ideas?

Everything is going OK with Grace, not having the drama that everyone makes out. She is only 4 weeks old though so I guess she is going to make up for that over the next 20 plus years!

Cheers,

Post Reply