Article ID: 193757
Article Last Modified on 2/24/2004
APPLIES TO
- Microsoft SQL Server 6.5 Service Pack 4
- Microsoft SQL Server 6.5 Service Pack 3
This article was previously published under Q193757
BUG #: 17401 (SQLBUG_65)
SYMPTOMS
Using the RIGHT function within an ANSI join clause fails with a syntax error on servers with SQL Server 6.5 Service Pack 3 or Service Pack 4 installed.
WORKAROUND
To work around this problem, do either of the following:
- Recode the query to use traditional (that is, non-ANSI) join syntax.
-or-
- Consider whether the SUBSTRING function could be used. This would only be possible if the data value within the column was guaranteed to be of fixed length. For example, the au_id column in the authors table in the pubs sample database always contains a value of 11 bytes. So in this case, SUBSTRING (au_id,8,4) would always return the same values as RIGHT(au_id, 4).
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 3 and Service Pack 4. This problem has been corrected in the latest U.S. Service Pack 5a for SQL Server version 6.5. For more information, contact your primary support provider.
MORE INFORMATION
The following is an example of a query that demonstrates this problem:
SELECT a.au_lname, t.title_id
FROM authors a JOIN titleauthor t
ON right(t.au_id,11) = right(a.au_id,11)
The following error is generated:
By recoding the query to use traditional join syntax, the problem does not occur:
SELECT a.au_lname, t.title_id
FROM authors a, titleauthor t
where right(t.au_id,11) = right(a.au_id,11)
Additional query words: prodsqlsp sp3 sp4 sp5
Keywords: kbbug kbfix kbsqlserv650sp5fix KB193757
