SQLSTATE 54001

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

SQLSTATE 54001

I am running a bit of dynamic SQL in a stored procedure to cycle through tables in a database and get rowcounts.  After the execute immediate, I am capturing SQLSTATE.  For the most part, SQLSTATE is 00000, however in a few cases, it is 54001.  The query to get the count appears to finish OK... and i run it manually to verify that it finishes...  just not sure why it is throwing SQLSTATE of 54001.  I cannot seem to find the definition of this state online... below is a piece of the code so you can see where i am capturing SQLSTATE.

 

  FOR cur AS
      SELECT
         TRIM(DatabaseName) AS DBName,
         TRIM(TableName) AS TabName
      FROM dbc.Tablesv
      WHERE trim(DatabaseName) = 'DLAB_CIO'
      AND TableKind in ('T','V')
      AND (Tablename like 'DH_%' or Tablename like 'NSC_%' or Tablename like 'PBC_%')
   DO
     SET SqlTxt =
         'INSERT INTO DLAB_CIO.NSC_RowCounts ' ||
         'SELECT ' ||
         '''' || cur.DBName || '''' || ',' ||
         '''' || cur.TabName || '''' || ',' ||
         'CAST(COUNT(*) AS BIGINT)' ||  ',' ||
         'CURRENT_TIMESTAMP(2) ' ||
         'FROM ' || cur.DBName ||
         '.' || cur.TabName || ';';

     EXECUTE IMMEDIATE sqlTxt;
     if SQLSTATE <> '00000' then
       INSERT into DLAB_CIO.AAA_TB_LOG values ('DB: '||cur.DBName||'; '||'TBL: '||cur.TABName||'--'||SQLSTATE);
     end if;

 

1 REPLY 1
Teradata Employee

Re: SQLSTATE 54001

If you check the appendix in the SQL Reference: SQL Stored Procedures and Embedded SQL manual, you will find that SQLSTATE 54001 is mapped to a variety of database ErrorCode values that relate to exceeding some sort of internal database limit - and a few of those (such as 3705) are just warnings.

 

You could define an error handler and use GET DIAGNOSTICS EXCEPTION variable_name = MESSAGE_TEXT; to obtain more detail.