Solved

Troubleshooting TSQL Try Catch

Posted on 2007-12-03
11
2,264 Views
Last Modified: 2010-04-21
I have a TSQL TRY CATCH procedure written that isn't operating as I expect it to.

In the Try statement, I cast a nvarchar into an integer.  
False == If it isn't numeric, processing should jump to the CATCH statement where I insert this record into a different table where that field isn't requried to be an integer data type.
True == I insert the record into a table where that field is an integer data type.

My understanding of how TRY Catch works is that once an error is occurred in the Try Statement, processing immediately jumps to the Catch, errorhandling is performed then processing resumes at the top Try statement.
See Code below.

I asked this question last week and was told that I needed to handle my error.  I've done this and it still isn't working as expected.  Please help.
SELECT @SQLStr = 'SELECT * INTO ##MyTempMaintTable '

SELECT @SQLStr = @SQLStr + 'FROM OPENROWSET(BULK ''' + @FullArchivePathFile + ''', '

SELECT @SQLStr = @SQLStr + 'FORMATFILE=''' + @FormatFile + ''', '

SELECT @SQLStr = @SQLStr + 'FIRSTROW=2) as t1'
 

BEGIN TRANSACTION 

	EXEC (@SQLStr)

	DECLARE cursorMine CURSOR LOCAL

		FOR Select * from ##MyTempMaintTable

		Declare @evt_code			varchar (50)

		Declare @evt_object			varchar (4000)

		Declare @errMessage			nvarchar(4000)

		If @@Error <> 0 GOTO ErrorHandler
 

		OPEN cursorMaintSched

		IF @@Error <> 0 GOTO ErrorHandler

		

		WHILE 1=1 BEGIN

			FETCH cursorMine INTO

				@evt_code,

				@evt_object

			IF @@fetch_status <> 0 BREAK

			BEGIN TRY

				SELECT CAST(@evt_code AS INT)

				INSERT INTO tbLocal_Table(

					 evt_code, 

					 evt_object)

					VALUES(

					 @evt_code,

					 @evt_object)

			END Try

			BEGIN CATCH

				SELECT @errMessage = ERROR_MESSAGE()

				INSERT INTO tbLocal_TableTransLog(

 					 evt_code, 

					 evt_object,

					 Report,

					 AddedDate)

					VALUES(

 					 @evt_code,

					 @evt_object,

					 @evt_desc,

					 @errMessage,

					 @myDate)

			END CATCH;

		END -- WHILE 1=1	
 

		CLOSE cursorMine

		If @@error <> 0  goto ErrorHandler

		DEALLOCATE	cursorMaintSched

		If @@error <> 0  goto ErrorHandler
 

	COMMIT TRANSACTION
 

		RETURN 

	

	ErrorHandler:

		ROLLBACK TRAN

		RETURN

Open in new window

0
Comment
Question by:SDPierce
  • 7
  • 4
11 Comments
 
LVL 11

Expert Comment

by:deroby
ID: 20397111
Adding the error returned and/or giving a *working* code-snippet might help you know ... =/

Anyway, after some fiddling with the example code I came to this error :

>>> Msg 3930, Level 16, State 1, Line 54
>>> The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

From what I've found about this, it seems that the transaction is in the 'doomed state'. Not sure why, nor what it precisely means, but it comes down to the fact the transaction is suddenly read-only and that any changes made to the data will be discarded.

Maybe someone else has an idea on what this could be ???
0
 

Author Comment

by:SDPierce
ID: 20397161
Works:
evt_code:                  240828
evt_object:                  0233-004

Fails:
evt_code:                  A240828
evt_object:                  0233-004

Error:
Msg 3930, Level 16, State 1, Procedure spLocal_InsertRecords, Line 161
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I expect it to fail in the TRY portion.  However, I do not expect it to fail in the CATCH.
0
 

Author Comment

by:SDPierce
ID: 20397179
In addition, if the evt_code has a primary key duplicate error, the code works perfectly.  for example..  evt_code:  240828 is already in the main table and I try to insert it again.
0
 
LVL 11

Expert Comment

by:deroby
ID: 20397201
Anyway, here is the 'more or less' cleaned up code... wonder if anyone will be able to solve this, it's not quite what I would expect either...
(works fine without the transaction, so I think you could solve this by simply storing the results in some temp-tables first and then insert the temp-tables into the actual tables.

Or you could use IsNumeric() to make things a lot easier IMHO.


-- clean up transactions (if any)

WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION
 

GO
 

-- create tables involved
 

DECLARE @evt_desc       varchar(100)

DECLARE @myDate         datetime

DECLARE @evt_code       varchar (50)

DECLARE @evt_int        int

DECLARE @evt_object     varchar (4000)

DECLARE @errMessage      nvarchar(4000)
 

IF OBJECT_ID('tempdb..#MyTempMaintTable') IS NULL

    BEGIN

        SELECT evt_code   = convert(varchar(50), 'hello'),

               evt_object = convert(varchar(4000), 'world')

          INTO #MyTempMaintTable

    END
 

IF OBJECT_ID('tbLocal_Table') IS NULL

    BEGIN 

         SELECT evt_code       =  @evt_code, 

                evt_object     = @evt_desc,

                Report         = @errMessage,

                AddedDate      = @myDate

          INTO tbLocal_Table

         WHERE 1 = 2

    END
 

BEGIN TRANSACTION 

    

    -- fill up source table
 

    -- not needed here as there already is a row present with Hello World
 

    -- do rest        

    DECLARE myCursor CURSOR LOCAL 

        FOR SELECT evt_code, evt_object 

              FROM #MyTempMaintTable
 

    OPEN myCursor

    IF @@ERROR <> 0 GOTO ErrorHandler
 

    FETCH NEXT FROM myCursor INTO @evt_code, @evt_object

    WHILE @@FETCH_STATUS = 0

        BEGIN

            SELECT @errMessage = '', @evt_int = NULL
 

            BEGIN TRY

                SELECT @evt_int = Convert(int, @evt_code );

                print 'passed error'

            END TRY 

            BEGIN CATCH

                print 'error caught'

                SELECT @errMessage = ERROR_MESSAGE()

            END CATCH;
 

            IF @errMessage <> ''

                BEGIN

                    INSERT INTO tbLocal_Table(

                             evt_code, 

                             evt_object,

                                Report,

                             AddedDate)

                            VALUES(

                             @evt_code,

                             @evt_object,

                             -- @evt_desc,

                             @errMessage,

                             @myDate)  ;
 

                END

            ELSE

                BEGIN

                    INSERT INTO tbLocal_Table(

                             evt_code, 

                             evt_object)

                            VALUES(

                             @evt_code,

                             @evt_object)

                END
 
 

            FETCH NEXT FROM myCursor INTO @evt_code, @evt_object
 

        END -- WHILE 

     

    CLOSE myCursor

    DEALLOCATE myCursor
 

COMMIT TRANSACTION
 

GOTO TheEnd

        

ErrorHandler:

    ROLLBACK TRANSACTION

    

TheEnd:

    PRINT 'all done'

Open in new window

0
 
LVL 11

Expert Comment

by:deroby
ID: 20397214
I agree that the IsNumeric() won't catch things like PK or FK violations, or overflows, or... so you have a point using TRY...CATCH here...
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:SDPierce
ID: 20397315
I've also tried raising an error prior to the first Insert statement in the TRY block so that processing never hits that piece of code in this scenario.  It jumps to the CATCH statement as expected.  But appears that instead of incrementing for the WHILE loop,  it stays on the tainted record and resumes processing after the RAISERROR line.
0
 

Author Comment

by:SDPierce
ID: 20399028
I tried it similar to the way you suggested.

And received this error. ...
Error is Conversion failed when converting the varchar value 'A240828' to data type int.
 
Before ELSE Error Insert --  **I printed this to ensure I was falling into the correct portion of the if statement**
Msg 3930, Level 16, State 1, Procedure spLocal_StoredProcedure, Line 246
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. -- This is inserting into the log table where there is no data type issue.
AFTER CATCH block before END -- WHILE 1=1 -- this is just to ensure I'm were I think I should be



BEGIN TRY

	SELECT CONVERT(INT, @evt_code)

	If @DebugFlag = 1

		BEGIN

			PRINT ' '

			PRINT 'passed error'

			PRINT ' '

		END

END TRY

BEGIN CATCH

	SELECT @errMessage = ERROR_MESSAGE() 

	If @DebugFlag = 1

		BEGIN

			PRINT ' '

			PRINT 'Error is ' + @errMessage

			PRINT ' '

		END

END CATCH
 

If @errMessage = '' 

	BEGIN

	PRINT @errMessage

	BEGIN TRY

		PRINT 'Before Try Insert'

		INSERT INTO tbLocal_Table(

			 evt_code, 

			 evt_object)

			VALUES(

			 @evt_code,

			 @evt_object)
 

		SET @errRecord = 1

		PRINT 'After Try Insert'

	END TRY

	BEGIN CATCH

		PRINT 'Enter CATCH Block'

	SELECT @errMessage = ERROR_MESSAGE()

	INSERT INTO tbLocal_TableTransLog(

 		 evt_code, 

		 evt_object,

		 Report,

		 AddedDate)

		VALUES(

 		 @evt_code,

		 @evt_object,

		 @errMessage,

		 @myDate)

	PRINT 'After Catch Insert'

	END CATCH;

END

ELSE

	BEGIN

		PRINT 'Before ELSE Error Insert' 

		INSERT INTO tbLocal_TableTransLog(

			 evt_code, 

			 evt_object,

			 Report,

			 AddedDate)

			VALUES(

			 @evt_code,

			 @evt_object,

			 @errMessage,

			 @myDate)

	END

				

	PRINT 'AFTER CATCH block before END -- WHILE 1=1'

	

FETCH NEXT FROM cursorMaintSched INTO &&&&&&

Open in new window

0
 

Author Comment

by:SDPierce
ID: 20399272
Without the 2nd Try Block, I receive the following error:
Error is Conversion failed when converting the varchar value 'A240828' to data type int.
 
Before ELSE Error Insert
Msg 3930, Level 16, State 1, Procedure spLocal_StoredProcedure, Line 203
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
AFTER CATCH block before END -- WHILE 1=1
BEFORE cursorMaintSched

0
 

Author Comment

by:SDPierce
ID: 20399544
checking xact_state and rolling back transactions that are 'doomed' gets the tainted record into the log table but fails on the next transaction (good transaction).

Msg 16943, Level 16, State 4, Procedure spLocal_StoredProcedure Line 203
Could not complete cursor operation because the table schema changed after the cursor was declared.

All I want to do is check this field to be the correct data type and not violate primary key constraints.  If hte record is good, I want it to go into one table.  If the record is bad, I want it to go into a different table.  Its looking like I'm going to have to put each individual record in a transaction of its own.  That is a LOT of overhead for a simple task such as this.  Are there any other alternatives?
0
 
LVL 11

Accepted Solution

by:
deroby earned 500 total points
ID: 20402001
Well, you could dump the TRY-CATCH approach and try this :


-- get @string_value
 

SELECT @int_value = NULL,

       @err_msg   = NULL
 

IF IsNumeric(@string_value) = 0

    BEGIN

        SELECT @err_msg = 'Value is not numeic'

    END

ELSE

    BEGIN

        SELECT @int_value = Convert(int, @string_value)

        IF EXISTS ( SELECT 1 

                      FROM <yourtable>

                     WHERE field = @int_value )

            BEGIN

                SELECT @err_msg = 'Value already exists'

            END

    END
 
 

IF @err_msg IS NULL

    BEGIN

        INSERT <the_good_table> (fields) VALUES (@int_value, etc...)

    END

ELSE

    BEGIN

        INSERT <the_bad_table> (fields) VALUES (@string_value, @err_msg, etc ..)

    END

Open in new window

0
 

Author Closing Comment

by:SDPierce
ID: 31412375
Thanks..  I would have liked to use the new Try Catch.  But this gets the job done and that's what its about.  
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now