Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help needed with BCP command Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 6:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:28 AM
Points: 68, Visits: 318
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?

Post #1500756
Posted Wednesday, October 2, 2013 7:12 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:10 AM
Points: 654, Visits: 208
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.
Post #1500764
Posted Wednesday, October 2, 2013 7:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 2,247, Visits: 2,708
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’! **
Post #1500780
Posted Wednesday, October 2, 2013 8:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:28 AM
Points: 68, Visits: 318
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.)

Post #1500803
Posted Wednesday, October 2, 2013 1:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 2,247, Visits: 2,708
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’! **
Post #1500910
Posted Thursday, October 3, 2013 5:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:28 AM
Points: 68, Visits: 318
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.
Post #1501119
Posted Wednesday, October 9, 2013 8:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 393, Visits: 10,815
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
Post #1503139
Posted Thursday, October 10, 2013 4:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:28 AM
Points: 68, Visits: 318
Thank you for the reply, I will look into this.
Post #1503446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse