Help with Custom SQL Packager

jules140

Hi,

I'm fairly new to SQL Packager and my client's requirements are as follows:

1/ We will initially ship our application with a new copy of the main database that is constantly changing.
I’m writing a custom DLL that will copy the Main database and only one table.
I need to know how to copy the database structure with only one table via the api.

2/ Later stages require us to only ship the one table (for users who already have the application).
Please supply instructions of how to overwrite the existing table with the new version via the api.

We do not have the credentials of the SQL server user - can this be over-ridden using the Microsoft MSI Installer (as believed)?

Regards,

Jules

Brian Donahue

Hi,

Thanks for your post. It is possible to wrap the SQL Toolkit API calls up into a Class Library project that inherits System.Configuration.Install.Installer. This would allow you to tightly integrate Toolkit with an MSI, in a way that allows you to display dialogs to the user that will let them specify server, database, and user names in the installer and pass the information to your installer class DLL.

This would work well for comparing an existing database or comparing a database on the customer's machine to a SQL Compare snapshot and synchronizing it, but it's not so practical for deploying Packager data.

The reason for this is because SQL Packager outputs an executable or project to create an executable only. This executable will also assume that the table does not already exist -- it will not use the typical 'if exist drop...' that SQL Server management tools use, so a SQL command would need to be run outside of the package anyway to drop the table.

If you only want to deploy a schema containing a single table, it may honestly be easier to script the table in SSMS and deploy the script in your Installer assembly as an embedded resource and then run it using ADO .NET.

The SQL Compare API is much more useful if you want to compare and synchronize a customer's schema to a reference snapshot file as part of a product installation.

I hope this gives you some ideas...

jules140

Thanks Brian,

Your ideas are certainly useful. Thanks for your help.

More reading, testing etc!

Regards

Jules

jules140

Hi Brian,

One last thing - is it possible to customise the C# project so it always does a table drop on the install?

Regards

Jules

Brian Donahue

Hi Jules,

Only by outputting the package as a C# project and hand-editing the embedded resource (.resx) files in Visual Studio. You can add a new batch before Create Table to do if exists drop table, but you have to remember to add 1 to the batch count section of the resource. I don't personally recommend hand-editing resources as they are serialized ExecutionBlock classes, but some customers have said they have done it successfully so it may be worth doing in a pinch.

jules140

Hi Brian,

Just one more thing and then you can finally close this!

In the SQL Packager api, I need to drop all constraints on the table I am copying.

What's the command / options (on the worked example) to do this.

Cheers

Jules

Brian Donahue

Hi,

You can add the DropConstraintsAndIndexes option to the options through the EngineDataComparisonOptions object like this:

using (ComparisonSession session=new ComparisonSession())
			{
                session.Options = new EngineDataCompareOptions(
                MappingOptions.Default,
                ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                SqlOptions.Default | SqlOptions.DropConstraintsAndIndexes); 

				session.CompareDatabases(db1, db2, mappings);

				m_TableDifferences = session.TableDifferences;
    
				// now get the ExecutionBlock containing the SQL
				// we want to run this on WidgetLive so we pass on true as the second parameter
				SqlProvider provider=new SqlProvider();            
				provider.Options = session.Options;
				ExecutionBlock block;
				try
				{
					block = provider.GetMigrationSQL(session, new SelectionDelegate(this.SyncRecord), true);
}
finally
				{
					block = provider.Block;
					if (block != null)
					{
						block.Dispose();    // dispose of the objects to delete temporary files
					}
				}
			}

jules140

Thanks Brian,

Is there a VB version available of this code?

Also, do you have the method signature for "New SelectionDelegate(Me.SyncRecord)"?

Regards

Jules

Brian Donahue

Hi Jules,

Maybe this isn't the best example -- you don't need to specify a selectiondelegate to GetMigrationSql as there is an overload that doesn't require it.

The point is, to make sure that you set the session options by creating an EngineDataCompareOptions object and including DropConstraintsAndIndexes by Or-ing them to the default SqlOptions:
Dim opts as SqlOptions=SqlOptions.Default Or SqlOptions.DropConstraintsAndIndexes