How to catch a double SQL error when deploying a Foreign Key

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

How to catch a double SQL error when deploying a Foreign Key

Postby Ozzie » Tue Mar 22, 2011 5:19 pm

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
Ozzie
 
Posts: 18
Joined: Tue Jun 26, 2007 9:10 pm
Location: Havertown, PA, USA

Postby james.billings » Wed Mar 23, 2011 8:19 pm

Thanks for posting.

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
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests