What are the challenges you face when working across database platforms? Take the survey
Options

Comparison not generating Primary Keys or Foreign Keys

gusoakesgusoakes Posts: 4
I am attempting to create a full DB build script by comparing a script directory with an empty directory. The script file is being generated without any PKs or FKs. I have seen other references to this issue on the forums, but have not seen any resolution. Am I doing something wrong? Here is the code I am using:
Public Shared Function GenerateBuildSql(ByVal pSourceScriptDir As String) As String
	Using fromDb As New Database(), toDb As New Database(), tempDir As New TempDirectory()
		Dim dbInfo As New ReadFromFolder.ScriptDatabaseInformation()
		dbInfo.SQLServerDBVersion = RedGate.Shared.SQL.Server.SQLVersion.SqlServer2008

		Dim myOptions As Options = Options.Default Xor Options.IgnoreCollations Xor Options.NoSQLPlumbing

		fromDb.Register(pSourceScriptDir, dbInfo, myOptions)
		toDb.Register(tempDir.Path, dbInfo, myOptions)

		Dim diffs As Differences
		diffs = fromDb.CompareWith(toDb, myOptions)

		Dim wrk As New Work
		wrk.BuildFromDifferences(diffs, myOptions, runOnTwo:=True)
		Dim sql As String = wrk.ExecutionBlock.GetString

		Return sql
	End Using
End Function

Comments

  • Options
    It looks like it is having trouble identifying which column contain the Primary Key. You can use TableMappings to achieve this.

    The link below provides an example to demonstrate how you can set this up:
    http://labs.red-gate.com/index.php/TableMappingExample
  • Options
    The link you sent indicates that TableMapping is for mapping tables with different names, which is not the case here. It also says that TableMapping is for a data compare, which is not what I am doing. In any case, how could I do a table mapping if I don't know what the tables are? If I knew that, I wouldn't need SQL Compare!


    This process works with the SQL Compare UI, but gives problems with the SDK as well as the command line. What needs to be done to make this work?
  • Options
    If it works correctly from the UI, then it should also work the same through the command line.

    Can you post (or email) the command you've used?

    I'm not sure which exact version of SQL compare engine you're using, but it might be an idea to try the latest patch version of SQL Compare. At least make sure you're on the latest version (8.2).

    If you want to try the patch, you can download it from here:
    <http://www.red-gate.com/messageboard/viewtopic.php?t=11077&gt;

    Your function looks ok, but if the above doesn't help, I'll try it out myself.
    Chris
  • Options
    I found out what the problem was, from another post. Turns out that a change script is not correctly generated unless you use the option "ForceSyncScriptGeneration". Seems like that should be the default. There should at least be some documentation somewhere on what that option does.

    For anyone interested, the final (working) function is here:
    Public Shared Function GenerateBuildSql&#40;ByVal pSourceScriptDir As String&#41; As String
       Using fromDb As New Database&#40;&#41;, toDb As New Database&#40;&#41;, tempDir As New TempDirectory&#40;&#41;
          Dim dbInfo As New ReadFromFolder.ScriptDatabaseInformation&#40;&#41;
          dbInfo.SQLServerDBVersion = RedGate.Shared.SQL.Server.SQLVersion.SqlServer2008
    
          Dim myOptions As Options = Options.Default Xor
                    Options.IgnoreCollations Xor
                    Options.NoSQLPlumbing Xor
                    Options.ForceSyncScriptGeneration
    
          fromDb.Register&#40;pSourceScriptDir, dbInfo, myOptions&#41;
          toDb.Register&#40;tempDir.Path, dbInfo, myOptions&#41;
    
          Dim diffs As Differences
          diffs = fromDb.CompareWith&#40;toDb, myOptions&#41;
    
          Dim wrk As New Work
          wrk.BuildFromDifferences&#40;diffs, myOptions, runOnTwo:=True&#41;
          Dim sql As String = wrk.ExecutionBlock.GetString
    
          Return sql
       End Using
    End Function 
    
    
  • Options
    Thanks for letting us know how you fixed the problem.

    The default behaviour is to update the data source rather than output the migration script, but I agree that this should be documented or added to the example.
    Chris
Sign In or Register to comment.