BCP – password prompt?

December 24, 2012

BCP is a very useful tool.

How and why we use it is explained in the following link and in BOL: http://msdn.microsoft.com/en-us/library/ms162802.aspx

I wanted to write about a small little issue that you may come across which can drive you up the wall. Below is a simple example, where I’ve launched the command prompt and run the following command which exports the contents of table testable to a csv file test.csv

bcp.exe TEST..TestTable out “d:\temp\test.csv” -c -t, -T -Slocalhost

BCP

Notice the password: prompt? Even though I’ve specified –T which tells the bcp utility to run using trusted authentication.

“- T specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.”– Microsoft

What password? I’m using the service account which is a local windows administrator and a system administrator in SQL Server and I’m exporting to a local folder – so what f@ck3n! Password! Turns out that even though it’s prompting for a password, the real problem is actually the path that you are executing bcp from, in my case: c:\documents and settings\serviceaccount\

Press Ctrl + C to abort the above BCP command:

BCP CTRL C

Type cd\ {enter}

and then change directory to the physical location of the bcp executable

cd program files\microsoft sql server\90\tools\binn

and now run the exact same command

bcp.exe TEST..TestTable out “d:\temp\test.csv” -c -t, -T –Slocalhost

BCP Success

Success!

It’s the kind of thing that can catch you out and waste more time that it really needs to. BCP is also a utility that you may not use very often so unexpected stumbling blocks like this can really stump you for a bit.

If you’re using the command prompt you may figure out the problem fairly quickly. However, in my case I was writing a stored procedure, using the code below, and when running the query it never completed or failed. It seemed to run forever. Sp_who2 showed no signs of blocking.

All along it was waiting for a password that would never be entered.

BCP T-SQL

Merry Christmas and thanks for reading!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: