Generated scripts are missing some CR/LF in some cases

lenny

SQL Compare Version 2.03

It appears that when a stored procedure just happens to have a carriage return/line feed pair split up and stored in syscomments-- with the last character of the first record being the CR, and the first character of the next record the LF—that SQL Compare will combine the text when it generates a script and lose the CR. This can cause a problem since the SQL up to the first CR in the second syscomments record is now on the same line with the last line of the first syscomments record.
For example,

Syscomments rec 1 --> ….’Some comments here<CR>
Syscomments rec 2 --> <LF>Set @i=0&lt;CR><LF>Set @j=1….

SQL Compare seems to generate this:

….’Some comments here Set @i=0
Set @j=1

When obviously it should be:

….’Some comments here 
Set @i=0
Set @j=1

Is this a known issue (for 2.03)? Will an upgrade fix the problem?
Any assistance/info will be appreciated.

-Lenny

Brian Donahue

Hello Lenny,

Having a brief look at the code, I'd say it will work. We select all of the relevant rows from syscomments and concatenate them together in version 3. The best recommendation I have is to give the version 3 demo a chance by installing it on a second machine.

If you still have this issue in the new version, we'll be more than happy to fix this if you'll be kind enough to let us know about it.

lenny

Thank you. I'll try the demo.

mzcopea

We are having the same problem with sql stored procs, particularly large procs. What we have discovered is that in the Sql Systable, there are missing ASCII 10's after the ASCII 13. This occurs randomly, or seems to. There doesn't appear to be a rhyme or reason. The missing ASCII 10's mess up the line feed and when Sqlcompare creates a script, code lines get placed directly behind comment lines in the destination stored proc. It works fine on the source stored proc.

We are already using Sql compare version 3.

We have talked to Microsoft and they claim that their stored procs are in Unicode and that the ASCII 10's are not needed for a carriage return/line feed. We can recreate the problem by copying the code into Wordpad (works fine in Notepad). They state that since the proc works fine on the source destination, it is not their problem. Since we are copying to a place that is non-Unicode, they cannot help us.

So - is Sqlcompare supposed to be able to deal with this? We use the command-line version to create our script.

If I need to explain further, please tell me what else you need to know.

Brian Donahue

Hello,

That certainly is interesting! So the problem is that you may be using an external editor to modify the stored procedures?

For our part, we are going to look into this in the future. I beleive that in SQL Compare, even proper CR/LF (CHAR(13)+CHAR(10)) sequences in comments cause problems still.

mzcopea

I thought I submitted a reply, but got knocked out of the forum, so I'll try again.

We have found that editing a stored proc in the editor in Sql Enterprise Manager (simply open the properties of the proc and edit there) will introduce the problem. Altering a stored proc with Query Analyzer can also introduce the problem. If you leave the stored proc there, it works fine, but if you copy the text to another place such as WordPad, the line feed will be missing wherever there was a missing ASCII10 in the proc. This is what is happening in our Sqlcompare scripts.

If you use Visual Studio to modify your stored procs, you won't have the problem. We are afraid to rely on that because we are a large IT shop with many developers and DB admins.

We have asked Microsoft to escalate our problem because we want to know if this is expected behavior.

It would be even better if Sqlcompare could deal with this. :-)
Microsoft Word actually fixes the problem - it will insert an ASCII10 wherever one is missing, so I guess it's possible.

Thanks.

mzcopea

Another update:

Microsoft told us today that another customer has reported the same problem and they have entered the problem into their bug tracking system. They do not know yet whether a fix will be provided or not.

We have told our developers and db admins to use Visual Studio from now on.

Brian Donahue

Excellent! Thanks for letting us know.