A duplicate definition was found

seannseann Posts: 5
edited May 12, 2016 3:11PM in SQL CI 2
Hello,

I have a SQL SSDT database project that contains PreDeploy and PostDeploy scripts. It builds successfully in VS2013 and SSDT (latest ver) and generates the publishing script in the correct fashion (Pre, Diff, Post). We have passed this project onto TeamCity using SQL CI Build & Package (Red Gate SQL CI Build). It appears that the build process has difficulty in understanding what is a PreDeploy and a PostDeploy script. We have also tried to keep the manual scripts in separate folders to the SSDT project but in the same solution folder, that did not help either, they are still validated in this fashion.

In my example I wish to have the PostDeploy script rebuild an existing index. I am simply just using this example to simulate a need to implement schema change outside of the diff tools or to even orchestrate a change in a desired fashion outside of a diff engine. We have a number of reasons why we choose to do so.

Below is the error being generate which should not be the case.

[13:12:58]Step 1/2: SQL CI Build & Package (Red Gate SQL CI Build) (5s)
[13:12:58][Step 1/2] Starting: C:Teamcitypluginssqlci-teamcity-agentassetssqlCI.exe build /scriptsFolder=ContentManagement /packageId=ContentManagement /packageVersion=1.0.0.57 /licenseSerialKey=xxxxxx
[13:12:58][Step 1/2] in directory: D:Teamcitywork4d1f627f2ed34934
[13:12:59][Step 1/2] sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.6.125
[13:12:59][Step 1/2]
[13:12:59][Step 1/2] STARTING: Verifying license
[13:12:59][Step 1/2] Starting: C:Teamcitypluginssqlci-teamcity-agentassetsSCSQLCompare.exe /activateSerial="xxxxxx"
[13:12:59][Step 1/2] /activateSerial is being ignored because the license is already activated with that serial key
[13:12:59][Step 1/2] SQL Compare: activated, edition: professional, serial number:xxxxxx
[13:12:59][Step 1/2] COMPLETED SUCCESSFULLY: Verifying license
[13:12:59][Step 1/2] STARTING: Creating scratch database sqlCI_6dd9d9b5-cf69-4326-9c1c-e164a315a720
[13:12:59][Step 1/2] COMPLETED SUCCESSFULLY: Creating scratch database sqlCI_6dd9d9b5-cf69-4326-9c1c-e164a315a720
[13:12:59][Step 1/2] STARTING: Validating database state
[13:12:59][Step 1/2] Starting: C:Teamcitypluginssqlci-teamcity-agentassetsSCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="D:Teamcity empuildTmpSQL CI5y4qgr5o.ld2dbstate" /server2="np:\.pipeLOCALDB#BF3387A0 sqlquery" /database2="sqlCI_6dd9d9b5-cf69-4326-9c1c-e164a315a720" /synchronize /tempInstance="Data Source=np:\.pipeLOCALDB#BF3387A0 sqlquery;Integrated Security=True" /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseCompatibilityLevel,UseMigrationsV2"
[13:13:00][Step 1/2] SQL Compare: activated, edition: professional, serial number: xxxxxx
[13:13:00][Step 1/2] SQL Compare Command Line V11.3.3.25
[13:13:00][Step 1/2] ==============================================================================
[13:13:00][Step 1/2] Copyright Copyright ¸ 1999 - 2015 Red Gate Software Ltd
[13:13:00][Step 1/2]
[13:13:00][Step 1/2] Warning: Could not find the file 'RedGateDatabaseInfo.xml' in the scripts folder
[13:13:00][Step 1/2] 'D:Teamcity empuildTmpSQL CI5y4qgr5o.ld2dbstate': the default settings
[13:13:00][Step 1/2] will be used instead.
[13:13:00][Step 1/2] Registering data sources
[13:13:00][Step 1/2] Error: Comparison of 'Scripts.state' and
[13:13:00][Step 1/2] 'np:\.pipeLOCALDB#BF3387A0 sqlquery.sqlCI_6dd9d9b5-cf69-4326-9c1c-e164a315a
[13:13:00][Step 1/2] 720' failed: A duplicate definition was found for the index
[13:13:00][Step 1/2] IX_StyleEnvironmentAttributeValue_StyleId. Ensure that case sensitivity options
[13:13:00][Step 1/2] are set correctly and all object creation scripts are valid. If the problem
[13:13:00][Step 1/2] persists, contact our support.
[13:13:00][Step 1/2] Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
[13:13:00][Step 1/2] FINISHED WITH ERROR: Validating database state
[13:13:03][Step 1/2] Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
[13:13:04][Step 1/2] Process exited with code 32
[13:13:04][Step 1/2] Step SQL CI Build & Package (Red Gate SQL CI Build) failed

So the question would be, how do we describe manual scripts in a fashion that would not create any schema conflicts as seen above. Did I miss something? I have had a look at the switches for the sqlCI.exe Build commands but did not see anything there.

Many thanks
Sean
· Share on Twitter

Comments

  • Rob CRob C Posts: 419 Gold 2
    Hi Sean,

    Can you look in your .sqlproj file and see how your post-deploy script is handled?

    Is it like this - 
    <ItemGroup>
<PostDeploy Include="Script.PostDeployment1.sql" />
</ItemGroup>

    or more like this 
    <ItemGroup>
<Build Include="Script.PostDeployment1.sql" />
</ItemGroup>

    Thanks.
    Software Engineer
    Redgate Software
    · Share on Twitter
  • seannseann Posts: 5
    Hi Robert,

    Apologies for the delay, I have been on leave. I have dropped in the sections of the project file I think you are looking for below.

    Please let me know if you need anything else.

    Regards
    Sean

    <ItemGroup>
    <Folder Include="Properties" />
    <Folder Include="ContentManagement" />
    <Folder Include="ContentManagementTables" />
    <Folder Include="Security" />
    <Folder Include="Scripts" />
    <Folder Include="ScriptsPreDeploy" />
    <Folder Include="ScriptsPostDeploy" />
    </ItemGroup>

    <ItemGroup>
    <PreDeploy Include="ScriptsPreDeploy1_CreateLogin.sql" />
    </ItemGroup>
    <ItemGroup>
    <PostDeploy Include="ScriptsPostDeploy1_CreateIndex.sql" />
    <None Include="ScriptsPostDeploy2_InsertData.sql" />
    </ItemGroup>
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    Hi Sean,

    I'm going to create a ticket for you. You should receive an email shortly.
    Software Engineer
    Redgate Software
    · Share on Twitter
  • sheldonhullsheldonhull Posts: 35 Silver 1
    I'd had duplicate definition issues in running sql compare from a sql project (SSDT) for a while. There are some duplicate items in the project file itself, but they ignored during deployment. I would like to have the option to ignore these duplicate definitions when they have the same content, as msbuild can deploy them, but sql compare is preventing me from doing a comparing due to this issue.

    For me to integrate CI, I can always fix the project and going forward it would be fine, but any type of comparison to previous results or further issues with SSDT doing this would cause errors that ideally would be handled in SQL Compare.
    · Share on Twitter
Sign In or Register to comment.