November 15, 2004 at 8:02 am
Not a question, but a solution that may be helpful to others.
It sure bugged me for a long while.
As this may happen to others in a 'mingled' environment of SQL 2000 and SQL 7 - I am submitting it to the world at large.
This SQL causes a syntax error only when running this against one specific server here; and I couldn't find why it would cause a syntax error there when it worked just find on other SQL 2000 boxes. We try and keep each server in synch as far as configs and service packs.
On that server the following SQL will give a syntax error when the database currently in 'use' is a restored SQL 7.0 DB, but will appearently parse and run just fine when pointing at a DB created SQL 2000 DB and referring to the restored database.
And this error only happens with a COLLATE within a sub-query and not in a straight join.
SELECT
ap.cStudentSSN ,
max( x.iAppId ) as iAppId
FROM DBAWORK..XFER_App x
JOIN LAMS_RECON..App la on x.iAppId = la.iAppId
JOIN LAMS_RECON..AppPrivate ap on la.dAppDate = ap.dAppDate and la.iAppSeq = ap.iAppSeq
WHERE IsNull( ap.cBorrowerIsStudentInd , 'Y' ) = 'N'
and ap.cStudentSSN COLLATE SQL_Latin1_General_CP437_BIN not in (select cSSN from DBAWORK..LAMSAPPSUM)
and ISNUMERIC(ap.cStudentSSN) = 1
GROUP BY
ap.cStudentSSN
November 15, 2004 at 7:30 pm
My experience (similar to it) directs to tempdb default collation out of sync with the restored SQL 7.0 DB. It explains "run just fine when pointing at a DB created SQL 2000 DB and referring to the restored database."
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply