updating scripts from live db

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

updating scripts from live db

Postby toddwhitehead » Thu Aug 06, 2009 2:39 am

I'm using sdk 8 and trying to compare previously generated scripts with a live db and then update the scripts as needed.

I've been through a sample on the wiki (http://labs.red-gate.com/index.php/Synchronizing_Schema_-_Live_Database%2C_Scripts_folder_VB) but although the comments say it compares a db and scripts folder, the code actually seems to compare two script folders. Also the variable names dont match the description and is very confusing.

Below is my attempt to convert the sample but its complaining i'm using a folder compare api when i shouldnt.

Code: Select all
Public Sub SynchroniseScripts(ByVal SrcScriptsFolder As String)
        Using DestScripts As New Database(), MasterDB As New Database()
            ' Read the schema for the WidgetStaging database
            MasterDB.Register(New ConnectionProperties(\"xxxxx\\sqlexpress\", \"xxxxx\", \"sa\", \"xxxxxxxxx\"), Options.Default)

            ' Establish the schema from the scripts stored in the WidgetProduction scripts folder
            ' Passing in null for the database information parameter causes SQL Compare to read the
            ' XML file supplied in the folder.
            DestScripts.Register(SrcScriptsFolder, Nothing, Options.[Default])

            ' Compare the database against the scripts.
            ' Comparing in this order makes the WidgetProduction scripts folder the second database
            Dim DBDifferences As Differences = DestScripts.CompareWith(MasterDB, Options.[Default])

            ' Select all of the differences for synchronization
            For Each difference As Difference In DBDifferences
                difference.Selected = True

            ' Use the default folder structure for any new files
            Dim folderOptions As New WriteToFileOptions()

            ' Calculate the work to do using sensible default options
            ' The WidgetProductionScripts folder is to be updated, so the runOnTwo parameter is true
            Dim work As New Work()
            work.BuildFromDifferences(DBDifferences, Options.[Default], True, folderOptions)

            ' We can now access the messages and warnings
            Form1.txtMessages.Text &= \"Messages:\" & vbCrLf

            For Each message As Message In work.Messages
                Form1.txtMessages.Text &= message.Text & vbCrLf

            Form1.txtMessages.Text &= \"Warnings:\" & vbCrLf

            For Each message As Message In work.Warnings
                Form1.txtMessages.Text &= message.Text & vbCrLf

            ' Disposing the execution block when it's not needed any more is important to ensure
            ' that all the temporary files are cleaned up
            Using block As ExecutionBlock = work.ExecutionBlock
                ' Display the SQL used to synchronize
                Form1.txtMessages.Text &= \"SQL to synchronize:\" & vbCrLf

                ' Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
                ' BlockExecutor executor = new BlockExecutor();
                ' executor.ExecuteBlock(block, \".\", \"WidgetLive\");
                Form1.txtMessages.Text &= block.GetString() & vbCrLf
            End Using
            ' and we can also perform the synchronization now

            ' Retrieve the list of actions to perform to synchronize the database against the scripts folder
            Dim scriptActions As ScriptSynchronizationActions = work.ScriptSynchronizationActions

            ' Display the files that will be modified during the synchronization
            Form1.txtMessages.Text &= \"Script files that will be modified:\" & vbCrLf

            For Each action As ScriptSynchronizationAction In scriptActions.SynchronizationActions.Values
                Form1.txtMessages.Text &= action.FileName & vbCrLf

            ' Check for any read-only files
            Catch generatedExceptionName As SqlCompareException
                ' An exception here means that there are read-only files in the scripts folder.
                ' We can continue, but they will be forcibly overwritten. For the purposes of
                ' this demonstration we choose to replace any read-only files, so we ignore
                ' the exception.
            End Try
            ' Synchronize the scripts folder, overwriting any read-only files
        End Using

    End Sub
Posts: 3
Joined: Thu Aug 06, 2009 2:27 am

Postby Chris Auckland » Fri Aug 07, 2009 2:59 pm

Thanks for your post.

It seems the examples on the labs site need a little attention.

You should find the example in the SQL Compare API sample project works a lot better. You can download it from here:
http://www.red-gate.com/sqltoolkit/late ... ompare_API

I just tested this example, and it worked fine for me.

I hope this helps.
Chris Auckland
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests