0

I am running sqlcmd from a linux bash script. My target server is an Azure SQL. I execute an sql file with a lot of sql statements (around 2500). These are Insert/update/delete statements. I used the following command

sqlcmd -i $SQL_SCRIPT_FILE -m-1 -p 

Initially there were individual sql statements in the file. When executing that, a lot of statements were being missed out (I found that by checking the database, as some of the insert statements does not reflect in db).

Then I put all the statements in try catch and transaction. I am writing the exceptions in an ErrorLog table. Now when I execute the file, nothing happen. I do not see any error in ErrorLog table, the sqlcmd output also not display anything specific. The output is:

(0 rows affected)
Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total        23  avg   23.0 (43.5 xacts per sec.)
1

It sounds like your transaction is rolling back (including any rows written to your ErrorLog table). You haven't posted any TSQL source, so I'm not sure if you have the TRY/CATCH within a transaction or the transaction is within the TRY/CATCH.

I'd suggest following the guidance in this post - (TSQL Try / Catch within Transaction or vice versa?).

Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.

If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required, which might include writing rows to your ERRORLOG table.

Here's the code sample from the post above.

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    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
END CATCH
  • My scripts are exactly same. But nothing is being added to the error log table. The problem happen only when the script file is huge. I split out in multiple .sql files with try-catch and transaction and things are working well. – HSharma May 1 '18 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.