FIX: ANSI SQL JOIN May Fail With AV Joining VIEW With Table

• Microsoft SQL Server, version 6.5

SYMPTOMS

A SELECT statement may fail with an access violation (AV) if you use the new ANSI style SQL JOIN when joining a VIEW and a table. This problem will occur if either of the following are true:

• The VIEW contains expressions in its SELECT statement

  -or-

• The VIEW contains the old style of JOIN operators.

WORKAROUND

To work around this problem, avoid using the old style of join operators in VIEWs if you are joining these VIEWs with new style ANSI SQL JOINs.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 1. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

Microsoft SQL Server 6.5 now supports the ANSI SQL syntax for joined tables and views. This type of JOIN exists in the FROM clause rather than in the WHERE clause (as in previous versions of SQL Server). The ANSI SQL syntax provides the INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER JOIN operators.

Although the OUTER JOIN operators *= and =* in the WHERE clause from earlier versions of SQL Server are supported, you cannot use both OUTER JOIN operators and the ANSI SQL style joined tables in the same query. Microsoft makes no guarantee that these old-style operators will be supported in future versions of SQL Server.