Bug Report for SQL Compare 3.2.0.5

neil.price

Originally found this problem in one of the previous versions, but it still exists after updating to the latest version.

I've been using this product quite happily for over a year, but after a couple of recent changes to our schema came across the following problem. I'm comparing between databases from a live & dev server pair. Historically at least one of the live databases was created as a restore from a backup of the dev database, so the differences between the two should be minimal. Additionally, the behaviour is the same regardless of which destination database I choose.

I have one particular stored procedure created relatively recently on the dev server that has in the past been synchronised to the live databases. On initialising/refreshing the comparison, the two copies are not matched up as the same stored procedure (despite being identical) - they are always listed as a dev item missing in live AND a live item that is extra compared to dev. This behaviour goes away if I disable the option 'Treat items as case sensitive' (despite the live version having been updated by SQL Compare at least 2 times already ....)

The stored procedure definition for the two databases are below & I can't see why they aren't recognised as being the same SP on both database. Presumably if they were, then it would also recognise the fact that they are identical .....

Anyone from Red Gate know why this would be?

Cheers,
Neil

Dev version:

CREATE PROCEDURE GetAdminRightsByWebUserID
@WebUserID int,
@IncludeAll bit -- Do we include only the items for display on the admin menu, or all items
AS

SELECT
ar.AdminRightID,
ag.AdminGroupID,
GroupName,
[Name],
Page,
ag.[Sequence] as GroupSeq,
ag_ar.[Sequence] as RightSeq
FROM AdminGroup ag
INNER JOIN AdminGroup_adminright ag_ar ON ag.AdminGroupID = ag_ar.AdminGroupID
INNER JOIN AdminRight ar ON ag_ar.AdminRightID = ar.AdminRightID
WHERE ar.AdminRightID in (SELECT AdminRightID FROM AdminUser_AdminRight au_ar WHERE au_ar.WebuserID = @WebuserID)
and (@IncludeAll = 1 or ag.[Sequence] != -1)
and (@IncludeAll = 1 or ag_ar.[Sequence] != -1)
ORDER BY ag.[Sequence], ag_ar.[Sequence]

SELECT
ag.AdminGroupID,
GroupName
FROM AdminGroup ag
WHERE EXISTS
(
SELECT null
FROM AdminGroup_AdminRight ag_ar
INNER JOIN AdminUser_AdminRight au_ar ON ag_ar.AdminRightID = au_ar.AdminRightID
WHERE ag.AdminGroupID = ag_ar.AdminGroupID
AND (@IncludeAll = 1 or ag_ar.[Sequence] != -1)
AND au_ar.WebuserID = @WebUserID
)
and (@IncludeAll = 1 or ag.[Sequence] != -1)
ORDER BY ag.[Sequence]
GO

Live version:

CREATE PROCEDURE GetAdminRightsByWebUserID
@WebUserID int,
@IncludeAll bit -- Do we include only the items for display on the admin menu, or all items
AS

SELECT
ar.AdminRightID,
ag.AdminGroupID,
GroupName,
[Name],
Page,
ag.[Sequence] as GroupSeq,
ag_ar.[Sequence] as RightSeq
FROM AdminGroup ag
INNER JOIN AdminGroup_adminright ag_ar ON ag.AdminGroupID = ag_ar.AdminGroupID
INNER JOIN AdminRight ar ON ag_ar.AdminRightID = ar.AdminRightID
WHERE ar.AdminRightID in (SELECT AdminRightID FROM AdminUser_AdminRight au_ar WHERE au_ar.WebuserID = @WebuserID)
and (@IncludeAll = 1 or ag.[Sequence] != -1)
and (@IncludeAll = 1 or ag_ar.[Sequence] != -1)
ORDER BY ag.[Sequence], ag_ar.[Sequence]

SELECT
ag.AdminGroupID,
GroupName
FROM AdminGroup ag
WHERE EXISTS
(
SELECT null
FROM AdminGroup_AdminRight ag_ar
INNER JOIN AdminUser_AdminRight au_ar ON ag_ar.AdminRightID = au_ar.AdminRightID
WHERE ag.AdminGroupID = ag_ar.AdminGroupID
AND (@IncludeAll = 1 or ag_ar.[Sequence] != -1)
AND au_ar.WebuserID = @WebUserID
)
and (@IncludeAll = 1 or ag.[Sequence] != -1)
ORDER BY ag.[Sequence]
GO

Brian Donahue

Hello,

Could this be an ownership issue? The creation DDL in your scripts does not mention an owner, so when you migrate the stored procedure, it will take on the ownership of the person who had run the synchronization script. This is not an issue if you always choose a user who has a DBO role, because then the stored procedure will always be owned by DBO.

Please check that when the synchronization is completed, that the stored procedure has the same owner in both databases.

neil.price

The SPs are owned by dbo in both databases both before and after synchronization. The synchronization is run from a saved SQL Compare project that uses sa logins to both databases. All other stored procedures in the database have the same ownership and do not have a problem.

Brian Donahue

I have another idea...

Is the name of the stored procedure the same in Enterprise Manager the same as the one that you'd listed in the DDL in the post?

The reason I ask is that renaming stored procedures in Enterprise Manager does not update the underlying DDL definition for the object. You'd mentioned that it works when you change SQL Compare's case-sensitivity option. I believe that the object was renamed in EM, changing the case of some of the characters, and the underlying DDL does not match.

This would explain why the names of the objects match perfectly in your listed DDL but SQL Compare is saying they're two totally different objects.

neil.price

Ahah! It turns out that you're right. In the source database the name has a lower case U, but the DDL contains an upper case U ....

Of course when it got synchronised, the DDL created an SP in the destination database using the upper case version.

I was sure I'd checked that when I first saw the problem ... but it's an easy one to miss even when it's right infront of your nose.

Feel free to rename this topic's subject to 'Idiot User' rather than 'Bug Report'

Many Thanks,
Neil