March 25, 2009 at 11:22 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 11:41 am
Hi
Mike Levan (3/25/2009)
The OLE DB provider "SQLNCLI" for linked server "OBEAVER" reported an error. Execution terminated by the provider because a resource limit was reached.
Sounds like a resource problem. Maybe try OPENQUERY to let the remote server do the grouping and only return the result:
SELECT *
INTO #temp
FROM OPENQUERY(OBEAVER,
'Select RevID From REV_PA.dbo.Budget Where Year(Adddate) >= 2004 group by RevID')
Greets
Flo
March 25, 2009 at 1:16 pm
Bad Idea to run such a query over a linked server if the table is big!
* Noel
March 25, 2009 at 1:27 pm
Hi Noel
So I thought the OPENQUERY might be a better solution. I don't really know if the data are too much before or even after the grouping. Maybe Mike has some additional information about this.
Greets
Flo
March 25, 2009 at 1:30 pm
Florian Reischl (3/25/2009)
Hi NoelSo I thought the OPENQUERY might be a better solution. I don't really know if the data are too much before or even after the grouping. Maybe Mike has some additional information about this.
Greets
Flo
Flo,
Totally agree with you, sending the query over with OPENQUERY does away with the pulling of non-summarized data across!
Good Call.
I am, on top of that, concerned with ... Where Year(Adddate) >= 2004 which is not SARGable .
* Noel
March 26, 2009 at 7:03 am
does it mean OPENQUERY will use less resource than regular query? what happens when openquery is used with respect to the timeout and using resources.
March 26, 2009 at 7:29 am
Mike Levan (3/26/2009)
does it mean OPENQUERY will use less resource than regular query? what happens when openquery is used with respect to the timeout and using resources.
Nope.
The difference between OPENQUERY and a usual query with a linked server is that the OPENQUERY executes the query on the remote server and returns the result. A usual query with linked servers does the joins on the local server. So the data to group by (in your case) will be completely transferred to your server and grouped on this.
I don't know what to do if you get resource problems with OPENQUERY. Maybe work in batches.
Greets
Flo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply