[Microsoft] [ODBC SQL Server Driver] Timeout expired

[MikeTruscott]

Hi everyone!

I've got sql server (v7 I think) with a database linked to MS Access. Via a macro in Access I run a set of 20 queries, which with a smaller sample of data work, but not with the main dataset which has over 2 million rows.

It stops on the 12th query, which is the biggest one. I found what I thought was a rogue column and was able to correct it using SQL in the sample data but, again, it doesn't work with a full dataset.

Whenever I try to run a query now through Access or just in SQL Server I get the timeout error message above after 30 seconds or so.

I've tried to find the 'timeout settings' to change them to 0...this hasn't worked. I tried to create an index on the table to make it process the query faster and I get the same error message.

Hope I've made sense.....Any ideas/advice most appreciated!

Regards

Mike

 

[vbkris]

>>just in SQL Server I get the timeout error

do you get an ODBC error when you run it from SQL Query analyser also???

Known is handfull, Unknown is worldfull

 

[MikeTruscott]

Hello!

Yes, I do get the same error. I've managed to split the csv file up into smaller chunks, which eliminates this error but now some of the queries are taking over a day to run!

I'm wondering if changing the block size will make any difference, but don't know how to do it in SQL server. I'll stick another post on!

Cheers

Mike

 

[SQLSister]

In the first place do not use linked tables, access the data through stored procs and pass-through queries instead.

If you are importing data, do not use Access at all, use DTS instead. Breaking the data into chunks will make it go faster. But do not use a cursor or loop that runs one record at a time. This will make the import exponentially slower than using set based processes.

For more specific advice you will nede to post the queries.



"NOTHING is more important in a database than integrity." ESquared