## updating scripts from live db

Automate and integrate using the SQL comparison API

### updating scripts from live db

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

Posts: 3
Joined: Thu Aug 06, 2009 1:27 am

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
Chris Auckland

Posts: 760
Joined: Tue Oct 24, 2006 1:12 pm
Location: Red Gate Software Ltd.