Comparison not generating Primary Keys or Foreign Keys

gusoakes

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

chriskelly

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

gusoakes

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?

Chris Auckland

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.

gusoakes

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(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 Xor
                Options.ForceSyncScriptGeneration

      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 

Chris Auckland

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.