How can we help you today? How can we help you today?

Database to Source Control Failing on KEY Scripts

Hi - I am trying to use SQL Compare to update our source control script folder from a SQL Database. The problem I seem to have is that the database generated version of a table that has keys - either PK or FK - includes an ALTER statement to add the contsraint - however in our Source Control folders the PK & FK definitions are cotained in stand-alone scripts inside a "/Keys" folder.

For example: I have a table called "[dbo].[DatHrzEmpExecPensionPlan]", which has a PK called "PK_DatHrzEmpExecPensionPlan". When the database was scripted for our source control (using Visual Studio for TFS), it generates a CREATE TABLE script, and then a stand-alone PK script in a "/Keys" folder.

When SQL Compare does the compare, I get the following errors for the "/Keys" scripts:
"A refrenced object name [dbo].[DatHrzEmpExecPensionPlan] was not found"

The PK script contains the code:
ALTER TABLE [dbo].[DatHrzEmpExecPensionPlan]
which is triggering the error.

I can contune without resolving these, but then - when I go to deploy the changes to the scripts, it adds the ALTER TABLE .... ADD CONSTRAINT code to the table script. This then causes a duplicate key definition error to occur in future compares.

if I set the compare to ignore Constraints and Foreign keys it resolves this, but then I am missing that compare, which partially defeats the purpose of foing the compare.

Any ideas how to overcome this?

Thanks,
Darwin
Darwin.L
0

Comments

1 comment

Sort by
  • Allen LeVan
    Hello,

    Thanks for your post. Sorry to hear about the issue you are having. We opened a ticket for this issue so we could take a deeper dive/investigation into it.

    For your setup you would not want to deploy to that folder as a target. SQL Compare is not best used that way (deploying from source control is the better workflow), you could use it to generate scripts or with that folder as a source to a non source controlled repository.

    Then this would need to go through SQL Source Control as a commit which should work into your repository.

    Let me know if you have any other questions, thanks.
    Allen LeVan
    0

Add comment

Please sign in to leave a comment.