Comments
10 comments
-
Official commentHi James,
Thanks for reaching out to Redgate Support regarding your issue with SQL Compare. What version of the product are you using in this case? For testing purposes, would you be able to share the exact T-SQL you are using? I should be able to replicate the behavior once I know the version and syntax.
-
RedGate.SQLCompare.UI.exe
C:\Program Files (x86)\Red Gate\SQL Compare 15
15.4.10.27977+6a233340b701adde9321bff7be3c47314fc3b733
4.0.30319.0
False
Thanks Christian- apologies, I should know by now that it would be useful to include these each time on a ticket! - Also that would prompt me to do what i normally do when i encounter a possible issue and update the software to see if it's been resolved already.
I will probably update later today
i have a simple proc on both databases
i make a trivial change to force a difference
I go to deploy and the script include my intended change but as a consequence changes the first line
so following the deployment it shows as no difference between the two databases
but an unintended change has occurred - which means i have to correct it manually afterwards (which is tedious when it surprises you and amends multiple items) and it shows up as every object different when using a different compare tool (Eg devops)
script for the simple proc to be precisely like mine - but it happens on any proc, object you write
CREATE OR ALTER
PROC [dbo].[proc]
AS
;BEGIN
;SET NOCOUNT ON
;BEGIN TRY
;DECLARE @this_proc_name sysname = (CONCAT((QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID))), (CHAR(46)), (QUOTENAME(OBJECT_NAME(@@PROCID)))))
;DECLARE @RC INT = (0);RAISERROR(@this_proc_name, 10, 0)WITH NOWAIT
;END_OF_QUERY:
;RETURN(@RC)
END TRY
BEGIN CATCH;DECLARE @ErrorMessage NVARCHAR(MAX) = (ERROR_MESSAGE())
;RAISERROR(@ErrorMessage, 16, -1)
;RETURN(ERROR_NUMBER())END CATCH
END --of sp
GO
-
Hi James,
Thanks for your patience while we continued testing this and for providing your T-SQL Script. I was able to test this locally in my environment using SQL Compare version 15.4.11 and after deploying the stored proc to an empty database I made the same commenting change on the RAISEERROR section of your example and I am not seeing the behavior you are reporting in this case. I am still seeing:
CREATE
PROC [dbo].[proc]On both ends of the comparison and after the deployment. Could I trouble you to upgrade SQL Compare and repeat this test and let us know if the issue is now resolved?
-
the software is saying there aren't any updates to apply as i'm on latest version.
RedGate.SQLCompare.UI.exe
C:\Program Files (x86)\Red Gate\SQL Compare 15
15.4.13.28096+ebd604dab73334a415f1205f61fa1334ccdf8910
4.0.30319.0
False
my current depoloyment showing below where in the screen behind it shows the comparison with PROC keyword on second line but the script window then shows the PROC on first line
if i scroll down the script all other procedures have PROC keyword as first word of second line - but this first entry shows it differently for some reason
-
Hi Christian,
In your own test, did you take my proc script as is? Because i think the issue is when the script has CREATE OR ALTER PROC in it, whereas if the script is a CREATE PROC or an ALTER PROC it works
Regards,
James
-
Though i just had it happen on a normal ALTER so I have noticed this
One whitespace after my ALTER keyword (renders as CREATE in redgate comparison) things are ok in the generated script
No whitespace after the opening keyword things are ok in the generated script
But two whitespaces after the opening keyword then the generated script outputs on one line
Three whitespaces after the opening keyword then the generated script outputs on one line
Tab after the opening keyword and things are ok
Even though i've now determined the workaround it will be a bit tedious to police myself and others in code-review to prevent it corrupting the environments.
the reason I make this change is that i have litany of code that has ended up similar to this;
where there are varying big gaps between the keywords - i don't know exactly where these have come from (SSMS modifications or redgate compare deployments) but in order to stop them from creeping in - i try and enforce a consistent syntax - which the redgate whitespace bug is therefore undermining.
Hope above helps resolve.
James
-
HI James,
Apologies on the delay getting back to this. Let me review through your new entries and try testing with the amount of whitespace I am passing in this time around. Should have more for you by this afternoon.
Cheers,
Christian Perez
Product Support Engineer
-
Hi James,
Given these behavioral changes you are seeing seem to be caused by the amount of whitespace in your script, could I suggest that you try going into your SQL Compare Options and check off the comparison option for “Ignore whitespace” and then try this test again and let us know if any change in behavior occurs?
Cheers,
Christian Perez
Product Support Engineer
-
I should have mentioned i'd already tried that option but turning that option off means you get countless differences that i do want to ignore and renders that option pointless.
regards,
James
-
Looking at views at the moment and it appears that when you right-click and script out an existing view (CREATE, ALTER, DROP AND CREATE, CREATE OR ALTER) where the object has been created with the object type (VIEW) on a new line then the scripting inserts extra white space that's not needed and therefore adjusts the exact script of what it was before:
The whitespace inserted is inconsistent between the script methods:
Any reason why they can't all just have no or one whitespace in each - or is this an SSMS issue rather than redgate scripting?
Add comment
Please sign in to leave a comment.
when i create or alter an object and have the object name on the following line to the create or alter part on both sides of the comparison identically once i deploy the change (which is further within the procedure itself) when the databases re-compare i now have a difference which i didn't have before where the object name is in the same line as the ALTER part