BCP - Export Query to Tab Delimited Text File

Question

BCP - Export Query to Tab Delimited Text File

AMCiti

SSC-Addicted

Points: 496
More actions
March 17, 2021 at 4:57 pm

#3859057
Dear Group:
I am using SSMS and trying to export a query to a text file using T-SQL (I know there are options in the tool itself to do this, but I need this code for a stored procedure).
I found examples but none of them seem to work and each gives a different error message:
```
EXEC xp_cmdshell 'bcp "SELECT * FROM [REPORT_ReviewStatus]" queryout "\\sd-eefx-5586\Report Outputs\REPORT_H75_ReviewStatus.txt" -T -c'
```
The above gives me an error about:
Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
So I try it using "Out"
```
EXEC xp_cmdshell 'bcp "SELECT * FROM [REPORT_ReviewStatus]" out "\\sd-eefx-5586\Report Outputs\REPORT_H75_ReviewStatus.txt" -T -c'
```
This gives me teh following:
Password:
CTLIB Message: - L1/O1/S1/N138/1/0:
: user api layer: external error: A data length of 255 exceeds the maximum length allowed for password data.
Setting connection properties failed.
What am I doing wrong? How can I use BCP for this, or is there something else to do that doesn't use SSIS (I will do this if necessary, but would like to avoid it if possible)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

There are a few things wrong with you query. You need to use queryout to get that to work, but remember, that xp_CmdShell is actually shelling out to a command prompt, so all context of server and database are lose. You'll need to specify the server name as a parameter as well as include the 3-part naming convention in the select statement. Lastly, it is not recommended to have xp_CmdShell turned on since you inherit all the permissions that the service account that starts SQL Server has. It's a dangerous thing. You could simple set this up as a batch file to run the bcp statement outside of SSMS if needed

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Mike01 wrote:

There are a few things wrong with you query. You need to use queryout to get that to work, but remember, that xp_CmdShell is actually shelling out to a command prompt, so all context of server and database are lose. You'll need to specify the server name as a parameter as well as include the 3-part naming convention in the select statement. Lastly, it is not recommended to have xp_CmdShell turned on since you inherit all the permissions that the service account that starts SQL Server has. It's a dangerous thing. You could simple set this up as a batch file to run the bcp statement outside of SSMS if needed

Having xp_CmdShell turned on is no more dangerous than having it turned off because only the people that can turn it on can use it unless you've made the terrible mistake of granting privs to use it to the wrong people.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

Just use built in wizard for EXPORT data (Right click on database in Object Explorer, choose Tasks, Export Data, and select objects that you would like to export).

Or use SSIS via Visual Studio and/or Data Tools (for earlier versions). SSIS is the best and most professional and flexible way to do it.

Likes to play Chess

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

SSIS is not the best neither the most professional way to do it.

Most times SSIS should not be used at all, and it can be the source of the biggest pain in your processes.

Powershell or even some small c# app would suffice for most things.