Tags: bulk, code, differences, error, executed, following, handling, insert, itags, org, procedure, programming, server, sql, stored, written

Differences in error handling between 2000 & 2005

On Programmer » SQL

3,237 words with 1 Comments; publish: Thu, 29 May 2008 01:11:00 GMT; (20078.13, « »)

The following code is executed within a stored procedure, written for

SQL Server 2000:

SET .sql.itags.org.BI = "BULK INSERT " + .sql.itags.org.TableName2 + " FROM '" + .sql.itags.org.path + "\"+

.sql.itags.org.TableName1 + "' WITH ( FORMATFILE = '"+.sql.itags.org.formatPath + "\"+ .sql.itags.org.TableName +

".fmt' , MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS,

ERRORFILE='"+.sql.itags.org.formatPath+"\"+.sql.itags.org.TableName + ".bad')"

EXEC (.sql.itags.org.BI)

SELECT .sql.itags.org.err = .sql.itags.org..sql.itags.org.error

IF .sql.itags.org.err <> 0

BEGIN

EXEC usp_BCP_GetErrorStringSP .sql.itags.org.err, .sql.itags.org.TableName2

SET .sql.itags.org.errMsg = 'ERROR: Fatal Error occurred. Initial data load has been

terminated abnormally.'

EXEC usp_BCP_WriteErrorLog .sql.itags.org.errMsg

GOTO ERROR

END

The code executes the BULK INSERT command and if an error occurs, an

error message is written to a log file and then the code goes to the

ERROR tag, to tidy up processing and quit.

This works fine in SQL Server 2000 but does not work for 2005.

I know I could replace the 'IF .sql.itags.org.err<>0' with a 'BEGIN TRY' and a 'BEGIN

CATCH' but I need the code contained within the stored procedure to be

able to execute on both SQL Server 2000 and 2005.

Could someone tell me what error handling code I could use to

successfully trap any BULK INSERT errors in both 2000 and 2005

environments?

All Comments

Leave a comment...

  • 1 Comments
    • EXEC usp_BCP_GetErrorStringSP I think you can't satisfy using error handler

      statement both 2000 and 2005.

      You can modify sql 2000 statement.

      In sql server 2005

      BEGIN TRY

      SET .sql.itags.org.BI = ..,

      EXEC (.sql.itags.org.BI)

      END TRY

      BEGIN CATCH

      EXEC usp_BCP_GetErrorStringSP ...,

      END CATCH

      "Nick"ë'ì?´ ì'ì?±í' ë?´ì?©:

      > The following code is executed within a stored procedure, written for

      > SQL Server 2000:

      > SET .sql.itags.org.BI = "BULK INSERT " + .sql.itags.org.TableName2 + " FROM '" + .sql.itags.org.path + "\"+

      > .sql.itags.org.TableName1 + "' WITH ( FORMATFILE = '"+.sql.itags.org.formatPath + "\"+ .sql.itags.org.TableName +

      > ".fmt' , MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS,

      > ERRORFILE='"+.sql.itags.org.formatPath+"\"+.sql.itags.org.TableName + ".bad')"

      > EXEC (.sql.itags.org.BI)

      > SELECT .sql.itags.org.err = .sql.itags.org..sql.itags.org.error

      > IF .sql.itags.org.err <> 0

      > BEGIN

      > EXEC usp_BCP_GetErrorStringSP .sql.itags.org.err, .sql.itags.org.TableName2

      > SET .sql.itags.org.errMsg = 'ERROR: Fatal Error occurred. Initial data load has been

      > terminated abnormally.'

      > EXEC usp_BCP_WriteErrorLog .sql.itags.org.errMsg

      > GOTO ERROR

      > END

      > The code executes the BULK INSERT command and if an error occurs, an

      > error message is written to a log file and then the code goes to the

      > ERROR tag, to tidy up processing and quit.

      > This works fine in SQL Server 2000 but does not work for 2005.

      > I know I could replace the 'IF .sql.itags.org.err<>0' with a 'BEGIN TRY' and a 'BEGIN

      > CATCH' but I need the code contained within the stored procedure to be

      > able to execute on both SQL Server 2000 and 2005.

      > Could someone tell me what error handling code I could use to

      > successfully trap any BULK INSERT errors in both 2000 and 2005

      > environments?

      >

      #1; Thu, 29 May 2008 01:12:00 GMT