Teamcity - SQL Compare.exe (10) - Duplicate Definition Found

RameshShankarG

I am trying to create a TeamCity Build using CommandLine option. As there are more number of database objects, if i generate a complete database script by comparing Source Control and a BLANK database, the script fails due to dependency issues.

I have created 4 separate build steps for Tables, Views, Functions and Stored Procedures. Given below are the command parameters for the first two build steps just for reference.

Build Step 1

/scr1:%system.teamcity.build.checkoutDir% /revision1:HEAD
/server2:*****
/db2:Deployment
/username2:*****
/password2:*****
/Include:identical
/exclude:view
/exclude:storedprocedure
/exclude:function
/include:table
/Options:Default,IncludeDependencies,DropAndCreateInsteadofAlter
/Report:Report/SchemaDiffReport_Tables.html
/ReportType:Interactive
/ScriptFile:Report/SchemaSyncScript_Tables.sql
/Force
/IgnoreParserErrors

Build Step 2
/scr1:%system.teamcity.build.checkoutDir% /revision1:HEAD
/server2:*****
/db2:Deployment
/username2:*****
/password2:*****
/Options:Default,IncludeDependencies,ObjectExistenceChecks,DropAndCreateInsteadOfAlter
/include:identical
/include:view
/exclude:storedprocedure
/exclude:function
/exclude:table
/Report:Report/SchemaDiffReport_Views.html
/ReportType:Interactive
/ScriptFile:Report/SchemaSyncScript_Views.sql
/Force
/IgnoreParserErrors

If i execute one build step at a time (Manually disabling the other build steps), i am able to execute the build with out any issue.

If i have all the build steps enabled, it fails in Step 2 itself, exception was,

"[Step 2/4] Registering data sources
[Step 2/4] Error: A duplicate definition was found for the table [dbo].[*****].
[Step 2/4] Ensure that case sensitivity options are set correctly and all object creation
[Step 2/4] scripts are valid. If the problem persists, contact our support.
[Step 2/4] Process exited with code 126 "

The table name reported in the exception is different every time when i run the build. Please help me on this to get it resolved.. It will be a great help...

Brian Donahue

I'm afraid I do not understand why it is necessary to have a separate step for tables and views, but if you are telling SQL Compare to include dependencies, I would imagine that the cause of the problem is that when you, for instance, run the job to synchronize views, it is pulling in some dependent tables as part of the synchronization and because the command is filtering them out, SQL Compare effectively does not recognize they are already there.

If you just remove the IncludeDependencies from the options to the command-line, that is probably not going to help because "Default" also enforces IncludeDependencies as part of that. So you have to not use Default and explicitly specify all options that you want to use, if you want SQL Compare to not include dependent tables.

I'd personally use one command to synchronize the databases; can I ask if you tried that already and had some dependency issue with that?

Or you could try three syncs - one and two as tables and views without dependencies and then "everything else" on the third one.

RameshShankarG

Thank you. I have resolved the issue using the steps mentioned below.

1. Though i have Tables and Views combined together, when generating the rest of the scripts still thrown exception.

2. I have created separate scripts for Tables, View, Functions, Stored Procedures, while generating script, I have synched it against a blank database.

3. After generating all the scripts, I have dropped the database. This way i have resolved the issue.

Thanks again for your feedback and suggestions. Thank you.