Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Troubleshooting TSQL Try Catch

Posted on 2007-12-03
11
Medium Priority
?
2,290 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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