Solved

Troubleshooting TSQL Try Catch

Posted on 2007-12-03
11
2,281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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