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


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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 "More Information" 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 "DaVinci", 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.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbbug kbpending KB288527
©2003 Microsoft Corporation. All rights reserved.