Red Gate forums :: View topic - Comparison not generating Primary Keys or Foreign Keys
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

Comparison not generating Primary Keys or Foreign Keys

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



Joined: 05 Oct 2010
Posts: 3

PostPosted: Tue Oct 05, 2010 4:37 pm    Post subject: Comparison not generating Primary Keys or Foreign Keys Reply with quote

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:


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

Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 308
Location: Cambridge, UK

PostPosted: Wed Oct 06, 2010 5:45 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
gusoakes



Joined: 05 Oct 2010
Posts: 3

PostPosted: Wed Oct 06, 2010 7:13 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Chris Auckland



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

PostPosted: Mon Oct 11, 2010 8:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
gusoakes



Joined: 05 Oct 2010
Posts: 3

PostPosted: Thu Oct 14, 2010 4:58 pm    Post subject: Reply with quote

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:


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

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 Oct 15, 2010 4:31 pm    Post subject: Reply with quote

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