What are the challenges you face when working across database platforms? Take the survey

SQL 2005-> 2000 syntax error when sync views

dmoyesdmoyes Posts: 2
edited June 7, 2006 6:29AM in SQL Compare Previous Versions

When using SQL compare 5.1 I get a syntax error when sync'ing views from 2005 to 2000. The problem is with the SELECT TOP (100) PERCENT, where the designer in sql 2005 automatically places the "(" ")" around the 100. This is giving an error in the comparision. Yes, I could create my views using script in 2005 to avoid this, however, I have a preference for the query designer.

Is there anyway around this please? (is there some settings or other methods)

Thank you.

The following error message was returned from the SQL Server:

[170] Line 3: Incorrect syntax near '('.

The following SQL command caused the error:

ALTER VIEW dbo.ProcessTaskDetailsViewQCI


  • Options
    Hi there,

    As you suggest unfortunately some of the changes Microsoft made to the SQL 2005 syntax are not compatable with SQL Server 2000, and brackets around the TOP value as one of these changes.

    When we migrate a view to a target database SQL Compare uses exactly the same syntax that was used to create the view in the source database. This means that if you are downgrading databases from SQL2005 to SQL2000 then you may experience problems with invalid syntax if you have used SQL 2005 only syntax.

    Sorry, I know it must be frustrating, but currently the only way around this situation at the moment is to either:

    * Alter your view before you run SQL Compare on it or
    * Output the script to QA or Managment Studio and edit there before you run it.


    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
This discussion has been closed.