converting default check constraint messages to friendly ones

I'm trying to set up a table that will convert default SQL error messages
for check constraints into friendly front end messages. What I'm having
trouble with is how to pick apart the default message so I can do this. Is
the error stored anywhere that I can look at it's parts? Any suggestions on
a good strategy for this sort of thing would be appreciated.
Thanks,
KeithFriendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e80rgIRTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> I'm trying to set up a table that will convert default SQL error messages
> for check constraints into friendly front end messages. What I'm having
> trouble with is how to pick apart the default message so I can do this. Is
> the error stored anywhere that I can look at it's parts? Any suggestions
> on
> a good strategy for this sort of thing would be appreciated.
> Thanks,
> Keith
>|||Thanks, OJ. But what code? I know how to do that in stored procedures but in
Check Constraints, where is there a code? I created a constriant that
prevents zero length strings on a column in a table as follows:
Len(LTrim(RTrim(CustName))) > 0
And the message that is returned is:
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'.
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'
If I can always be guaranteed that the message will start with "Database
error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'" then I suuppose I can parse it out. But I was hoping there
would be an easier way to do it.
Keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:OKznpORTFHA.2548@.TK2MSFTNGP14.phx.gbl...
Friendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj|||Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%235ONvURTFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Thanks, OJ. But what code? I know how to do that in stored procedures but
> in
> Check Constraints, where is there a code? I created a constriant that
> prevents zero length strings on a column in a table as follows:
> Len(LTrim(RTrim(CustName))) > 0
> And the message that is returned is:
> Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
> statement conflicted with COLUMN CHECK constraint
> 'CK_tblCustomers_CustName'. The conflict occurred in database
> 'WidgetsInc',
> table 'tblCustomers', column 'CustName'.
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'
> If I can always be guaranteed that the message will start with "Database
> error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'" then I suuppose I can parse it out. But I was hoping there
> would be an easier way to do it.
> Keith
>|||Thanks, OJ. No, I did understand. Just that there's no error number
returned to the client side (using Delphi). Just the text I showed you
below. I'll investigate this further in the Borland NGs. Thanks
again. -keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:egat8fRTFHA.2908@.TK2MSFTNGP10.phx.gbl...
Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj

Convert unique clustered indexes to PK constraints

Hi,
We have a database where no PK constraints are defined and only unique
clustered indexes. Is there a way for us to change all the columns that make
up the unique clustered index in all of the tables in the database to be the
columns that make up the PK constraint of the tables? In other words, we
want to change the clustered indexes to actual PK constraints.
Thanks,
DeeThere is no easy way. You have to drop the CI and then run the alter table
commands. You could use the system catalogs to help generate the commands
though.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:8366A973-76BE-4731-8960-E3D487D41AB8@.microsoft.com...
> Hi,
> We have a database where no PK constraints are defined and only unique
> clustered indexes. Is there a way for us to change all the columns that
> make
> up the unique clustered index in all of the tables in the database to be
> the
> columns that make up the PK constraint of the tables? In other words, we
> want to change the clustered indexes to actual PK constraints.
> Thanks,
> Dee

Convert unique clustered indexes to PK constraints

Hi,
We have a database where no PK constraints are defined and only unique
clustered indexes. Is there a way for us to change all the columns that make
up the unique clustered index in all of the tables in the database to be the
columns that make up the PK constraint of the tables? In other words, we
want to change the clustered indexes to actual PK constraints.
Thanks,
Dee
There is no easy way. You have to drop the CI and then run the alter table
commands. You could use the system catalogs to help generate the commands
though.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:8366A973-76BE-4731-8960-E3D487D41AB8@.microsoft.com...
> Hi,
> We have a database where no PK constraints are defined and only unique
> clustered indexes. Is there a way for us to change all the columns that
> make
> up the unique clustered index in all of the tables in the database to be
> the
> columns that make up the PK constraint of the tables? In other words, we
> want to change the clustered indexes to actual PK constraints.
> Thanks,
> Dee