How to catch double error messages from SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to catch double error messages from SQL?

Author

Topic

Ozzie19
Starting Member

2 Posts

Posted - 2011-03-22 : 12:40:55

None
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? Is this a bug?

Doug

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:38:57

Create a primary Key?

I guess I don't understand..are you running some kind of Automated database structure ALTERation script??

If Yes...Don't do that...you can hurt yourself

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 14:10:13

Do an existence check before you add the constraint, like this:

IF EXISTS(SELECT * FROM Mosaic.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='ProgramRecoType' AND CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE'))
BEGIN
	ALTER TABLE dbo.ProgramRecoType ADD CONSTRAINT ...
END

Alternately, it might make more sense to do a NOT EXISTS check and throw an error (RAISERROR) for that issue, since your script cannot proceed until it's corrected.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 14:45:12

Or you can know your database and just write the appropriate script

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-22 : 15:30:03

quote:
Originally posted by X002548

Or you can know your database and just write the appropriate script

<snip>

I agree knowing you database is a good thing.

However, there are times when you might need to execute these sorts of scripts on an object that is in a bad state. For example, if you had an ETL that droped all the indexes on a table before a load, you want to check to see if they exist before you drop them (again) incase the previous execution failed after droping the indexes and they were not recreated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources