Microsoft KB Archive/922660

= The bcp utility does not return the correct value of the %ERRORLEVEL% variable on some errors in SQL Server 2000 =

Article ID: 922660

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Workgroup Edition

-



Bug #: 471243 (SQL Server 8.0)



SYMPTOMS
When you use the bcp utility to copy data between an instance of Microsoft SQL Server 2000 and a data file in a user-specified format, you may notice that the bcp utility does not return the correct value of the %ERRORLEVEL% variable on some errors.

For example, you may insert rows that contain duplicate values in a specific column into a table that uses the UNIQUE constraint for that column. In this case, you receive an error message that resembles the following:

Starting copy...

SQLState = 23000, NativeError = 2627

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'IX_TableName'. Cannot insert duplicate key in object 'TableName'.

SQLState = 01000, NativeError = 3621

Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

BCP copy in failed

However, the value of the %ERRORLEVEL% variable in this operation is incorrectly returned as 0.



CAUSE
This problem occurs because the bcp utility does not set the value of the %ERRORLEVEL% variable for all the failures.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Step to reproduce the problem
  In a text editor, create a batch file that is named Out.bat, and then paste the following commands in the file: echo off bcp pubs..authors out c:\authors.dat -T -n -S \ echo %ErrorLevel% Note  and   represent the name of the computer and the instance name of SQL Server.   Create a batch file that is named In.bat, and then paste the following commands in the file: echo off bcp pubs..authors in c:\authors.dat -T -n -S \ echo %ErrorLevel%  At a command prompt, run Out.bat. At the command prompt, run In.bat.

The value of the %ERRORLEVEL% variable is set to 0.</li></ol>

Keywords: kbexpertiseadvanced kbtshoot kbprb KB922660

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.