OLE DB for SQL Server: TimeOut Expired

Question

OLE DB for SQL Server: TimeOut Expired

day-476284

SSCommitted

Points: 1844
More actions
September 28, 2007 at 10:26 am

#181967

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

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.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

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

day-476284 SSCommitted Points: 1844 More actions · Answer 3

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?

day-476284 SSCommitted Points: 1844 More actions · Answer 4

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?

dev81 SSC Rookie Points: 43 More actions · Answer 5

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.

day-476284 SSCommitted Points: 1844 More actions · Answer 6

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

misandrew SSC Enthusiast Points: 144 More actions · Answer 7

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

kanna Valued Member Points: 69 More actions · Answer 8

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.