March 25, 2009 at 10:26 am
Select RevID into #temp
From OBEAVER.REV_PA.dbo.Budget Where Year(Adddate) >= 2004
group by RevID
Here is the error.
OLE DB provider "SQLNCLI" for linked server "OBEAVER" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "OBEAVER" 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 "OBEAVER". .
My link server is set to " 0 " timeout and remote connection settings on server OBEAVER also set to 0. but why is this error again?
March 25, 2009 at 12:07 pm
Check out Query Governor setting on your SQL Server.
I had a similar message when my Query Governor was turned on; it is designed to prevent long-running queries. I changed back to default and starting working properly.
Right Click on Server
Properties
Connections
Uncheck "Use query governor to prevent long-running queries"
or
Change the value in the box below.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 12:37 pm
that option is already unchecked, we usually dont check that option.
March 25, 2009 at 1:58 pm
Found this KB article ... http://support.microsoft.com/kb/314530
I'll keep looking...
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 2:01 pm
Mike, do not cross post, that is useless and only fragments answers.
Future posts should be posted here
http://www.sqlservercentral.com/Forums/Topic683505-338-1.aspx
Cheers,
J-F
March 26, 2009 at 6:58 am
Usually this us happening when users pull large amounts of data from the views through link servers. I am not sure how to manage these data reads from cross over servers.
Here is the thing.
All out databases has data around not less than 1 TB and they do access through views. most of our views include simple select statements which do union all from multiple tables and to read the data from the views it takes few hours as we have 100's of million of data.
Is this the right way to access data cross over servers and how do i avoid this kind of time out errorr though all our servers were setup for unlimited connection time and query time.
March 26, 2009 at 7:18 am
Here is a sample view , almost all our views are like this.
ALTER view [dbo].AllRevBudget as
select *
from RevPa rp
where rp.RevID IN ('CA', 'GA')
UNION ALL
select *
from Revpt rt
where REvID IN ('CA', 'GA')
March 26, 2009 at 8:26 am
Here is my configuration:
SELECT * FROM sys.configurations
WHERE configuration_id = 1541
1541query wait (s)-1-12147483647-1maximum time to wait for query memory (s)11
March 26, 2009 at 8:44 am
Could some one let me know the diffrence here.
1. Query Time out Expired
2. Execution terminated by the provider because a resource limit was reached.
3. Query Wait time - what happens if this value is set to -1, is that the max to get the result without getting errors.
March 26, 2009 at 10:10 pm
Mike Levan (3/26/2009)
Could some one let me know the diffrence here.1. Query Time out Expired
2. Execution terminated by the provider because a resource limit was reached.
3. Query Wait time - what happens if this value is set to -1, is that the max to get the result without getting errors.
Hi Mike,
1. Query Time out indicates that the Query wait time was reached.
3. Query Wait time is the maximum time a SQL Server is going to wait on resources (Network I/O, Memory, Disk I/O) before it times out the query. The default setting of -1 indicate take the estimated cost of query and times it by 25. So if your query takes 1 Min on your remote server (executed at the server) it will wait 25 min before timing out the query.
Ref: http://blogs.solidq.com/EN/rdyess/Lists/Posts/Post.aspx?ID=16
2. Resource Limit: One of the resources was taxed out, CPU, Memory, or Network. To avoid this as suggested in the linked post pull over less data, is a solution.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply