Issues with deploying SQL scripts

ag97690

I am seeing issues with the SQL Scripts used in deployment after comparing databases. Commands in SQL are not getting spaced correctly causing errors when I try to run them in SSMS. The SQL setup that I am using is Compare 12.0.20.2791, SSMS 2016, SQL 2012 and SQL 2008R2 servers. I am comparing a database on SQL 2012 against SQL 2008R2 and both databases are set to compatibility level 100. The script is trying to create stored procedures to the 2008 R2 database. Some of the spacing happens with where statements, Integer statements , Set statements and more. Some examples are:
1. "intASSET NOCOUNT ONDELETE from" which should be "int AS SET NOCOUNT ON DELETE"
2. "OUTPUTASSET NOCOUNT ONINSERT INTO PROJECT" which should be "OUTPUT AS SET NOCOUNT ON INSERT INTO PROJECT"
3. "[update_date] = @update_dateWHERE" which should be "[update_date] = @update_date WHERE"

So from what I can see either carriage returns or spaces are being lost when creating the deployment scripts for a large amount of stored procedures.

sam.blackburn

Hi,

This sounds like a text encoding issue, but it's hard to locate the problem since there are subtly different ways of exporting the script. Would you mind letting me know which of these methods work?

- Does the script look OK in the SQL Compare deployment wizard?
- Can you run the script using "Deploy Now" in SQL Compare?
- Does it look OK if you click "Save Script"?
- Does it look OK if you press the "Copy" button and paste into Notepad or SSMS?
- Does it look OK if you select all the text and copy it with keyboard shortcuts? (Ctrl+A, Ctrl+C)
- What locale (language) is your local machine using?

ag97690

Here are my results from the questions you asked.

- Does the script look OK in the SQL Compare deployment wizard?

No

- Can you run the script using "Deploy Now" in SQL Compare?

It is listing it out wrong, but it looks like it correctly added itself to the other database when finished.

- Does it look OK if you click "Save Script"?

Same spacing issue.

- Does it look OK if you press the "Copy" button and paste into Notepad or SSMS?

No

- Does it look OK if you select all the text and copy it with keyboard shortcuts? (Ctrl+A, Ctrl+C)

posting it to notepad actually removed most of the spacing structure and the words still run together.

- What locale (language) is your local machine using?

English (United State)


Does it matter what encoding I am using? under application options for Encoding I am currently set to use Unicode (UTF-16)

sam.blackburn

Thanks for your information. It looks like SQL Compare is having trouble with unusual whitespace in your text objects - if I run

CREATE TABLE PROJECT (id INT)
DECLARE @NewLineChar AS CHAR(1) = CHAR(13)
EXEC ('CREATE PROC Procodile @DeptValue int'+@NewLineChar+'AS'+@NewLineChar+'SET'+@NewLineChar+'NOCOUNT ON'+@NewLineChar+'DELETE FROM PROJECT')

then the deployment wizard shows the behaviour you are describing. Additionally, the syntax highlighting is colouring some of the text white, so you have to select the text to make it visible in the deployment wizard.

The reproduction I found uses the Carriage Return character without a Line Feed - can you check whether your function definition contains this kind of line ending?

sam.blackburn

The fix for this should be available in 12.0.23 - does it solve your problem?

Cheers,