Msg 0, Level 11 and Msg 3621, Level 0

• joop.terwijn

  SSC Veteran

  Points: 217

  More actions

  August 21, 2014 at 4:38 am

  #397234

  As many before me a stumbled into the famous error;

  Msg 0, Level 11, State 0, Line 0 A severe error on the current command. The results, if any, should be discarded.

  and his little brother;

  Msg 3621, Level 0, State 1, Server XXXX, Line 1 The statement has been terminated.

  I have been plowing throw this forum and internet and must say learn a lot :-)! This is the positive note of my problem (challenge). All my efforts did not resolve the issue and now I’m seeking your wisdom and advice how to proceed.

  My process: basically I use a master stored procedure next to multiple sub stored procedure to distribute millions of records from an import table to several different tables. In this process I have to check if the import table contains the right date, if necessary make new target tables . Remove indexes and constraints form tables due to be data appended and replace indexes and constraints etc. etc. all with the help of TSQL, cursors dynamic sql and all the fun stuff you cab imagine.

  I have build the process in chunks, let sag a check procedure if the import is already added before and if so remove the previous load. The removal of indexes is a procedure, the removal of constraints and so on.

  So the master procedure does a part, than jumps to a sub procedure and after this finished it jobs the process goes back to the master (simple nesting). This is repeating on and on, basic so far.

  Each procedure has it one try-catch with it one error reporting. Now the challenge, this process performed very well for months on a row. Suddenly, more or less random a run broke down, showing the famous error. Form sometime times an error it increased to the point it will break down almost all the time I try to run it :-(.

  The error occurs in as well as the master or sub procedures. Until now it occurs always around two tables, a ‘big’ import table and an administration table. By using the log I narrowed it down to a couple of TSQL moments. I’m exciting the procedure or from MS sql server management studio, or by Informatica Powercenter command task. Within the command task I used OSQL or SQLCMD. All methods result in errors ….

  - At a certain moment in the script I’m checking if a date in first row of the ‘big’ import table exists in the administration table (Msg 0) ( insert into statement ).

  - At a certain moment in the script I’m extracting a aggregation data set from the ‘big’ import table due to be insert into the administration table (Msg 0) (if a<> b begin….end ).

  - At a certain moment in the script I’m adding an index on the ‘big’ import table (Msg 3621) (if not exists create index).

  Running the scripts part on them self, no problem. In the procedure process … .

  What I have done up to now:

  - DBCC checktable / checkdb … OK, no problem.

  - Removed TSQL 'print' from master and sub procedures ( existed next to logging into log table ).

  The use of ‘print’ in a sub procedure, seems to be an issue … problem still occur.

  Tried replacing ‘print’ with raiserror (msg,0,0) … problem still occur.

  Disappointed I was at that point in time, removed the “print-raiserror” alt all! … problem still occur

  - Monitored transaction log … OK, no problem.

  - Monitored TEMPDB … OK, no problem

  - Removed and recreated the two suspect tables … problem still occur.

  - Although the errors are is displayed with Level 11 and level 0. it seems that the level 11 does not retch the catch block ( according to MSDN documentation it should ) . After removing “print and raiserror-print” there was no reporting in my log table. For this reason I reentered one raiserror-print [raiserror (msg,0,0)] in my catch blocks.[/i]

  So my MSsql server friends again, I’m seeking your wisdom and advice how to proceed.