February 18, 2010 at 2:03 am
Hi,
I have created a table variable in SQL 2008 that is based on a user defined table type. The UDTT has multiple check constraints on the columns. What I want to do, is when the check constraint is breached, I want to catch the error and return the contents of the check constraint in the error message.
I can catch the error no problems. The only issue I have is linking the value returned in ERROR_MESSAGE() for the check constraint back to the system tables (sys.syscomments) it appears that the object name returned by the error message bears no relation to the object name in sys.syscomments.
I have tried the same thing using temp tables and the object name returned in the error message and in sys.syscomments is exactly the same.
Has anyone else come across this (and found a solution)? Am I looking in the wrong system tables? Is there a way to link these 2 object names when using table variables??
Thanks in advance
Barry
February 18, 2010 at 6:13 am
Barry,
Basically, it comes down to the fact that UDTTs are a TYPE.
When you create an instance of the type, you create a new table - which has its own object_id and, critically, has a different system-generated name for the instance of the CHECK constraint.
System tables you might be interested in:
sys.table_types (the type is here)
sys.tables (each instance of the type is here)
sys.check_constraints (each instance of the check constraint *plus* the TYPE's constraint is here)
See in particular, the definition column on sys.check_constraints.
Note that a new row appears in sys.check_constraints for the lifetime of the variable that is an instance of a UDTT.
Paul
February 18, 2010 at 6:59 am
Hi Paul.
Thanks for your response. Does that mean that I should be able to link the value returned in the Error meassge that is raised due to the check constraint being breached to an id in the sys.table_types and then link this to the sys.check_constraints to get the definition??
Thanks again
Barry
February 18, 2010 at 7:10 am
Barry,
No, I think you're going to have to jump through some hoops on this one, and it may not be pretty.
That's just my recollection - it's been a while since I played with UDTTs.
Ordinarily I would look into this and post a better reply, but it is 3:09am here in GMT+13, so I'm off to bed.
Have a play around with the system tables - if you're still stuck, post back and I'll take a look in the morning.
Cheers
Paul
edit: I rushed that: your general plan of attack is sound...but the names won't match, no...:pinch:
February 18, 2010 at 10:08 am
Hi Paul.
Thank for you help. I have had a play but I am getting nowhere fast.
I have got this so far:
select *
from sys.table_types as TT
INNER JOIN sys.check_constraints as CC
ON TT.type_table_object_id = CC.parent_object_id
WHERE [type] = 'C'
Which gives me the UDTT and it's associated CHECK Constraint(s).
I am still however, struggling to link this back to the Error message.
Any further help would be much appreciated.
Thanks in advance
Barry
February 19, 2010 at 4:49 am
Barry,
I had a deeper look at this today, and I'm afraid the news isn't good.
It's complex, so here's a reproduction script:
USE tempdb;
GO
-- Drop the test table type
IF EXISTS (SELECT * FROM sys.table_types WHERE name = N'Test' AND [schema_id] = SCHEMA_ID(N'UDTT')) DROP TYPE UDTT.Test;
GO
-- Drop the containing schema
IF SCHEMA_ID(N'UDTT') IS NOT NULL EXECUTE (N'DROP SCHEMA UDTT');
GO
-- Create the schema
CREATE SCHEMA UDTT;
GO
-- Create the type
CREATE TYPE UDTT.Test AS TABLE (col1 INTEGER NOT NULL, CHECK (col1 >= 0));
GO
-- Clear the log
CHECKPOINT;
-- Show the contents of the transaction log
-- (Should be just begin and end CHECKPOINT events: LOP_BEGIN_CKPT and LOP_END_CKPT)
SELECT [Current LSN], Operation, Context, Description,
AllocUnitName, [Page ID], [Slot ID], PartitionId, [Num Elements],
SPID, [Begin Time], [End Time], [Transaction Name]
FROM fn_dblog(NULL, NULL);
-- Create an instance of the type
DECLARE @T UDTT.Test;
-- Cause a constraint violation inside a TRY...CATCH BLOCK
BEGIN TRY
-- Throws an error
INSERT @T (col1) VALUES (-1)
END TRY
BEGIN CATCH
-- Save the error message text
DECLARE @ErrorMessage NVARCHAR(2048) = ERROR_MESSAGE();
-- Used when decoding the error message text
DECLARE @Index1 INTEGER = 0,
@Index2 INTEGER = 0;
-- The two object (?) references in the error message text, converted from hex
DECLARE @Reference1 INTEGER = 0,
@Reference2 INTEGER = 0;
-- Show the check constraints
-- There are two: one for the type, and one for the instance
-- The one associated with the type has is_ms_shipped = 1
SELECT * FROM sys.check_constraints;
-- Show the parents of the constraints
-- Two entries again
-- The type has type_desc = TYPE_TABLE and is_ms_shipped = 1
-- The instance has type_desc = USER_TABLE and is_ms_shipped = 0
SELECT * FROM sys.objects WHERE object_id = ANY (SELECT parent_object_id FROM sys.check_constraints);
-- Show user tables
-- Only the instance appears, named with a # plus the hexadecimal representation of its object_id
SELECT * FROM sys.tables;
-- Decode the hexadecimal strings in the error message
SET @Index1 = CHARINDEX(N'CK__', @ErrorMessage);
SET @Reference1 = CONVERT(INTEGER, CONVERT(BINARY(4), SUBSTRING(ERROR_MESSAGE(), @Index1 + 5, 8), 2));
SET @Index2 = CHARINDEX(N'".', @ErrorMessage);
SET @Reference2 = CONVERT(INTEGER, CONVERT(BINARY(4), SUBSTRING(ERROR_MESSAGE(), @Index2 - 8, 8), 2));
-- Show the error message and string extracts (for info)
SELECT error_message_text = @ErrorMessage,
hex_fragment_1 = SUBSTRING(ERROR_MESSAGE(), @Index1 + 5, 8),
hex_fragment_2 = SUBSTRING(ERROR_MESSAGE(), @Index2 - 8, 8),
reference_1 = @Reference1,
reference_2 = @Reference2;
-- Try to find anything with the coded object ids (nothing!)
SELECT * FROM sys.all_objects WHERE object_id IN (@Reference1, @Reference2);
SELECT * FROM sys.partitions WHERE object_id IN (@Reference1, @Reference2);
SELECT * FROM sys.internal_tables WHERE object_id IN (@Reference1, @Reference2);
END CATCH;
-- Show the contents of the transaction log now
-- The only significant activity are allocations,
-- compensation log records (for the rollback), and
-- deallocations related to the instance of the UDTT
-- (AllocUnitName matches the instance's name in sys.tables e.g. #04E4BC85)
-- #04E4BC85 is the 'real' name of the table held in variable @T
SELECT [Current LSN], Operation, Context, Description,
AllocUnitName, [Page ID], [Slot ID], PartitionId, [Num Elements],
SPID, [Begin Time], [End Time], [Transaction Name]
FROM fn_dblog(NULL, NULL);
GO
It seems that the error message does contain object_ids, but not for anything we can see. I would guess that the numbers coded in the message are either random (maybe for performance reasons, given the cost of looking up the 'correct' ids); or they refer to internal-only objects which are not exposed, even in the undocumented system tables.
The other complication here is that, as far as I know, there is no way to map the name of a variable holding a UDTT to the 'real' object id or name. The metadata functions (like OBJECT_ID) do not work with variables, for example. In fact, support across the various metadata facilities seems very patchy and a bit inconsistent in places when it comes to table variables in general, and UDTTs in particular. I guess this is to be expected with a relatively new feature like UDTTs, not so much with table variables. No doubt there is a good reason for it 😉
For the same reason, given two (or more) instances of the same UDTT, it doesn't appear to be possible to determine which temporary table refers to which. That might not be important in your scenario, but it does illustrate part of the problem. To be clear, given two instances of a UDTT (@A and @b-2) and two tables #046e2a01 and #047f1786 - which is which?
The other issue is that, although well-known, the format of system-generated constraints is not documented or supported as far as I know. It is convenient that it happens to work as it does, but I'm not sure it's a good idea to rely on it. I certainly wouldn't try to decode the constraint names in production code.
So, that's all a long way of saying 'I tried and failed' really 🙂
You have my sympathy with this - I can't immediately think of a good work-around either. Can I ask why it is important that you obtain the constraint definition from your catch block?
Paul
February 19, 2010 at 6:21 am
Hi Paul,
Thanks for your efforts on this one.
The reason for wanting to return the Contraint Name and Definition in the catch block is because there are multiple constraints on the UDTT and there are hundreds on columns. When the error is thrown, it could be because of any one of these constraints so I was just trying to find a way to identify what constrint was breach and the acceptable values.
Thanks again
Barry
February 19, 2010 at 6:37 am
barry.hostead (2/19/2010)
Hi Paul,Thanks for your efforts on this one.
The reason for wanting to return the Contraint Name and Definition in the catch block is because there are multiple constraints on the UDTT and there are hundreds on columns. When the error is thrown, it could be because of any one of these constraints so I was just trying to find a way to identify what constrint was breach and the acceptable values.
Thanks again
Barry
That's ok - I enjoyed it. I thought that was what you were trying to do - but it is for debugging or to feedback to the end-user? I'm still giving this some thought, so any additional information at this stage would be useful.
Paul
February 19, 2010 at 6:52 am
Paul,
At this stage, it is for testing and debugging. Eventually, this SP could be released to other users around the business so it would then become informational for the end users.
I am currently using Management Studio and trying to handle all of this myself as per this post. I am also starting to develop in Visual Studio Team System 2010 RC so I will investigate that avenue to see if there is anything additional that Visual Studio can offer.
Thanks again for your help, I have got to the point now where I don't want to let this beat me!!
🙂
Barry:
February 19, 2010 at 6:56 am
barry.hostead (2/19/2010)
Thanks again for your help, I have got to the point now where I don't want to let this beat me!!
I feel your pain! It is an interesting problem. I'll sleep on it. Again 😀
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply