Constraint Errors: What Blew Up?

[Gene Wirchenko]

Dear SQLers:

If a constraint of some sort is violated, how can I find out
which one?

Yes, I can parse the error message hoping to find it, but, as
pointed out by Erland, that is subject to the error message being
changed. I prefer to avoid kludges.

Is there a function that returns this value?

Anyone for an error_constraint() function?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
If a constraint of some sort is violated, how can I find out
which one?
Yes, I can parse the error message hoping to find it, but, as
pointed out by Erland, that is subject to the error message being
changed. I prefer to avoid kludges.
Is there a function that returns this value?
Anyone for an error_constraint() function?

You can always try to raise your voice on
http://connect.microsoft.com/SqlServer/FeedBack
It helps if you can explain this from a business perspective.

But for now, you will have to parse the error message. You can stick it into
a function, so if the message is changed in a futre version of SQL Server,
you only have to change the function.

[Gene Wirchenko]

On Tue, 22 May 2012 07:27:13 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
If a constraint of some sort is violated, how can I find out
which one?
Yes, I can parse the error message hoping to find it, but, as
pointed out by Erland, that is subject to the error message being
changed. I prefer to avoid kludges.
Is there a function that returns this value?
Anyone for an error_constraint() function?

You can always try to raise your voice on
http://connect.microsoft.com/SqlServer/FeedBack
It helps if you can explain this from a business perspective.

"try" is the word. Apparently, a Windows Live id is required and
one has already been created for my E-mail address. I might have
tried to create one years ago and had it fail somehow.

Post by Erland Sommarskog
But for now, you will have to parse the error message. You can stick it into
a function, so if the message is changed in a futre version of SQL Server,
you only have to change the function.

That will be a lot of work going through the various, possible
related errors, but I suppose it will have to do.

Since I can not post to the feedback, could you please post the
following on my behalf?

***** Start of Feedback *****
Dear SQL Server Feedback:

0) Requiring a Windows Live id account in order to post is rather
unfriendly. I have not been able to get one set up. Or rather,
apparently, I did set one up, but it does not work. An alternative
way, such as E-mail, would be a good idea. This is being posted on my
behalf.

1) I am developing an application that will use SQL Server. Because
I want a friendly application and I want validated data, I want to
have db constraints. There are some complications.

First, I want to be able to detect which constraint failed so I
can return an appropriate return code so that I can set focus
appropriately in the front-end. I suggest a function
error_constraint() which would return the name of the constraint that
failed. Currently, I am going to have to write a function to parse
the relevant error messages. This will be prone to breaking should
you change the error messages.

Second, there is no defined order (that I know of) for the order
of checking of the constraints. I would like them to fire in an order
that makes sense to my front-end. I realise that many will not care
about the order and consider speed more important. I am fine with it
defaulting to optimise for speed, but I would like to be able to
specify the order.

Sincerely,

Gene Wirchenko
***@ocis.net
***** End of Feedback *****

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
"try" is the word. Apparently, a Windows Live id is required and
one has already been created for my E-mail address. I might have
tried to create one years ago and had it fail somehow.

So register a new Live ID. The username there does not have to be a
real mail address - my Live ID is not an existing mail address and
has never been.

You can still setup a profile and have updates on the Connect item
sent to your mail address. Which can be different from the Live ID.
Or try your mail address and click "Forgot your password?".

Post by Gene Wirchenko
Since I can not post to the feedback, could you please post the
following on my behalf?

It has happened that I filed Connect items on behalf of other people,
but this has mainly been bugs where I also did some part of the
analysis.

This is a feature request, and not one that I would rank as terribly
important, at least not compared to all other things I think are
important, and they still refuse to implement for some reason.

Also, wouldn't a better solution be the ability to attach an error
message to the constraint definition?

[Gene Wirchenko]

On Tue, 22 May 2012 23:51:18 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
"try" is the word. Apparently, a Windows Live id is required and
one has already been created for my E-mail address. I might have
tried to create one years ago and had it fail somehow.

So register a new Live ID. The username there does not have to be a
real mail address - my Live ID is not an existing mail address and
has never been.
You can still setup a profile and have updates on the Connect item
sent to your mail address. Which can be different from the Live ID.
Or try your mail address and click "Forgot your password?".

Some Websites are decidedly unfriendly when they fail. I had not
gotten a success message.

Post by Erland Sommarskog

Post by Gene Wirchenko
Since I can not post to the feedback, could you please post the
following on my behalf?

It has happened that I filed Connect items on behalf of other people,
but this has mainly been bugs where I also did some part of the
analysis.
This is a feature request, and not one that I would rank as terribly
important, at least not compared to all other things I think are
important, and they still refuse to implement for some reason.

The more things accessible by a good interface, the better.
Having to scrape error messages is prone to error.

Post by Erland Sommarskog
Also, wouldn't a better solution be the ability to attach an error
message to the constraint definition?

And what happens to the error message to get it sent back to the
front-end? At some point, it has to be specified that this is done,
and that is where the function would be called.

Or I am missing something.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
And what happens to the error message to get it sent back to the
front-end? At some point, it has to be specified that this is done,
and that is where the function would be called.

The idea is that instead of standard message you get now, SQL Server would
emit your custom-designed error message.

Of course, all that depends on how the feature is designed.

[Gene Wirchenko]

On Wed, 23 May 2012 07:21:17 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
And what happens to the error message to get it sent back to the
front-end? At some point, it has to be specified that this is done,
and that is where the function would be called.

The idea is that instead of standard message you get now, SQL Server would
emit your custom-designed error message.

Emit it where? I want to capture so that the front-end can use
it.

Post by Erland Sommarskog
Of course, all that depends on how the feature is designed.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
Emit it where? I want to capture so that the front-end can use
it.

To exactly the same place where error messages are emitted today. That is,
the custom error message would replace the system-generated message.