Red Gate forums :: View topic - updating scripts from live db
Return to www.red-gate.com RSS Feed Available

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

updating scripts from live db

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
toddwhitehead



Joined: 06 Aug 2009
Posts: 3

PostPosted: Thu Aug 06, 2009 2:39 am    Post subject: updating scripts from live db Reply with quote

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:

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
            Next

            ' 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
            Next

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

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

            ' 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
            Next

            ' Check for any read-only files
            Try
                scriptActions.Check()
            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
            scriptActions.Execute()
        End Using

    End Sub
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Aug 07, 2009 2:59 pm    Post subject: Reply with quote

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/latestsamples/sql_compare_API

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

I hope this helps.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
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