txt file Pipe

  • Hello,

    I need to put data from table to txt file as pipe on certain drive can someone assist me? Thank you

  • i used this code:

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM dbo.CustomerInformation " queryout C:\Data\Objects.txt -t"|" -c -T ' and it is not working

  • Does SQL Server have permissions to write to that folder?

    Are you getting any error messages?

    Are you executing in SSMS? If so, does the login have permissions to write to that folder?

    In a SQL Agent job? If SQL Agent job, does the SQL agent login have permissions to write to that folder?

  • juliava wrote:

    i used this code:

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM dbo.CustomerInformation " queryout C:\Data\Objects.txt -t"|" -c -T ' and it is not working

    What is your definition of "Not Working" here?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SQLState = S1000, NativeError = 0

    Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

    NULL

  • I change drive and code  and it is working now

     

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM table1 " queryout G:\Backup\Test.txt -t"|" -c -T '

     

    I need to know how to add headers please

    • This reply was modified 1 year ago by  juliava.
  • juliava wrote:

    I need to know how to add headers please

    If this is an Agent Job step then Powershell is a lot less hassle. Play with something like:

    $SQLparams = @{
    'ServerInstance' = '.';
    'Database' = 'YourDB';
    'ErrorAction' = 'Stop';
    'Query' = 'SELECT * FROM dbo.table1' }

    $MyPath = "G:\Backup\Test.txt"

    Invoke-Sqlcmd @SQLparams |
    ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
    % {$_ -replace '"',''} |
    Out-File -Encoding ascii $MyPath
  • My first replay seemed to time out so I posted again.

  • this is not working

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

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