Brackets vs. Quotes in Snapshot Comparison

cardsharp

I've used SQL Compare 6 to generate a snapshot. When I compare the snapshot with another copy of the database it wants to rename every single stored proc in my DB using quotes rather than brackets. For example, my database contains a proc create that looks like:

CREATE PROC [dbo].[BillingReport] (@beginDateTime DATETIME)

When I create a snapshot and do the compare, SQL Compare wants to rename it like this:

CREATE PROC "dbo"."BillingReport" (@beginDateTime DATETIME)

Why is it changing brackets into quotes in the snapshot and how do I set it to ignore this difference? It takes a considerable amount of time to go through a few hundred stored proc declarations to verify that the only change is just brackets vs. quotes. I've got to be missing something...

Michelle T

I've never seen this behaviour before...

Are you sure that the original copy of the database (that you took the snapshot of) didn't have quotes around the object name instead of brackets? That's the only way I've managed to get quotes into a snapshot.

If it's actually converting the brackets into quotes, I would love a copy of your database snapshot and a backup of the database you've taken it from, if at all possible (you can send them to michelle.taylor@red-gate.com). Hopefully with those we can reproduce and fix the problem. If you created the snapshot from a project (rather than just the export data source window with the Other Data Source tab), it would also be good to know what options were set in that project.

Unfortunately there isn't currently a way to ignore the kind of bracketing in the procedure name - I've added your request to the bug open in our bug tracking system about the problem. Because Stored Procedures are a 'textual' object (i.e. SQL Server remembers the actual text of the CREATE statement) doing non-textual comparisons (e.g. ignoring the difference between quotes and brackets) is not entirely trivial, alas.

cardsharp

Well, my database is 150 GB. I guess I could create an empty copy of it...

As for checking for quotes in the DB, I've checked the original DB and it does have brackets. Not sure why the snapshot thinks it's supposed to be quotes.

In MSSMS I click "Script Stored Procedure" and script it to a file, I get a create statement that looks like:

USE [warehouse]
GO
/****** Object:  StoredProcedure [dbo].[BillingReport]    Script Date: 08/15/2007 09:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROC [dbo].[BillingReport] (@OrgObjectId INT, @beginDateTime DATETIME, @endDateTime DATETIME)
...

But when I compare it with SQL Compare, It shows quotes:

CREATE PROC "dbo"."BillingReport" (@OrgObjectId INT, @beginDateTime DATETIME, @endDateTime DATETIME)
...

Michelle T

When you say 'when I compare it with SQL Compare', do you mean 'when I compare that live database to anything with SQL Compare'?

If SQL Compare is reporting the live database as containing quotes, then it looks like your sys.syscomments table contains the stored procedure with quotes. Could you run the following query:

SELECT text FROM sys.syscomments WHERE id = OBJECT_ID('dbo.BillingReport')

and tell me if it has quote marks or not?

If that query doesn't show quote marks, an empty copy of your database would be great - SQL Compare only deals with the schema.

If that query does show quote marks, I'm afraid the problem is that your database has quote marks around the name in its internal representation of the object, which is what SQL Compare retrieves, and therefore SQL Compare is always going to report quote marks from that database, and they're going to keep showing up as a difference until we manage to get the fix in to ignore the kind of quotes around textual object names.