Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ODBC error: Timeout expired Expand / Collapse
Author
Message
Posted Monday, April 6, 2009 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Hi,

I have an access application whose tables linked to SQL Server 2005. Last month also, it worked well. but this month, I have an problem while executing one query. it is showing the below error_

ODBC --call failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

I just some analysis and found that there is a problem with the table which has 3000 records. This error is coming when we try to filter the column InvYear =2009. It is working fine for the filter InvYear=2008 and all others. It is only giving the problem for 2009. Why it is giving the above error only for that condition. Last month also this query worked well. Is there any thing I need to check for the data. Any wrong data like NULLs or empty spaces causes this problem?

I am attaching screen shot of the table and error to get an Idea to you.





Thanks a lot,
Venki


  Post Attachments 
error.JPG (43 views, 57.47 KB)
Post #691012
Posted Tuesday, April 7, 2009 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Please help out. It's very urgent.

I analysed the problem and found that the table I mentioned is not the table in SQL Server 2005. It is a VIEW which is having another complex queries. The data is also around 150000 records.

But this worked of February month, but not working for March month. I didn't exactly understand what is exactly the problem.

Please give any suggession to fix this. I am not well experienced in Databases. But it is very urgent as user is waiting for the report to take?


Thanks a lot,
Venki
Post #691942
Posted Wednesday, April 8, 2009 4:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 17, 2012 2:51 AM
Points: 935, Visits: 251
Difficult to say without understanding the query you are running.

Timeout messages can be misleading, but maybe you could rule out a query timeout by increasing your query's ODBC timeout property from the default 60 seconds to a larger value.

If it is really being caused by the data in your view then you could attempt to troubleshoot.. I might try creating a smaller set of test records on the SQL Server end - perhaps SELECT TOP xxx records from your view, and see if your Access query runs with that data. It might help you identify particular data which are causing the issue.
Post #692898
Posted Wednesday, April 8, 2009 5:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 205, Visits: 1,341
Venki

"Timeout messages can be misleading, but maybe you could rule out a query timeout by increasing your query's ODBC timeout property from the default 60 seconds to a larger value"

Have set the default value from 30 ms to something more?
Access 2000 Menu Tools Options Advanced is the place to change.

What code are you running?
Do you have any connection or command timeout property set?

/Gosta

Post #692960
Posted Monday, April 13, 2009 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Thanks for your information.

I attached the options screen shot. Time out property set to 0 secs so it means infinity. So it would not the problem.

My Access application has a table. For that table, we again run a query to get the report. The query is as below__

SELECT ManHoursPerDistrictPerMonth.District, Sum(ManHoursPerDistrictPerMonth.SumReg) AS SumOfSumReg, Sum(ManHoursPerDistrictPerMonth.SumOT) AS SumOfSumOT
FROM ManHoursPerDistrictPerMonth
WHERE (((ManHoursPerDistrictPerMonth.InvYear)=Year(getStartdate())) AND ((ManHoursPerDistrictPerMonth.InvMonth)>=Month(getStartdate()) And (ManHoursPerDistrictPerMonth.InvMonth)<=Month(getEndDate())))
GROUP BY ManHoursPerDistrictPerMonth.District;

UNION SELECT "zTotal" AS District, Sum(ManHoursPerDistrictPerMonth.SumReg) AS SumOfSumReg, Sum(ManHoursPerDistrictPerMonth.SumOT) AS SumOfSumOT
FROM ManHoursPerDistrictPerMonth
WHERE (((ManHoursPerDistrictPerMonth.InvYear)=Year(getStartdate())) AND ((ManHoursPerDistrictPerMonth.InvMonth)>=Month(getStartdate()) And (ManHoursPerDistrictPerMonth.InvMonth)<=Month(getEndDate())));

If I delete the UNION and execute both queries separately then it is working fine with out any problem. If I keep the UNION and try to print the report giving the problem. This behavior causing me to look on Data count. As you know this View ManHoursPerDistrictperMonth in SQL Server is a complex view i.e it has complex queries inside and it has to work on 1,50,000 records. So Two times calling the same view and doing Union operation. This looks like it is very tedious job so the Query is failing to execute on huge amount of data, I suppose.

I couldn't decide where exactly the problem to precede further?


Thanks a lot,
Venki


  Post Attachments 
Access options.JPG (18 views, 38.68 KB)
Post #695768
Posted Tuesday, April 14, 2009 5:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 5:51 AM
Points: 1,511, Visits: 2,711
The basic problem is that you're bringing all the records into Access and then applying the date filter. I would suggest using a pass-thru query, following these steps. . .

-- create a stored procedure like this
CREATE PROC prcTest
(
@StartDate datetime,
@EndDate datetime
)
AS
SELECT ManHoursPerDistrictPerMonth.District, Sum(ManHoursPerDistrictPerMonth.SumReg) AS SumOfSumReg, Sum(ManHoursPerDistrictPerMonth.SumOT) AS SumOfSumOT
FROM ManHoursPerDistrictPerMonth
WHERE (((ManHoursPerDistrictPerMonth.InvYear)=Year(@StartDate)) AND ((ManHoursPerDistrictPerMonth.InvMonth)>=Month(@StartDate) And (ManHoursPerDistrictPerMonth.InvMonth)<=Month(@EndDate)))
GROUP BY ManHoursPerDistrictPerMonth.District
UNION SELECT "zTotal" AS District, Sum(ManHoursPerDistrictPerMonth.SumReg) AS SumOfSumReg, Sum(ManHoursPerDistrictPerMonth.SumOT) AS SumOfSumOT
FROM ManHoursPerDistrictPerMonth
WHERE (((ManHoursPerDistrictPerMonth.InvYear)=Year(@StartDate)) AND ((ManHoursPerDistrictPerMonth.InvMonth)>=Month(@StartDate) And (ManHoursPerDistrictPerMonth.InvMonth)<=Month(@EndDate)))
GO

note that I took your Access query, replaced the two date functions with parameters, and also removed the semicolon at the end of the first half of the query.

Then in Access, run these statements in the Immediate Window (Alt-F11, then Ctrl-G:

' do this from the Immediate Window
CurrentDb.CreateQueryDef ("qryTest")
CurrentDb.QueryDefs("qryTest").Connect = CurrentDb.TableDefs("one of your linked tables").Connect
CurrentDb.QueryDefs("qryTest").sql = "prcTest '',''"

Change your report to use qryTest as the data source.

Finally, in the button Click() event that opens the report, add this before you DoCmd.OpenReport

' do this in the button Click() event
CurrentDb.QueryDefs("qryTest").sql = "prcTest '" & Format(getStartdate(), "mm/dd/yyyy") & "', '" & Format(getEndDate(), "mm/dd/yyyy") & "' "

Post #697150
Posted Thursday, April 16, 2009 3:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
William,

Thanks for your help. I will try this example and tell you.

But a quick question is I don't think there is a difference between existing query and your query because mandayperdistrictpermonth is a view in sql server and a table in Access. The query which I sent is a query applied on the mandayperdistrictpermonth. I directly given the two functions in query and you just used parameterized query which again takes the value of the same functions. I don't think there will be a difference in performance.

Any way, I will try and tell you.



Thanks a lot,
Venki
Post #698262
Posted Thursday, April 16, 2009 4:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 205, Visits: 1,341
Venki
Do you run the query as a query in Access?
In design view you have Query Properties there you can adjust the
ODBC Timeout. Default is 60 s. I run into the same problem as you describe
and fixed it.
I usually run the queries with VBA code and ADO and the timeout property can then
be set (connection and command) timeout.

/Gosta

Post #698277
Posted Thursday, April 16, 2009 5:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 5:51 AM
Points: 1,511, Visits: 2,711
venki (4/16/2009)
mandayperdistrictpermonth is a view in sql server and a table in Access.


Please allow me to explain.

If mandayperdistrictpermonth is a view in SQL Server, that is what it is. When you link to a view from Access, it looks like a table from the Access side, but that doesn't change the fact that it is a view.
Post #698309
Posted Thursday, April 16, 2009 5:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Gosta Munktell (4/16/2009)
Venki
Do you run the query as a query in Access?
In design view you have Query Properties there you can adjust the
ODBC Timeout. Default is 60 s. I run into the same problem as you describe
and fixed it.
I usually run the queries with VBA code and ADO and the timeout property can then
be set (connection and command) timeout.

/Gosta



Gosta,

Thanks a lot for your help. It's very funny really. The fix is very simple.

Yes, as you said, it is a query in Query.
I was not looked into query properties when every one suggested me to change the ODBC timeout property. I am looking into MS Access Applications Tools -> Options. And also in Tables properties thing it is a query in sql server.
The query is taking 70 minutes to execute the data. That' s why this is giving the error. After changing the Query properties -> ODBC timeout to 120, it is showing report correctly.

William,
Thanks for your valuable suggestions.

This problems learned me a new concept how to create passthru queries.


Thanks a lot to all for your great help.


Thanks a lot,
Venki
Post #698320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse