SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query timeout expired
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

juicyapple
Posting Yak Master

176 Posts

Posted - 02/11/2008 :  02:35:47  Show Profile  Reply with Quote
Hi, I am doing insert data from table A to B but I get this error message. Does this because of the insufficient disk space problem? I have checked on it, the disk space is still more than enough...Please advise.

OLE DB provider "SQLNCLI" for linked server "SERVER5" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "SERVER5" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 1
Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "SERVER5". .



Thanks.

tprupsis
Yak Posting Veteran

USA
88 Posts

Posted - 02/11/2008 :  11:55:18  Show Profile  Visit tprupsis's Homepage  Send tprupsis a Yahoo! Message  Reply with Quote
I wouldn't think this is related to disk space. What database are you linking to?
Go to Top of Page

juicyapple
Posting Yak Master

176 Posts

Posted - 02/11/2008 :  21:19:56  Show Profile  Reply with Quote
I am executing a store procedure in SERVER1, which performs insert data from SERVER2.tableA to SERVER2.tableB, and the tableA consists of ~50000000 rows data. Any idea?
Go to Top of Page

tprupsis
Yak Posting Veteran

USA
88 Posts

Posted - 02/12/2008 :  01:35:03  Show Profile  Visit tprupsis's Homepage  Send tprupsis a Yahoo! Message  Reply with Quote
Your error message refers to SERVER5, but you only mention SERVER1 and SERVER2. What version of SQL Server are you using? In SQL 2005, look at your server properties in SQL Server Management Studio. On the Connections page, there is a timeout value for remote queries. On my local SQL Express instance, this is defaulted to 600 seconds. You might try increasing that if your query of 50M records runs longer than that.
Go to Top of Page

juicyapple
Posting Yak Master

176 Posts

Posted - 02/12/2008 :  02:21:24  Show Profile  Reply with Quote
Your error message refers to SERVER5, but you only mention SERVER1 and SERVER2. What version of SQL Server are you using?

Sorry, should be like this,
1.In SERVER1 SQL 2005, executing store procedure to insert data from linked server SERVER5.tableA to SERVER5.tableB.
2.SERVER 5 is using SQL 2K.


On the Connections page, there is a timeout value for remote queries. On my local SQL Express instance, this is defaulted to 600 seconds. You might try increasing that if your query of 50M records runs longer than that.
SERVER1 uses the default remote query timeout = 600 seconds.

If I increase the timeout value, it also affects all the applications connected to the database?
Go to Top of Page

tprupsis
Yak Posting Veteran

USA
88 Posts

Posted - 02/12/2008 :  11:12:55  Show Profile  Visit tprupsis's Homepage  Send tprupsis a Yahoo! Message  Reply with Quote
Yes, that setting should impact anything sending a remote query to the server. I think you'll need to adjust the default remote query timeout on SERVER5 though, not on SERVER1. I would assume Enterprise Manager has a similar setting, but I couldn't tell you where to find it.

There's also a Query Timeout setting in your linked server properties. That defaults to 0, which uses the remote server's default. If you need to increase the timeout for just queries issued through the linked server, you might try adjusting that property instead.
Go to Top of Page

juicyapple
Posting Yak Master

176 Posts

Posted - 02/12/2008 :  20:14:55  Show Profile  Reply with Quote
I do not want to change the default remote query timeout as this may affect others application. If I increase only the timeout value through a linked server properties, means it is only applicable to all queries through linked server?
Go to Top of Page

Rina
Starting Member

Malaysia
2 Posts

Posted - 08/05/2009 :  03:21:18  Show Profile  Reply with Quote
Hi
I get this error

"Query timeout expired" Description: "Query timeout expired" Help File: "(null)" Help Context: 0 GUID: {0C733A63-2A1C-11CE-ADE5-00AA0044773D}
OLE DB Error Records for hr = 0x80040e31"

Could you tell me what is wrong?
I get this error at my local machine .

Im using the sql2005 Express


Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/05/2009 :  03:27:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See http://support.microsoft.com/kb/314530


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

CLS
Starting Member

Hungary
1 Posts

Posted - 05/10/2010 :  02:54:21  Show Profile  Reply with Quote
@tprupsis had answered the question.
If you connect to an SQL server, you can specify query timeout.
If you connect to a linked server from an another connection, the destination server specifies the query timeout.
This can be changed with SQL Server Management Studio, connect to the destination server as "sa", right click the server, click "Properties", go to the "Connections" page set "Remote query timeout".
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000