Comparison not generating Primary Keys or Foreign Keys

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Comparison not generating Primary Keys or Foreign Keys

Postby gusoakes » Tue Oct 05, 2010 4:37 pm

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:

Code: Select all

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

gusoakes
 
Posts: 3
Joined: Tue Oct 05, 2010 4:29 pm

Postby chriskelly » Wed Oct 06, 2010 5:45 pm

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
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby gusoakes » Wed Oct 06, 2010 7:13 pm

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?
gusoakes
 
Posts: 3
Joined: Tue Oct 05, 2010 4:29 pm

Postby Chris Auckland » Mon Oct 11, 2010 8:15 pm

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>

Your function looks ok, but if the above doesn't help, I'll try it out myself.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby gusoakes » Thu Oct 14, 2010 4:58 pm

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:

Code: Select all

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

gusoakes
 
Posts: 3
Joined: Tue Oct 05, 2010 4:29 pm

Postby Chris Auckland » Fri Oct 15, 2010 4:31 pm

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