April 1, 2008 at 9:36 am
I seem to have hit a little bug which doesnt make much logical sense! I have written a small query on a 2000 server (freedom) which works fine. However when i try and run it from a 2005 box using a linked server to the 2000 box i get the following error...
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.
The query is shown below...
select VD.ID, VD.FirstName, VD.Surname, VD.EmailAddress, VD.DateOfVisit
from FREEDOM.ADVTEST.DBO.FEQ_VisitorDetails VD
left join (select top 1 visitorid from FREEDOM.ADVTEST.DBO.FEQ_ApplicationReference where dateofApp > dateadd(mm,-1,getdate()) order by id desc)
as AR on AR.visitorID = VD.ID
where (select max(PageID) from FREEDOM.ADVTEST.DBO.FEQ_PageVisits where visitorid = VD.ID and visitdatetime > dateadd(mm,-1,getdate())) = 2
and AR.Visitorid is null
order by VD.ID desc
i can quite happily run
SELECT * FROM FREEDOM.ADVTEST.DBO.FEQ_VisitorDetails
SELECT * FROM FREEDOM.ADVTEST.DBO.FEQ_ApplicationReference
SELECT * FROM FREEDOM.ADVTEST.DBO.FEQ_PageVisits
and they return fine.
Even the first 2/3rds of the query works!!
select VD.ID, VD.FirstName, VD.Surname, VD.EmailAddress, VD.DateOfVisit
from FREEDOM.ADVTEST.DBO.FEQ_VisitorDetails VD
left join (select top 1 visitorid from FREEDOM.ADVTEST.DBO.FEQ_ApplicationReference where dateofApp > dateadd(mm,-1,getdate()) order by id desc)
as AR on AR.visitorID = VD.ID
So it seems there is a problem in the where statement linking to an external reference (VD.ID) when using a linked server from 2005 to 2000.
I tried the same query from a 2000 box using the same login and it works fine.
Anyone come across this issue before and have a workaround? If not i will have to build a table on the 2005 box and then query it directly from 2005 rather than trying to build the table as i go along on the remote server (which might be better anyway!)
April 1, 2008 at 11:58 am
Hi,
Let us know the error message you are getting. Also the Sql Server versions of Target and Source Servers.
regards,
Som
April 1, 2008 at 12:50 pm
It looks to me that you're only accessing tables on the linked server. If so - have you considered using OPENQUERY to run this?
Something like:
select * from OPENQUERY(Freedom,'select VD.ID, VD.FirstName, VD.Surname, VD.EmailAddress, VD.DateOfVisit
from ADVTEST.DBO.FEQ_VisitorDetails VD
left join (select top 1 visitorid from ADVTEST.DBO.FEQ_ApplicationReference where dateofApp > dateadd(mm,-1,getdate()) order by id desc)
as AR on AR.visitorID = VD.ID
where (select max(PageID) from ADVTEST.DBO.FEQ_PageVisits where visitorid = VD.ID and visitdatetime > dateadd(mm,-1,getdate())) = 2
and AR.Visitorid is null
order by VD.ID desc')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 7:48 pm
In my experience the best way to bring data across from a linked server, especially in your situation when you are not joining between local and linked server, is to write an SP on the linked server and call the SP from the local server. This offloads processing to the linked server and limits network traffic. I have seen a select from a linked server go from minutes to seconds by converting it to an SP on the linked server.
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
April 2, 2008 at 1:38 am
som (4/1/2008)
Hi,Let us know the error message you are getting. Also the Sql Server versions of Target and Source Servers.
regards,
Som
The error message is at the top....
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
Freedom is target server on sql 200 Sp3a
Source is sqlmaster on sql 2005 SP2
April 2, 2008 at 1:40 am
Matt - I would consider using openquery, however i think i like Jacks idea the best. I will give them both a go and let you know the results.
April 2, 2008 at 4:54 am
ok guys ive gone for a mix of the two. Ive created an SP and then call that SP from an OPENQUERY Command bringing the results into a local table as i then have to use the result set to link to other local tables (sorry i didnt mention that earlier!)
Thanks all for your help. Lets hope though that MS do fix this bug - or i get the budget to upgrade the 2000 box to 2005 and then i wont have the problem 😛
April 3, 2008 at 7:32 am
It sounds to me like you need to upgrade your SQL Server 2000 SP3 instance to SP4, run the instacat.sql file to update the system tables, and you should be able to query simply with
SELECT id FROM LinkedServerName.DatabaseName.Ownername(dbo).TableName. SQL Server 2005 doesn't play nice with SQL Server 2000 with SP3.
August 12, 2010 at 12:27 pm
I'm not sure if this board leans toward opening new topics or pulling up old ones, but I wanted to share a really bizarre experience related to this issue.
I am migrating my DTS packages from SQL 2000 server to SQL 2005. The query I used to review the existing packages worked fine:
SELECT
[name],
[description],
createdate,
[owner]
FROM [LinkedServerSQL2K].msdb.dbo.sysdtspackages p
where [name] in ('A few', 'packages', 'I still need') and
createdate =
(SELECT MAX(createdate) FROM [LinkedServerSQL2K].msdb.dbo.sysdtspackages WHERE [name] = p.name)
order by [name]
The reason for MAX is that the sysdtspackages table contains multiple versions of the same DTS package.
However, when I modified this query for the INSERT, it stopped working.
SELECT [name]
,[id]
,[versionid]
,[description]
,[categoryid]
,[createdate]
,[owner]
,[packagedata]
,[owner_sid]
,[packagetype]
FROM [LinkedServerSQL2K].msdb.dbo.sysdtspackages p
where [name] in ('A few', 'packages', 'I still need') and
createdate =
(SELECT MAX(createdate) FROM [LinkedServerSQL2K].msdb.dbo.sysdtspackages WHERE [name] = p.name)
The error message I get is:
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.
After playing "spot the difference" with the queries, I determined that it was the ORDER BY clause that made it work. With the addition of ORDER BY [name] to the second query, it ran correctly.
Until I tried to execute the INSERT. Same error. Tried to run the query to a temp table, same error.
I can get around it easily enough, but I wish I understood the underlying logic.
In conclusion, this appears to be a known issue with SQL Server 2000 linked servers, and it may be helpful to people facing it to try adding ORDER BY clause to their queries.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply