0

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)

Community
  • 1
  • 1
Simon Lang
  • 533
  • 3
  • 18
  • I'm guessing this won't answer your question hence is a comment, I think it's a really good idea to catch more narrow exceptions than this, just catch the exceptions for the database query in your handling. Put any code that you need to run only if an exception is *not* thrown in the `else` clause for the exception handling – shuttle87 May 09 '18 at 09:52

2 Answers2

1

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;
Sameer
  • 349
  • 4
  • 12
  • Maybe I didn't specify properly enough. My goal is to handle the error in Python. This solution is not helping for that. I tried it. I was also tried `raiserror`within the `CATCH` part. But still the same. – Simon Lang May 09 '18 at 14:41
1

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Perfect, it works. Thank you very much. To your comment about the `WHERE` clause. I skipped some parts to make the question more readable. In reality the query is much longer and make more sense. I still need to fix the error with the duplicate entries though. – Simon Lang May 09 '18 at 14:42