Solved

Troubleshooting TSQL Try Catch

Posted on 2007-12-03
11
2,259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:SDPierce
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

772 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

10 Experts available now in Live!

Get 1:1 Help Now