January 31, 2003 at 4:21 am
When working with SQL Server 2000 I have encountered the following problem: according to the SQL Server 2000 specs, setting the XACT_ABORT option to OFF should not rollback the current transaction if an error occurs. Everything works fine, except when I have a data conversion error, in which case the server behaves like the XACT_ABORT option is set to ON (rolls back the transaction). This is a really pain in the ass because it leaves me with no possiblity to properly address the exception occured.
STEPS TO REPRODUCE
OS version: Microsoft Windows 2000 Advanced Server SP3
Hardware platform: 4Processor Intel based IBM xSeries
SQL Server 2000 SP2 Enterprise Edition
Steps to reproduce:
CREATE TABLE test1(test INT)
GO
CREATE PROCEDURE testproc AS
SET XACT_ABORT OFF
BEGIN
BEGIN TRAN
INSERT INTO test1(test) VALUES (1)
INSERT INTO test1(test) VALUES (‘a’)
INSERT INTO test1(test) VALUES (2)
COMMIT
END
GO
EXEC testproc
January 31, 2003 at 4:54 am
quote:
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
Verification of datatypes occurrs at compile time not run time and is considered a compile error.
To show you the difference change the value from 'a' to 4 and add a constraint to the table with the following syntax.
test != 4
and save. This time the run will do what you are expecting.
January 31, 2003 at 5:01 am
quote:
quote:
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.Verification of datatypes occurrs at compile time not run time and is considered a compile error.
To show you the difference change the value from 'a' to 4 and add a constraint to the table with the following syntax.
test != 4
and save. This time the run will do what you are expecting.
True, but not for table columns datatypes.
So, this verification is done at runtime. Anyway I could use a CAST or CONVERT function and obtain the same result (and they are surely verified at run time).
January 31, 2003 at 5:21 am
For CAST and CONVERT won't change anything. We are just talking the difference between explicit conversion verses implicit.
As for compile time there seems to be a point here that I am still trying to validate.
There are 2 forms of compile.
Early compile and Late compile.
Early compile is seen in the basic code compile. For example if you mispell INSERT like INSRT you will get an error immediately and the code will not run at all.
Late compile is validation and conversion before execution. The INSERT of 'a' does not check the table datatypes until running, the it is compiled to implicitly convert to a matching datatype and run.
Then you have the actual run which will hit the other validation items on a table. Such as triggers, defaults if NULL supplied, constraints and so on.
With 2000 the first compile is done when the code is parsed. CREATE parses at that time without validation of typing. That is why you can create an SP like so
CREATE PROC ip_x
AS
DECLARE @a int
SET @a = 5
SET @a = 'b'
and it will parse fine. However on execution the code is tpyed and the 'b' will cause an error.
I guess it is more like this.
Parse - First compile of code.
First validation - Explicit/Implicit typing at the stage for the line executes, still considered a compile operation.
Second validation - actuall run of the line and the server side items do their validation.
January 31, 2003 at 2:17 pm
Just to clear things up a bit now that my brain is working. The three stages of SQL code during it's life cycle are these
Parse Time -- Syntax validation only.
Compile Time -- Validation of code, implcit/explicit conversion, code built to run in SQLs memory similar to any language complie.
Runtime --- Actually execution of the code.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply