BUG: Errors When Creating View Using WITH CHECK OPTION in SEM

ID: Q198105

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 50705 (SQLBUG_70)

SYMPTOMS

When you create or edit a view with the WITH CHECK OPTION from SQL Server Enterprise Manager, you may receive any of the following error situations and messages:

When you try to save or run the view you created, you receive the following error message:
```
      unable to parse query text
```
If you respond to the dialog box which displayed the previous error, you may receive the following error message:
```
      [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1 :
      Incorrect syntax near 'WITH' .
```

If you choose Verify SQL, you receive the following error message:

      Query Designer encountered a MS Design error :
      ODBC error : [Microsoft][ODBC SQL Server Driver][SQL Server]
      Line 1 :  Incorrect syntax near 'WITH' .
      [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
      could not be prepared .

If you attempt to design a view which was originally created using the WITH CHECK OPTION, the WITH CHECK OPTION clause is not displayed in Enterprise Manager, but is indeed enabled for the view. If you check the view properties (right-click the view and click Properties), you can verify that the WITH CHECK OPTION is enabled.

WORKAROUND

To create or save the view, do either of the following:

Use the Query Analyzer to create a view with the CREATE VIEW statement, if you want to include the WITH CHECK OPTION clause.
-or-
When you save the view in SQL Enterprise Manager, the view is actually created and saved, even though you receive the error message.

To run the view, do either of the following:

Go to the Query Analyzer and run the view from there. For example, if you need to have all the columns in the View1 view returned, run the following query:
```
      Select * from View1
```
-or-
After you save the view, close the Design View window in SQL Enterprise. You can run the view by right-clicking it and then clicking the Open View option.

STATUS

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

Additional query words: prodsql Enterprise Manager SEM EM MMC

Keywords          : SSrvEntMan kbbug7.00 
Version           : WINNT:7.0
Platform          : winnt
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: January 2, 1999