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

Converting a Clustered Index on a PK Identity field to non-clustered

Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.

If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.

So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?

ThanksYou can certainly drop the PK and recreate it non-clustered.

If you have FKs to this PK, you'll have to drop them first though, and then recreate them once you have recreated your PK.

This is one reason why using the UI for table design is not a good idea - better to write the DDL yourself so you know exactly what you are getting. :}|||Here are a couple of scripts to help you identify FKs related to the PK column that you have.

Instructions:
1. Make a backup of the DB before you do this.
2. Copy and paste the two snippets of code into QA.
3. Press CTRL+Shift+M to set the variables for the table name and the column ID of the column with the PK.
4. Run the CREATE script FIRST.
5. Copy and paste the results into another QA window.
6. Run the DROP script.
7. Copy and paste the drop script results into a QA window
8. Execute the drop statement
9. Alter your PK
10. Execute the CREATE script that was generated earlier.

/* DROP SPECIFIC FOREIGN KEYS */
print '-- Drop Specific Foreign Keys related to a field'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.owner varchar(800)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name, user_name(so.uid) as Local_Tab_Owner
from sysforeignkeys k inner join sysobjects so on
k.fkeyid = so.id
where k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner

WHILE @.@.FETCH_STATUS = 0
BEGIN

select @.pline = 'ALTER TABLE [' + @.owner + '].[' + @.tabName + '] '

select @.pline = @.pline + 'DROP CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Code to generate DDL for FKs

-- Generate Adds for SELECTED Foreign Keys in Database
print '-- Add Foreign Keys'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.refName varchar(800), @.fkOwner varchar(800), @.refOwner varchar(800)
DECLARE @.isDel int, @.isUpd int, @.isNotRepl int, @.isNotTrusted int, @.isDisabled int, @.fkCol varchar(8000), @.refCol varchar(8000)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name,
object_name(rkeyid) as Remote_Tab_Name,
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ) DeleteCascade,
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' ) UpdateCascade,
OBJECTPROPERTY ( constid , 'CnstIsNotRepl' ) NotForReplication,
OBJECTPROPERTY ( constid , 'CnstIsNotTrusted' ) NotTrusted,
OBJECTPROPERTY ( constid , 'CnstIsDisabled' ) Disabled,
USER_NAME(fkso.uid) fkOwner,
USER_NAME(refso.uid) refOwner
from
sysforeignkeys k inner join sysobjects fkso on
k.fkeyid = fkso.id
inner join sysobjects refso on
k.rkeyid = refso.id
where
k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.fkCol = NULL
SELECT @.fkCol = ISNULL(@.fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno

select @.refCol = NULL
SELECT @.refCol = ISNULL(@.refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno
select @.pline = 'ALTER TABLE [' + @.fkOwner + '].[' + @.tabName + '] '

if @.isNotTrusted = 1
select @.pline = @.pline + 'WITH NOCHECK'
select @.pline = @.pline + ' ADD CONSTRAINT [' + @.fkName + ']' + CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @.fkCol + ') REFERENCES [' + @.refOwner + '].[' + @.refName +
'] (' + @.refCol + ')'

if @.isDel = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @.isUpd = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
if @.isNotRepl = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' NOT FOR REPLICATION'
select @.pline = @.pline + CHAR(13) + CHAR(10) + 'go'
if @.isDisabled = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
'ALTER TABLE [dbo].[' + @.tabName + ']' +
' NOCHECK CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Regards,

hmscott|||Thanks for the tips. I normally do my DDL with T-SQL and not EM, but this was just some quick and dirty test stuff. I should have known that I would have to drop the FK constraints before dropping the PK constraint.

Convert into Default Instance.

I have by mistaken installed Named Instance, now I wanna convert this as a
default instance, how can I?
Thanks
Hi
Detach your databases, un-install the named instance, install the default
instance and re-attach the databases.
There is nothing wrong with having named instances, any reason you have to
go back?
Regards
Mike
"Joh" wrote:

> I have by mistaken installed Named Instance, now I wanna convert this as a
> default instance, how can I?
> Thanks
>
>