March 17, 2021 at 4:57 pm
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)
March 18, 2021 at 2:42 pm
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/
March 18, 2021 at 3:29 pm
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
March 18, 2021 at 6:55 pm
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
March 18, 2021 at 7:45 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply