Problem with brackets

Rawden

I'm sure this is a simple one...

I have a query on a 2005 database that when I sync with a 2000 database gives me the following error:

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.qryNotes
AS
SELECT     TOP (100) PERCENT dbo.tblNotes.NotesID, dbo.tblNotes.LinkedMidID, dbo.tblNotes.NoteText, dbo.tblNotes.NoteDate, dbo.tblNotes.LinkedUserID, 
                      dbo.tblLogins.FirstName, dbo.tblLogins.SecondName, LEFT(dbo.tblLogins.FirstName, 1) + LEFT(dbo.tblLogins.SecondName, 1) AS Initials, 
                      dbo.tblLogins.FirstName + ' ' + dbo.tblLogins.SecondName AS Name, dbo.tblNotes.NoteOrigin
FROM         dbo.tblNotes LEFT OUTER JOIN
                      dbo.tblLogins ON dbo.tblNotes.LinkedUserID = dbo.tblLogins.LoginID
ORDER BY dbo.tblNotes.NoteDate

I know this is because the query shoud look like SELECT TOP 100 PERCENT in 2000 (without the brackets) so is there an option I can turn on so it doesn't generate the error?

By the way, the reason why the query is returning 100% of the records, making it seemingly an unnecessary keyword is so that I can have the query ordered. Management Studio puts in the TOP keyword automatically as soon as I specify and ORDER BY.

Michelle T

Unfortunately, we don't have an option like that - we leave alone as much as possible the text of 'textual objects' (stored procedures, views, functions etc), so if there's syntax invalid for 2000 in a stored procedure that you synchronize to a 2000 database, it will remain invalid for 2000, and then it may fail to create the stored procedure.

If you want to run a synchronization script without having it stopped halfway by error messages, select the 'Open in Query Analyzer' or 'Open in Management Studio' option (you can change which one you get in Application Options) at the end of the synchronization wizard instead of trying to run the synchronization directly from SQL Compare. Then you should be able to run the script without it stopping for every error. (You might also need to select the 'do not use transactions' project option.)

I'm slightly unclear on what feature you are looking for - do you want an option to ignore errors while running the synchronization script (or possibly just specific errors) or an option to translate into 2000 syntax?

(Translating to 2000 syntax would be quite a major addition (as there are a lot of small incompatibilities in such objects between 2005 and 2000, and it would need extensive testing to make sure we didn't change the behaviour of the objects while changing the syntax) and we have no current plans to include it.)

Rawden

Thanks Michelle. I think your last paragraph answers my question. I didn't know there were so many incompatibilities that the software didn't handle. I thought it synchronised them despite their SQL version.

I create a snapshot of my Development database and send it out in my install program, along with a custom built tool which uses the Redgate API to update the client's database. So unfortunately, the Open in Query Analyzer option wouldn't be a possibility.

I’ll have a think about a get-round then… perhaps putting the ORDER BY in my app's call.

Michelle T

We attempt to synchronize database despite their SQL version, and it generally works with the non-textual objects, but we don't actually go into your textual objects and attempt to make changes to make them compatible.

You can get the same kind of effect by getting the tool to write out the synchronization script to a string or a file instead of running it, and then using some other method to run the script, but obviously what that would do is miss out the stored procedure entirely rather than correct it.