September 28, 2007 at 10:26 am
Hi everyone,
I am using a reporting application which connects to a link server using OLE DB Provider for SQL Server. Link Server is connected to a FoxPro database by using OLE DB for FoxPro.
When I test my query using "SQL Server 2000 Query Analyzer", I can get the result from the link server without anny problem.
However, when I try to run the same query from the reporting application, I am getting this message:
"Microsoft OLE DB Provider for SQL Server: Timeout expired."
Anyone can help me?
Thanks
October 10, 2007 at 2:11 pm
Increase the timeout period under tools , Advanced settings and then u try increasing timeout under Rt.clk on server ->properties ->connections->query time out from 600 secs to more.
October 11, 2007 at 6:21 am
What are you using for security to connect to the FoxPro database?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 25, 2007 at 5:18 pm
I changed the timeout settings to umlimited (timeout = 0)
Still getting the same error for the large tables.
I don't thing the error is related with the security because when I delete some of the table columns from its view, I can get the results without timeout error.
Any ideas?
October 26, 2007 at 11:36 am
CLIENT SIDE:
If the query is reading from large table, (100 columns x 20000 rows)
I have no problem getting results using SQL Query Analyzer on the Client side.
However, I am getting timeout problem from the client side application.
The query failed. The message from the database engine was:
Microsoft OLE DB Provider for SQL Server: Timeout expired.
SERVER SIDE:
I tested the same query on the server using the application. I can get the results.
ENVIRONMENT:
Server machine:
The Server : Windows 2003 Server SP2
Database Server : SQL Server 2000 – (8.00.2039 Standard Edition SP 2) uses linkserver (OLE DB 9.0.0.3504 ) to FoxPro 9.0 SP1 table
: Timeout Settings: Query time-out (sec, 0=unlimited)
Client machine:
Windows XP SP2 : Windows Network Authentication
SQL Server 2000 client
For some reason my environment doesn’t like the outside application to connect to the server long time?
Do you have any idea how to fix this timeout problem? Do I need to configure DCOM or DTC?
November 7, 2007 at 2:17 am
Hi,
You are getting the error at the client side and not the server side. It means changing the server configuration will not achieve anything. Instead you have to change the settings at the client side through code. e.g. in VB if we set property
cnn.CommandTimeout
This handles the timeout error. See if you can do such settings in your code.
November 7, 2007 at 10:29 am
Thank you for the advice, it was helpful.
The client-side is very thin, it doesn't hold any component except a pointer to the application which is running on the server side.
I added custom function script to the server side application for the ADO object timeout settings.
You were right it wasn't the issue related with windows server
Thanks again
January 1, 2008 at 1:50 pm
try this -- this work magic
UPDATE STATISTICS Sales.SalesPerson
WITH FULLSCAN --- not even about timeout or changing the parameter !! [ this apply to not well tuned database table / index ]
problem : timeout expired
this solution is magic
I try to configure the timeout parameter by right clicking on ther SERVER NAME
and change the report manager file
it does not work
===== but this one work magic (reminder: table has not even been index yet), so this is wonderful ===
use AdventureWorks
go
UPDATE STATISTICS Sales.SalesTerritory
WITH FULLSCAN
go
UPDATE STATISTICS Sales.SalesPerson
WITH FULLSCAN
go
UPDATE STATISTICS HumanResources.Employee
WITH FULLSCAN
go
UPDATE STATISTICS Person.Contact
WITH FULLSCAN
go
UPDATE STATISTICS Sales.SalesOrderHeader
WITH FULLSCAN
go
=========== and then I run this sample report (territory) on AdventureWorks again ===
use AdventureWorks
go
SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
FROM Sales.SalesTerritory ST INNER JOIN
Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
ORDER BY ST.Name
------ I think this happen again and again in SQL Server, esp new, not performance tuned database, but people always overlook the UPDATE STATISTICS -- thanks
xx3xxx
January 3, 2008 at 6:54 am
We faced a similar problem. The query performance was better when executed through SQL Query Analyzer but it got timed out when executed through the application.
We tried the update statistics and it worked fine.
But we are unable to understand, how the execution plans can differ between query analyzer and remote application.
Any way thanks for the suggestion.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply