Compare based on SQLScripts generated from EM

• 
  Constraints added inline (i.e. in a table) ought to be recognised as identical to constraints added via ALTER TABLE.
  
  There are a couple of things that might be causing them to look different:
  
  1) The constraints will come up as different in the Difference Viewer pane at the bottom of the comparison window, because that just does a textual comparison (for speed reasons) rather than a full semantic comparison. The comparison determining whether the objects are marked as different in the main comparison window is much more sophisticated than the one run by the difference viewer. So if you have any other differences on that table, that might be the cause.
  
  2) If either of the constraints are not given a name in the script, Compare will generate a random name for them in the same way that SQL Server would, but it is quite likely that it will generate different names for the two different constraints. Turning on the option 'Ignore Constraint and Index Names' or naming the constraints explicitly in the scripts will stop this showing up as a difference. (Unfortunately it is impossible to guess what name SQL Server will give an unnamed constraint before the script is run into a live database... it will be different if you run the script into the same live database twice.)
  
  If it is neither of these, you may have succesfully found a bug :oops: and we'd like some more details on what these constraints look like so we can reproduce it...

  Michelle T May 18, 2007 05:00

  - 0 +
• 
  I understand your point but generating lots of spurious differences sort of defeats the purpose of the tool. I tried your workaround to set the "ignore contraint and index names" but it didn't change the result. I also tried to set up a test by creating two simple scripts with the different syntaxes
  
  test1.sql
  =====
  CREATE TABLE [AccessControlList](
  [ACL_AccessControlListId] uniqueidentifier ROWGUIDCOL DEFAULT NewId() NOT NULL,
  [ACL_SecurityDescriptorId] uniqueidentifier NOT NULL,
  [ACL_Type] varchar(25) NOT NULL,
  CONSTRAINT [PK_AccessControlList] PRIMARY KEY NONCLUSTERED (ACL_AccessControlListId)
  WITH FILLFACTOR = 90
  ON INDEXES,
  CONSTRAINT [FK_AccessControlList_SecurityDescriptor] FOREIGN KEY ([ACL_SecurityDescriptorId])
  REFERENCES [SecurityDescriptor]([SDE_SecurityDescriptorId])
  )
  go
  
  test2.sql
  =====
  CREATE TABLE [dbo].[AccessControlEntry] (
  [ACE_AccessControlEntryId] uniqueidentifier ROWGUIDCOL NOT NULL ,
  [ACE_AccessControlListId] [uniqueidentifier] NOT NULL ,
  [ACE_Type] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [ACE_StandardAccessRights] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [ACE_GenericAccessRights] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [ACE_CustomAccessRights] [bigint] NOT NULL ,
  [ACE_TrusteeId] [uniqueidentifier] NOT NULL ,
  [ACE_SystemFunctionId] [uniqueidentifier] NULL ,
  [ACE_Inheritance] [tinyint] NULL
  ) ON [PRIMARY]
  GO
  
  ALTER TABLE [dbo].[AccessControlEntry] ADD
  CONSTRAINT [DF_AccessControlEntry_ACE_AccessControlEntryId] DEFAULT (newid()) FOR [ACE_AccessControlEntryId],
  CONSTRAINT [PK_AccessControlEntry] PRIMARY KEY NONCLUSTERED
  (
  [ACE_AccessControlEntryId]
  ) WITH FILLFACTOR = 90
  GO
  
  
  Unfortunately, I received a "Object reference not set to an instance of object" error when I tried to run the comparison.
  
  Any thoughts?

  alanghopkinson May 18, 2007 11:11

  - 0 +
• 
  The exception that you're getting off the scripts you've provided is caused by the foreign key FK_AccessControlList_SecurityDescriptor in the table AccessControlList, which points to a table SecurityDescriptor which isn't in either file. Unfortunately it's quite difficult to track down all the individual permutations of problems like this, so some of them still don't have particularly nice error messages when Compare encounters them.
  
  Having taken out the foreign key, I can see the problem you're reporting - the ALTER TABLE ADD CONSTRAINT DEFAULT statement doesn't appear to be read in from the script at all. Thanks for spotting this - I've reported it to the developers and it will probably be fixed for the final release (and for the point release if not).

  Michelle T May 21, 2007 06:24

  - 0 +
• 
  Regarding the original issue, the script with the 'ALTER TABLE...ADD CONSTRAINT.. ' style was generated by Enterprise Manager while the other came from source code control (individual object scripts).
  
  I would expect that scripting via EM to be a very common method and therefore ignoring this statement would likely affect a large number of your users. Of course, the prioritisation is up to you so this is just my two cents.

  alanghopkinson May 21, 2007 12:32

  - 0 +
• 
  When the DEFAULT constraint is created inline with the column definition (named or not), SQL Server considers it a column constraint. When it is created at the end of the table creation or via a subsequent ALTER TABLE command, SQL Server considers it a table constraint. It is marked accordingly as such (table or column constraint) in the system tables. This is most likely the source of the issue (as I've run into it in the past myself).
  
  There would have to be logic added that compared the functionality and disregarded if it was functionally identical whether it was considered a column constraint or a table constraint.

  DavidBuckingham May 24, 2007 11:57

  - 0 +
• 
  It doesn't look (from Books Online) like you can create a DEFAULT constraint as a table constraint within a CREATE TABLE statement - if you've managed to do this, could I have some example SQL, please?
  
  The release version of Compare 6 will be able to read in defaults created with ALTER TABLE, but may still have some issues around using scripts with this syntax as the target database in a synchronization.

  Michelle T May 25, 2007 05:54

  - 0 +
• 
  Our inline constraints came individual object creation files were stored in source code control system. I am not sure how these were generated originally (perhaps a modeling tool such as ERWin) while the table constraint is from within Enterprise Manager. EM does not provide an option to use inline DEFAULT constraints. So, the solution seems to be to generate the scripts using the same tool so it will use the same style. i.e. assuming EM, that would imply running these individual CREATE scripts against an intermediary DB and then generating from there.

  alanghopkinson May 25, 2007 15:02

  - 0 +

Add comment

Please sign in to leave a comment.