SQL script created does not execute

CtrlAltDel

I am having big problems getting the SQL script that SQL Compare 5 generates to execute from SQL Server Management Studio.

I have a baseline SQL2005 DB that I have created a snapshot of, and an empty SQL2005 DB on another server. If I do a compare and synchronise from within SQL Compare's GUI, then everything works just fine. If however, I save the SQL script that SQL Compare generates, and execute that from SQL Server Management Studio, the script dies most of the way through with various SQL errors (particularly 'Incorrect syntax near 'GO''). If I use the command line version to generate the SQL script, that dies with the same errors. If I use SQLCMD to run the SQL script, that dies with the same errors.

Any ideas what's going on? If it helps, I can email the complete script as generated by SQL Compare.

Brian Donahue

Hi,

It would probably be a good idea to attach the SQL script to an email and send it to support@red-gate.com. Not all programs that run SQL can handle the GO batch keyword, but Management Studio should! I'd like to have a look at the script.

Thanks!

CtrlAltDel

After a lot of trial and error, I've narrowed it down to a stored procedure which refuses to compile when it is followed by a GO statement. Obviously, in a SQL database creation script, a GO statement is rather important, but the problem doesn't appear to be in SQL Compare. I can still send the script if you want to win some brownie points for top customer service

CtrlAltDel

Ha, nailed the sucker. There were some CRs in there missing following LFs, and that particular stored procedure was complex enough that it got very upset if all the lines didn't have CR/LFs. So you'll be glad to know that the problem was definitely not a bug in Red Gate's code

Brian Donahue

Hi there,

Thanks for the information. Maybe it would be a good idea if we respected non-MS-DOS text. I can't imagine it coming up too often, but it looks like it could potentially cause some problems.