Database to Source Control Failing on KEY Scripts

Darwin.L

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

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.