June 10, 2009 at 4:26 pm
Hello. Here is the error message I'm getting....
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Here is the query I'm using....
select ma.memid, ma.attributeid, ma.thevalue, ma.effdate, ma.termdate,
substring(rtrim(ma.thevalue),len(rtrim(ma.thevalue))-1,2) countyCode
from [laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute ma
where ma.attributeid = 'asstcountycode' and
ma.effdate = (select max(ma2.effdate) maxEffDt
from [laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute ma2
where ma2.attributeid = 'asstcountycode' and
ma2.memid = ma.memid and
isnull(ma2.termdate,'12/31/2078 23:59:59') >= getdate()
)
I'm using SQL Server Management Studio 2005...using a linked server to version 2000....I have no access to the linked server properties, so I can't give you more details on that - sorry! However, when I take out the subquery, the query runs...but all I'm doing is getting the latest record basically? And I'm not using the alias 'Tbl1001' for anything, and it's not a column name in any of the tables. Any help would be great! thanks!
June 10, 2009 at 5:13 pm
1. Try always use INNER JOIN to a derived table instead of subquery.
2. Can you ask for a view on the remote server based on your query (with join, not subquery, of course). If yes then you may just call that view with appropriate WHERE clause.
3. If not, then consider using openquery or openrowset. In this case you move linked server reference 1 tier up, out of the query itself, and force the join to be done on remote server, without passing the data to local server.
_____________
Code for TallyGenerator
June 10, 2009 at 6:58 pm
This is whate of Sergiy's suggestions would look like:
select
ma.memid,
ma.attributeid,
ma.thevalue,
ma.effdate,
ma.termdate,
substring(rtrim(ma.thevalue),len(rtrim(ma.thevalue))-1,2) countyCode
from
[laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute ma
inner join (select
max(ma2.effdate) maxEffDt
from
[laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute ma2
where
ma2.attributeid = 'asstcountycode' and
ma2.memid = ma.memid and
isnull(ma2.termdate,'12/31/2078 23:59:59') >= getdate()) dt
on (ma.effdate = dt.maxEffDt)
where
ma.attributeid = 'asstcountycode'
June 10, 2009 at 8:27 pm
Actually my suggestion would look like this:
select ma.memid, ma.attributeid, ma.thevalue, ma.effdate, ma.termdate,
substring(rtrim(ma.thevalue), len(rtrim(ma.thevalue))-1,2) countyCode
from [laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute ma
inner join (select memid, attributeid, max(effdate) maxEffDt
from [laforge\laforgesql].qnxt_plandata_oh.dbo.memberattribute
where termdate is null OR termdate >= getdate()
group by memid, attributeid
where termdate is null OR termdate >= getdate()
) ma2 on ma2.memid = ma.memid and ma.attributeid = ma2.attributeid and ma.effdate = ma2.maxEffDt
where ma.attributeid = 'asstcountycode'
But it still will be loading 2 remote tables to local tempdb and join them locally.
To avoid this you better try to make them create a view on remote server:
CREATE VIEW qnxt_plandata_oh.LatestMemAttribute
AS
SELECT ma.memid, ma.attributeid, ma.thevalue, ma.effdate, ma.termdate,
substring(rtrim(ma.thevalue), len(rtrim(ma.thevalue))-1,2) countyCode
FROM qnxt_plandata_oh.dbo.memberattribute ma
INNER JOIN (select memid, attributeid, max(effdate) maxEffDt
from qnxt_plandata_oh.dbo.memberattribute
where (termdate is null OR termdate >= getdate() )
group by memid, attributeid
where (termdate is null OR termdate >= getdate() )
) ma2 on ma2.memid = ma.memid and ma.attributeid = ma2.attributeid and ma.effdate = ma2.maxEffDt
and then call it remotely like this:
SELECT ma.memid, ma.attributeid, ma.thevalue, ma.effdate, ma.termdate, ma.countyCode
FROM [laforge\laforgesql].qnxt_plandata_oh.dbo.LatestMemAttribute ma
WHERE ma.attributeid = 'asstcountycode'
_____________
Code for TallyGenerator
June 10, 2009 at 8:50 pm
Sergiy,
Double check for me, but I think the code in the original post is missing a group by. I didn't catch that as I reformatted the code. That could also be part of the problem.
Good catch.
Edit: That's one reason I dislike poorly formatted code. Hard to catch things like that.
June 10, 2009 at 10:11 pm
Lynn Pettis (6/10/2009)
Sergiy,Double check for me, but I think the code in the original post is missing a group by. I didn't catch that as I reformatted the code. That could also be part of the problem.
Good catch.
Edit: That's one reason I dislike poorly formatted code. Hard to catch things like that.
Original code did not have GROUP BY because it was a correlated subquery executed once for each memid.
They name it "hidden cursor" (I'm telling it for OP, sure Lynn heard something about it 😉 ) and it's, let's say, not recommended on any kind of queries, and it's terribly bad on remote queries.
Effectively, it starts remote transaction as many times as many records are affected by main query. And al within the same remote transaction where main data set is retrieved.
No wonder server goes nuts. Who would not? 😛
By replacing it with derived table we reducing number of cross-server queries to 2.
But we still need to load raw data to local server and work it out here.
By going with the view we process data on the remote server, where the data is, and loading just final recordset within a simple remote call.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply