CRLF embedded in Field Varchar(255)

rockywurden

Problem
The saved SQL Script has broken the following UPDATE into 3 LINES (THE ***** are not part of the UPDATE. They just show where the line breaks are.) This is because there are embedded CRLF in data field.
Is there a way to fix this problem besides going into the Data and stripping CRLF?

UPDATE [dbo].[EccLinksSSSPData] SET [calcTLoad]=0, [calcTAnchor]=0, [calcTShore]=0, [calcTCruise]=0, [calcTFunctional]=0, [calcTEmer]=0, [calcNLoad]=0, [calcNAnchor]=0, [calcNShore]=0, [calcNCruise]=0, [calcNFunctional]=0, [calcNEmer]=0, [calcALoad]=0, [calcAAnchor]=0, [calcAShore]=0, [calcACruise]=0, [calcAFunctional]=0, [calcAEmer]=0, [Notes]=N'NOTE - WRITTEN IN PENCIL FOR THIS POSITON -
*********
02-107-2-1L-E8
*********
ISOLATION REC', [Validated]='20051107 00:00:00.000', [ValUser]=N'KellyC' WHERE [RIN]=N'(03-107-2)-1L-E8'


Thanks
Rocky

richardjm

Do you want SQL Data Compare to remove the line breaks? If that's the case it's not something we can do - although you could write a DDL trigger on the target database that performs the data manipulation or perhaps a view as source and the table as a target?

HTH

rockywurden

Note:
IF you use the wizard the data will be moved and the two databases will end up the same. If you save to SQL and run the script you get a error because of the embedded CRLF.
So how does the wizard get around the problem
Thanks

richardjm

The SQL should work fine so I'm not sure why you're seeing a problem. Are you using SQL Query Analyser or Management Studio to run the SQL or something else?

If possible can you send me the saved SQL script and I'll have a look at it to see if I can replicate the problem here. My e-mail address is richard.mitchell@red-gate.com.