April 6, 2009 at 7:45 am
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.
Thank You
April 7, 2009 at 6:59 am
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?
Thank You
April 8, 2009 at 4:08 am
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.
April 8, 2009 at 5:46 am
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
April 13, 2009 at 7:23 am
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?
Thank You
April 14, 2009 at 5:17 pm
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") & "' "
April 16, 2009 at 3:58 am
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.
Thank You
April 16, 2009 at 4:27 am
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
April 16, 2009 at 5:37 am
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.
April 16, 2009 at 5:53 am
Gosta Munktell (4/16/2009)
VenkiDo 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.
Thank You
August 20, 2015 at 8:04 am
Thanks guys, this easy fix solved my problem as well.
August 21, 2015 at 3:08 am
Hi Bill, why is there a comma in
CurrentDb.QueryDefs("qryTest").sql = "prcTest '',''"
?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply