Sql Compare API and stored procedures

ales.potocnik

It seems I've found yet another bug in the compare API. It doesn't seem to pick up stored procedures, longer than 4000 characters up correctly.

Procedures, who's body is longer than 4000 get split up in sys tables into 4000 character blocks. When collecting the procedure body this has to be taken into account.

Can anyone confirm this is a problem/bug?

Brian Donahue

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.

ales.potocnik

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

ales.potocnik

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.