Red Gate forums :: View topic - Help with Custom SQL Packager
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Help with Custom SQL Packager

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
jules140



Joined: 19 Jun 2008
Posts: 7

PostPosted: Thu Jun 19, 2008 12:55 pm    Post subject: Help with Custom SQL Packager Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Tue Jun 24, 2008 5:56 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
jules140



Joined: 19 Jun 2008
Posts: 7

PostPosted: Wed Jun 25, 2008 8:35 am    Post subject: Reply with quote

Thanks Brian,

Your ideas are certainly useful. Thanks for your help.

More reading, testing etc!

Regards

Jules
Back to top
View user's profile Send private message
jules140



Joined: 19 Jun 2008
Posts: 7

PostPosted: Wed Jun 25, 2008 8:49 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Jun 25, 2008 9:39 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jules140



Joined: 19 Jun 2008
Posts: 7

PostPosted: Fri Jun 27, 2008 8:11 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Fri Jun 27, 2008 6:14 pm    Post subject: Reply with quote

Hi,

You can add the DropConstraintsAndIndexes option to the options through the EngineDataComparisonOptions object like this:
Code:
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
               }
            }
         }
Back to top
View user's profile Send private message
jules140



Joined: 19 Jun 2008
Posts: 7

PostPosted: Mon Jun 30, 2008 10:28 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Jun 30, 2008 3:12 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group