Microsoft KB Archive/288527

= BUG: SQL Server Enterprise Manager Design View May Change the T-SQL Syntax of a View =

Article ID: 288527

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q288527



BUG #: 101230 (SQLBUG_70)

BUG #: 352015 (SHILOH)



SYMPTOMS
When creating a view from the SQL Server Enterprise Manager (SEM) Design View tool, the T-SQL syntax appears normal. However, if the view is saved, Design View is closed, and then Design View is reopened to examine the view, the syntax may change.

This problem occurs in both SQL Server 2000 and SQL Server 7.0, although the alterations to the T-SQL are slightly different between the two versions. This behavior is present in only a very few and unique set of circumstances. See the &quot;More Information&quot; section for further details.

When you right-click the view and then select Properties, the view's syntax appears normal; it can be run in Query Analyzer (QA) and will return the correct results every time. However, the altered syntax from Design View does not return the correct results.



CAUSE
This behavior has nothing to do with outer joins as such; rather it is due to autoaliasing when it should not be happening in these particular situations. The consequence is that an unbound column is created, which gets rebound to the wrong table.



WORKAROUND
After a view is created with Design View, avoid editing and then resaving the view with the Design View tool in SQL Enterprise Manager. Use Query Analyzer and T-SQL coding instead.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
Below is an example of the text taken from the two scenarios. The first is from when the view is being created; the other is after it has been saved, closed, and then reopened in Design View.

View After Being Created and While Still in Design View SELECT employee.emp_id, employee.fname, employee.minit, employee1.emp_id AS Expr1, employee1.fname AS Expr2, employee1.minit AS Expr3, employee2.emp_id AS Expr4, employee2.fname AS Expr5, employee2.minit AS Expr6

FROM employee employee2

INNER JOIN employee employee1 ON employee2.emp_id = employee1.minit

RIGHT OUTER JOIN employee ON employee1.emp_id = employee.minit View After Being Saved and Then Examined Again in Design View SELECT employee2.emp_id, employee2.fname, employee2.minit, employee1.emp_id AS Expr1, employee1.fname AS Expr2, employee1.minit AS Expr3, employee2.emp_id AS Expr4, employee2.fname AS Expr5, employee2.minit AS Expr6

FROM employee employee2

INNER JOIN employee employee1 ON employee2.emp_id = employee1.minit

RIGHT OUTER JOIN employee ON employee1.emp_id = employee2.minit To reproduce this behavior, follow these steps:
 * 1) From SEM, drill down into Pubs, Views.
 * 2) Select New View.
 * 3) Add the table Employee three times.
 * 4) Delete the suggested relationships between all tables.
 * 5) Select the fields (emp_id, fname, minit) from all three tables.
 * 6) Drag a join from employee.minit to employee_1.emp_id, and another from employee_1.minit to employee_2.emp_id.
 * 7) Right-click the relationship between Employee and Employee_1, and change the relationship to a RIGHT OUTER JOIN by selecting Select all rows from Employee.
 * 8) Take note of the T-SQL code that has been created, or copy it to Notepad for future comparison.
 * 9) Save the view as &quot;DaVinci&quot;, and then close Design View.
 * 10) Right-click the new view DaVinci, select Design View, and compare the T-SQL code now displayed to what was previously displayed (and saved in Notepad) as well as that found when you right-click the view and select Properties.

Keywords: kbbug kbpending KB288527

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.