Statement(s) could not be prepared.
-
Today, a developer reported the following problem using a linked server:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Statement(s) could not be prepared.
I took the SQL and ran it on the server using Query Analyzer and received the following error:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'iso' does not match with a table name or alias name used in the query.
The query:
select distinct(users.country), iso.a2 as cc
from [UserDB].[WebBoardDB].[dbo].users as users
join [UserDB].[WebBoardDB].[dbo].iso_3166 as iso
on users.country=iso.country
order by users.country
This query had been working fine until today. If I change the "join" to "left join" the query works fine. If I comment out the "order by" clause the query works fine.
The developer is worried that he may have to go through his code and change his joins to left joins.
What would cause this behavior?
All I've been able to dig up talks to SQL Server 2000 and UPDATE queries.
Thanks for the help,
Greg
-
What are you expecting this DISTINCT to do ?
>>select distinct(users.country), iso.a2 as cc
You can't DISTINCT just 1 column in the resultset, if that is the intent of the parentheses.
-
Changing to LEFT JOINs would not be advised. What they do is tell SQL server to include every row in the set preceeding the LEFT JOIN no matter if a match occurs in the table specified in LEFT JOIN. They also peform worse than INNER JOINs and should be used only when needed.
What happens if you replace 'JOIN' with 'INNER JOIN'?
-
Jeff,
Thanks!
The query fails with INNER JOIN as well. If I remove the ORDER BY clause it works.
The developer doesn't want to change anything. This query has been working since Sept of 2002. Why now it has decided to fail is the mystery. I can run this query on the "Linked" server and it runs without incident. Both servers are SQL Server 7.0.
- Greg
-
Hi,
have you tried running this query through the Query Analyser with the Show Execution Plan or display Estimated Execution plan option. It may be that your statistics for one of these tables are out of date and you need to do an UPDATE STATISTICS.
David
If it ain't broke, don't fix it...
-
Hi,
I am having the same problem and It isn't related to bad statistics. ANy other ideas ?
-
Greg and Amita. I would highly recommend re-visiting what PW said. (I have read this site for some time now, and he is one of the better guru's.)
[ btw, where is Remi, Susheila, Frank Kalis, etc?????? I miss you'se guys ]
I wasn't born stupid - I had to study.
-
You cannot order by value not mentioned in SELECT list.
There is no column "users.country" in your SELECT list, there is only "DISTINCT(users.country)".
Quote from BOL:
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
So, follow the orders.
_____________
Code for TallyGenerator -
This is the error message I get. The query if run separately from query analyzer works fine. but inside a stored proc gives this error, consistently. Any ideas ? Any help is appreciated. Thanks!
Msg 8180, Level 16, State 1, Server SQLMPT03, Procedure PayRefresh, Line 1258
Statement(s) could not be prepared.
Msg 107, Level 16, State 2, Server SQLMPT03, Procedure PayRefresh, Line 1258
The column prefix 'rap' does not match with a table name or alias name used in the query.
Query
insert @RemittanceAdvice
(
RemittanceAdviceID
,ReferenceNbr
,ClearedDate
)
select rap.RemittanceAdviceID
,ReferenceNbr = rap.PaymentReferenceNbr
,rap.ClearedDate
from SQLMPT03.Db1.dbo.RemittanceAdvicePayment rap
join SQLMPT03.Db1.dbo.RemittanceAdviceHeader rah
on rap.RemittanceAdviceID = rah.RemittanceAdviceID
join RemittanceAdvice ra with (nolock)
on ra.RemittanceAdviceID = rap.RemittanceAdviceID
join db2.dbo.Producer p with (nolock)
on p.ParentProducerID = ra.ParentProducerID
where rap.APRUpdateDate >= @LastRefreshTime
and p.APICustomerInd = @TRUE
and rap.PaymentSeqNbr = (select max(rapMax.PaymentSeqNbr)
from SQLMPT03.db1.dbo.RemittanceAdvicePayment rapMax
where rapMax.RemittanceAdviceID = rap.RemittanceAdviceID)
(UserID=357053)
-
Where this "(UserID=357053)" is dropped from?
_____________
Code for TallyGenerator -
Sorry, [that's the cut & paste problem. It's nto a part of the query.
-
Probably we need to see full query.
I used to get such kind of errors when there were too many derived tables or subqueries in my statement.
It was long time ago, I can't remember exactly, sometimes it was "Internal Server Error", sometimes "Statement(s) could not be prepared".
I believe this issue is related to distributed transactions settings.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply