bcp statement

• Richard Cranston

  Hall of Fame

  Points: 3680

  More actions

  April 19, 2004 at 10:21 am

  #107763

  I get an error procedure bcp not found when I run the following:

  bcp '"Select * From Salesperson_Dimension" queryout SalesPersonFile.txt'

   

  I get an error not authorized to xp_cmdshell when I run the following:

  DECLARE @bcp as varchar(8000)

  select @bcp = 'bcp "Select * From Salesperson_Dimension" queryout SalesPersonFile.txt'

  exec master.dbo.xp_cmdshell @bcp

   

  I am just trying to have bcp copy the Salesperson Table to a text file;

  eventually adding a where clause.

  Thanks for your help!

   

   

• Sopheap Suy

  SSCertifiable

  Points: 7893

  More actions

  April 19, 2004 at 10:28 am

  #503376

  bcp databasename.dbo.salesperson_dimension out salespersonfile.txt -T -SserverName -c -n

   

  sorry I have never try queryout option.

   

  mom

• Chad Littlewood

  Valued Member

  Points: 74

  More actions

  April 19, 2004 at 11:15 am

  #503380

  If you are part of the system administrator's group then you definitely have rights to execute xp_cmdshell, otherwise you may need to be given permissions to execute. If you are using a trusted connection then you should be able to use the script below. If you are not using a trusted connection then specify "-Uusername -Ppassword" in place of the -T.

   

  DECLARE @bcp as varchar(8000)

  select @bcp = 'bcp "Select * From northwind..Employees" queryout "C:\SalesPersonFile.txt" -T -Sservername'

  exec master..xp_cmdshell @bcp

   

  Chad

• Richard Cranston

  Hall of Fame

  Points: 3680

  More actions

  April 20, 2004 at 3:09 pm

  #503553

  Thanks for the help, one more question..

  Everytime this statement is run, the target text file is replaced, I was hoping to append to this target text file; is there an option for this?

  Thanks again for the help!

• David Burrows

  SSC Guru

  Points: 65141

  More actions

  April 21, 2004 at 7:13 am

  #503633

  Try this

  DECLARE @bcp as varchar(8000)

  select @bcp = 'CMD /C ' +

  'bcp "Select * From Salesperson_Dimension" queryout SalesPersonFiletemp.txt -S servername -T ' +

  ' && type SalesPersonFiletemp.txt >> SalesPersonFile.txt && del SalesPersonFiletemp.txt'

  exec master.dbo.xp_cmdshell @bcp

  Far away is close at hand in the images of elsewhere.
  Anon.