Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1061 > unrolled thread

Constraint Errors: What Blew Up?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Constraint Errors: What Blew Up? Gene Wirchenko <genew@ocis.net> - 2012-05-21 15:52 -0700
    Re: Constraint Errors: What Blew Up? Erland Sommarskog <esquel@sommarskog.se> - 2012-05-22 07:27 +0000
      Re: Constraint Errors: What Blew Up? Gene Wirchenko <genew@ocis.net> - 2012-05-22 11:12 -0700
        Re: Constraint Errors: What Blew Up? Erland Sommarskog <esquel@sommarskog.se> - 2012-05-22 23:51 +0200
          Re: Constraint Errors: What Blew Up? Gene Wirchenko <genew@ocis.net> - 2012-05-22 17:00 -0700
            Re: Constraint Errors: What Blew Up? Erland Sommarskog <esquel@sommarskog.se> - 2012-05-23 07:21 +0000
              Re: Constraint Errors: What Blew Up? Gene Wirchenko <genew@ocis.net> - 2012-05-23 09:07 -0700
                Re: Constraint Errors: What Blew Up? Erland Sommarskog <esquel@sommarskog.se> - 2012-05-23 23:32 +0200
                  Re: Constraint Errors: What Blew Up? Gene Wirchenko <genew@ocis.net> - 2012-05-23 15:03 -0700
                    Re: Constraint Errors: What Blew Up? Erland Sommarskog <esquel@sommarskog.se> - 2012-05-24 07:40 +0000

#1061 — Constraint Errors: What Blew Up?

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

[toc] | [next] | [standalone]

#1063

Gene Wirchenko (genew@ocis.net) writes:
>      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.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1066

On Tue, 22 May 2012 07:27:13 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      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.

>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
genew@ocis.net
***** End of Feedback *****

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1067

Gene Wirchenko (genew@ocis.net) writes:
>      "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?".
 
>      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?

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1068

On Tue, 22 May 2012 23:51:18 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      "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.

>>      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.

>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

[toc] | [prev] | [next] | [standalone]

#1069

Gene Wirchenko (genew@ocis.net) writes:
>      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.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1070

On Wed, 23 May 2012 07:21:17 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      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.

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

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1071

Gene Wirchenko (genew@ocis.net) writes:
>      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.
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1072

On Wed, 23 May 2012 23:32:47 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      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.

     Where is that?  You are making assumptions about my environment.
I do not know what you are even referring to.  I am doing my work in
SSMS for now and intend to link it with my front-end.  I am concerned
with the use with my front-end (a Webpage).

     I think I pass it back using a cursor, but I have not gotten
there yet.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1073

Gene Wirchenko (genew@ocis.net) writes:
>>To exactly the same place where error messages are emitted today. That is,
>>the custom error message would replace the system-generated message.
> 
>      Where is that?  You are making assumptions about my environment.
> I do not know what you are even referring to.  I am doing my work in
> SSMS for now and intend to link it with my front-end.  I am concerned
> with the use with my front-end (a Webpage).
 
I am not making any assumptions about your environment. I am discussing a
hypotethecial imrpovement to SQL Server which would solve the problem you
have identified in a different way from what you suggested.

I am not holding my breath for Microsoft to implement any of them.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web