How to catch a double SQL error when deploying a Foreign Key
Ozzie
Posts: 47 Bronze 5
Here's an instance.
If you go to add a Foreign key to a child table where the referenced column in the parent table is not a primary key, you will receive 2 errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Mosaic.dbo.ProgramRecoType' that match the referencing column list in the foreign key 'FK_ProviderServiceCountryRecommendation_ProgramRecoType_ProgramRecoType_ID_ProgramRecoType_ID'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
If you try/catch - you only catch the second error - resulting in the display of
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Which does me no good? Is there actually a way to catch this when deploying scripts?
Doug
If you go to add a Foreign key to a child table where the referenced column in the parent table is not a primary key, you will receive 2 errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Mosaic.dbo.ProgramRecoType' that match the referencing column list in the foreign key 'FK_ProviderServiceCountryRecommendation_ProgramRecoType_ProgramRecoType_ID_ProgramRecoType_ID'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
If you try/catch - you only catch the second error - resulting in the display of
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Which does me no good? Is there actually a way to catch this when deploying scripts?
Doug
Comments
I'm guessing you're building a C# project from packager and you want to be able to catch the first error in that?
I'm not immediately sure off the top of my head. I imagine it's something to do with the way SQL Server executes the statements and returns the errors. You might want to see if the InnerException contains anything further... but I'll see if I can set up a replication here to try it out on myself
Redgate Software