Sql Compare API and stored procedures

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  June 10, 2008 11:32AM

  Hi,
  
  This used to be a bug in SQL Compare API version 3.16, I remember it well. However any of the Toolkit assemblies newer than that will assemble all of the entries from syscomments into a whole stored procedure, UDF, or view.

  0 · Share on Twitter
• Options

  ales.potocnik Posts: 13

  June 10, 2008 12:05PM

  Brian, thanks for reply. Looks like I'll have to check if my version is up to date.

  0 · Share on Twitter
• Options

  ales.potocnik Posts: 13

  June 11, 2008 3:40AM

  I've checked and the version of the API is 6.xx.
  
  Perhaps this is a problem only in a specific scenario. To explain I'm not doind a straight forward CompareWith and then outputing the scripts as they are.
  
  Because the API has a confirmed problem with dependencies I'm rendering the scripts for each difference by enumerating collection:
  

  RGE.Differences differences = databaseSource.CompareWith(databaseTarget, RGE.Options.Default);
  foreach (RGE.Difference diff in differences)
  {
  	diff.Selected = (diff.DatabaseObjectType != RGE.ObjectType.User
  		&& diff.DatabaseObjectType != RGE.ObjectType.Schema
  		&& diff.DatabaseObjectType != RGE.ObjectType.XmlSchemaCollection
  		&& diff.DatabaseObjectType != RGE.ObjectType.Role
  		&& diff.DatabaseObjectType != RGE.ObjectType.Assembly
  		&& diff.DatabaseObjectType != RGE.ObjectType.FullTextCatalog);
  }
  

  
  After that enumerating:
  

  foreach (RGE.Difference diff in differences)
  {
  	worker = new RGE.Work();
  	worker.BuildFromDifferences(differences, diff, RGE.Options.Default, true);
  
  	for (long index = 0; index < worker.ExecutionBlock.BatchCount; index++)
  	{
  		Batch batch = worker.ExecutionBlock.GetBatch(index);
  		// ... store batch
  	}
  }
  

  
  Could it be that this is only a problem when compiling scripts this way?
  
  By the way, I've solved the rpoblem with dependencies. In short, I'm storing the filtered scripts (without fail safe script blocks) into a database just for this purpose, extracting each script information using regular expressions and then finding dependencies on a full-text indexed column with change script. At the end it produces scripts in valid script order even for a clean database.
  
  Because I'm storing the scripts I've double checked that the problem with stored procedure bodies appears after the Worker generates the change script. Perhaps there is a problem with how stored procedure bodies are stored in entities and Worker is not getting the whole thing ...
  
  It's not a big problme for us for now as there's only 1 stored procedure that's over 4000 characters long but it's a flaw nevertheless.

  0 · Share on Twitter