Help needed with BCP command

Question

Help needed with BCP command

wak_no1

SSCrazy

Points: 2400
More actions
October 2, 2013 at 6:39 am

#395586

Hello
I'm attempting to use the BCP command for the first time, I'm effectively trying to get the output of a SP copied to a text file, my syntax is as follows:
declare @sql varchar(8000)
SELECT @sql = 'bcp "exec [dbo].[sp_help_revlogin]" queryout "\\servername\d$\directoryname\filename.sql" -c -t -T -S servername'
EXEC xp_cmdshell @sql
The syntax runs without any errors and completes successfully but when I check the location where the output of the query should write to, I find nothing! It's really frustrating when no errors messages are printed, to help me diagnose the issue. Permissions are correct, as far as I can see.
Have I got something wrong in my syntax?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Atherton SSCrazy Points: 2663 More actions · Answer 1

Have you tried running the bcp command by itself from a command prompt? That should give you some error messages if it doesn't work.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

You need to alter the text "servername" in the BCP command to the name of your server where you want to execute the query. Also alter the text "\\servername\d$\directoryname\" into a valid and existing UNC path.

And as Jeff allready suggested: run the command from a command prompt to see if it works correctly.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

wak_no1 SSCrazy Points: 2400 More actions · Answer 3

Jeff Atherton (10/2/2013)
Have you tried running the bcp command by itself from a command prompt? That should give you some error messages if it doesn't work.

Yes, it runs in command. The output just contains a list of switches and their full names (-T Trusted connection, -i Inputfil etc.)

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Execute this command from SSMS:

exec xp_cmdshell N'whoami'

Make sure the account listed in the results has the correct permissions to execute the BCP executable and to write to the destination folder. Maybe you should also enter the full path to bcp.exe in the commandline.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

wak_no1 SSCrazy Points: 2400 More actions · Answer 5

HanShi (10/2/2013)
Execute this command from SSMS:
exec xp_cmdshell N'whoami'
Make sure the account listed in the results has the correct permissions to execute the BCP executable and to write to the destination folder. Maybe you should also enter the full path to bcp.exe in the commandline.

Yes, the permissions are correct as far as I can see to run the BCP.

Sorry, perhaps I wasn't clear in my original post, I've put 'servername' etc. in the syntax shown to protect my servers name. The actual syntax contains all the correct variables for my server.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 6

The stored procedure sp_help_revlogin does not return a result set so it will not work with BCP. You will get an error message like "BCP host-files must contain at least one column" from BCP. The sp_help_revlogin uses the PRINT statement to output the T-SQL for creating logins.

You could use OSQL like in the example below.

declare @sql varchar(8000)

SELECT @sql = 'osql -E -S servername -q"exec [dbo].[sp_help_revlogin]" -o \\servername\f$\outputfilename.sql -w120 -n'

EXEC xp_cmdshell @sql

wak_no1 SSCrazy Points: 2400 More actions · Answer 7

wak_no1

SSCrazy

Points: 2400

October 10, 2013 at 4:14 am

#1657431

Thank you for the reply, I will look into this.