BCP command fails with ERROR:"User name not provided…", when both SQL Server 2000 and SQL Server 2005 Tools are installed on machine
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 >> https://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.
Varun Dhawan
SE, Microsoft SQL Server
Reveiwed by
Nickson Dickson
TL,Microsoft SQL Server
bdf35qjzwn