Here’s the first ever issue I’ve encountered with SQL Server BCP Utility.

Issue:

Executing below BCP command on 2 different machines having SQL 2005 Instances, to transfer table data to a text file:

C:\>bcp "select * from credit.dbo.table_1" queryout c:\output.txt -C -S<SQL_Instance_name>

On Machine # A: Runs fine

On Machine # B: Fails with Error

"User name not provided, either use -U to provide the username or -T for a trust connection"

Questions here is why behavior is different on both machine ?

Cause:

Q1. Why this fails on machine B?

A1. SQL Server 2005, for a BCP.exe to communicate to SQL Instance, Login parameters [-T]or [-U] [-P] should be provided

Q2. Why this ran fine on machine A?

A2. On machine A, both SQL Server 2000 and SQL Server 2005 tools are installed. Hence by default BCP.EXE is used from

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\

instead of
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

IMP NOTE:

- SQL Server 2000 - BCP.exe has a different version and security design which permits BCP command to run without [-T] or [-U] parameter.

- However, In SQL Server 2005, which has enhanced security design requires that BCP.exe should be supplied with [-T] or [-U] parameter.

- For details, refer >>
http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

Resolution:

Follow below steps on machine # B:

Step1. Go to "My Computer" -> Properties -> Advanced -> Environment Variables
Step2. Go to System Variables -> Select "PATH" -> Press Edit
Step3. Remove value for SQL2000 Tools (C:\Program Files\Microsoft SQL Server\80\Tools\Binn)
Step4. Save and Exit
Step5. Re-cycle System and SQL server Service for changes to take affect

Once done, I get same behavior on from both the machines.

NOTE: This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it with you.

    Follow me on Twitter

    Varun Dhawan
    SE, Microsoft SQL Server

    Reveiwed by

    Nickson Dickson
    TL,Microsoft SQL Server

    bdf35qjzwn