CODEWITHSUNDEEP
pythonsql-serverjaydebeapi
Simon Lang
Simon Lang

Reputation: 592

Error swallowed

Problem

I am connecting with JayDeBeApi to SQL Server 2017 and running a script like:

  1. SELECT ... INTO #a-temp-table
  2. DELETE FROM a-table
  3. INSERT INTO a-table SELECT FROM #a-temp-table
  4. DELETE #a-temp-table

During step 3 i get the following error:

Cannot insert duplicate key row in object 'dbo.a-table' with unique index 'UQ_a-table'. The duplicate key value is (11, 0001, 3751191, T70206CAT, 0000).

Instead of ~360k records, only ~180k get inserted. So step 3 aborts. The temp table however gets deleted. So step 4 completes.

I am able to fix the error. But with JayDeBeApi, I am not seeing the error. It seems like everything went fine from the Python point of view.

My goal is to capture those errors to handle them appropriately. Any idea how to achieve that?

What I've tried

My Python code looks like.

try:
    localCursor = dbConnection.cursor()
    x = localCursor.execute(query)
        
    logInfo("Run script %s... done" % (scriptNameAndPath), "run script", diagnosticLog)
except Exception as e:
    logError("Error running sql statement " + scriptNameAndPath + ". Skipping rest of row.", 
        "run script", e, diagnosticLog)
    myrow = skipRowAndLogRecord(startRowTime, cursor, recordLog)
    continue

x = localCursor.execute(myqrystm) completes successfully, so no exception is thrown. x is None and while inspecting localCursor, I see no sign of any error message(s)/code(s)

Upvotes: 0

Views: 594

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46193

Step 3 should be all-or-none so the a-table should be empty following the duplicate key error unless your actual code has a WHERE clause.

Regarding the undetected exception, add SET NOCOUNT ON as the first statement in the script. That will suppress DONE_IN_PROC messages that will interfere with script execution unless your code handles multiple result sets.

Upvotes: 1

Sameer
Sameer

Reputation: 381

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
SELECT  
ERROR_NUMBER() AS ErrorNumber  
,ERROR_SEVERITY() AS ErrorSeverity  
,ERROR_STATE() AS ErrorState  
,ERROR_PROCEDURE() AS ErrorProcedure  
,ERROR_LINE() AS ErrorLine  
,ERROR_MESSAGE() AS ErrorMessage;  
GO  

BEGIN TRY  
-- Generate divide-by-zero error.  
SELECT 1/0;  
END TRY  
BEGIN CATCH  
-- Execute error retrieval routine.  
EXECUTE usp_GetErrorInfo;  
END CATCH;

Upvotes: 1

Related Questions