SQL 2005-> 2000 syntax error when sync views
dmoyes
Posts: 2
Hello,
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
AS
SELECT TOP (100) PERCENT
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
AS
SELECT TOP (100) PERCENT
This discussion has been closed.
Comments
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.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd