Post reply

BCP utility With ~

  • November 2, 2015 at 6:22 am

    #399260

    Hi,

    I'm Currently trying to export data from a table in Tilda format using the following BCP

    EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [db].dbo.

    " queryout "C:\bcp\Customers.csv" -c -b 10000 -t, -S 10.20.8.149 -U username -P password'

    But I cannot seem to get it to work , i have tried "-t ~" but it still exports with commas delimiters.

    Any help on where i am going wrong?

  • November 2, 2015 at 6:25 am

    #1837039

    Try -t^~

    Guessing the ~ is a command line special character like | and it don't like it, the ^ treats the ~ as an escaped character.

  • November 2, 2015 at 6:36 am

    #1837044

    Did the trick thank you

  • November 2, 2015 at 6:37 am

    #1837045

    Second Question ,

    would you know how to compress this file to a rar file if i was to put this in a sp ?

  • November 2, 2015 at 6:42 am

    #1837049

    Can you not call WinRAR from the command line with another xp_cmdshell call to the rar exe and pass in the file and archive into the string?

  • November 2, 2015 at 7:04 am

    #1837067

    yes you can EXEC master..xp_cmdshell '"C:\Program Files\WinRAR\unrar" x e:\rartest\*.rar'

    but i dont know how to execute my code within it . at the moment the code above is jus putting a .sql file in rar

  • November 2, 2015 at 7:09 am

    #1837070

    C:\Program Files\WinRAR\rar.exe a -r C:\bcp\customers.rar C:\bcp\Customers.csv

    http://comptb.cects.com/using-the-winrar-command-line-tools-in-windows/

  • November 2, 2015 at 7:13 am

    #1837072

    Thank you , Appreciated the help

  • November 2, 2015 at 8:50 am

    #1837119

    When we do this we use a BATCH file (I'm an old dog ... presumably PowerShell would be the modern equivalent).

    My BATCH file would do BCP and then RAR/whatever. It would check for ERRORLEVEL from BCP and would LOG the outputs to a text file so if it went wrong I could see, more easily, what had happened.

    My Batch file would take parameters for CSV filename, LOG filename, etc. so from SQL we could just do

    EXEC master..xp_cmdshell MyBatchFile.BAT MyOutpufile.CSV c:\LogFile\MyLogFile.LOG ...

    But more often the job would need to be scheduled at a particular time, in which case we just schedule the BATCH file (i.e. using Windows Scheduler), rather than a SQL Agent "SQL job"

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

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