| Author |
Message |
gusoakes
Joined: 05 Oct 2010
Posts: 3
|
 Posted: Tue Oct 05, 2010 4:37 pm Post subject: Comparison not generating Primary Keys or Foreign Keys |
 |
|
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 |
|
 |
chriskelly
Joined: 19 Apr 2010
Posts: 253
Location: Cambridge, UK
|
| Posted: Wed Oct 06, 2010 5:45 pm Post subject: |
|
|
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 |
|
|
gusoakes
Joined: 05 Oct 2010
Posts: 3
|
| Posted: Wed Oct 06, 2010 7:13 pm Post subject: |
|
|
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 |
|
|
Chris Auckland
Joined: 24 Oct 2006
Posts: 710
Location: Red Gate Software Ltd.
|
| Posted: Mon Oct 11, 2010 8:15 pm Post subject: |
|
|
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 |
|
|
gusoakes
Joined: 05 Oct 2010
Posts: 3
|
| Posted: Thu Oct 14, 2010 4:58 pm Post subject: |
|
|
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 |
|
|
Chris Auckland
Joined: 24 Oct 2006
Posts: 710
Location: Red Gate Software Ltd.
|
| Posted: Fri Oct 15, 2010 4:31 pm Post subject: |
|
|
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 |
|
|
|
