Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Md
    Posts
    1

    Red face Unanswered: bcp, queryout and pipe delimiter

    When I execute the following string within a stored procedure:
    (The string has been modified to mask company info)

    bcp "Exec dbname.dbo.GetSalesCodesContracts" queryout d:\ftp\sfa\SalescodeContract-1.txt -m1 -c -t| -Uxxxxx -Pxxxxx -Sxxxxx

    I get the following error: The name specified is not recognized as an
    internal or external command, operable program or batch file.

    But if I change -t| to -t, (comma) it works ok and produces a comma delimited file.

    The pipe character seems to be the problem. The help file on delimiters says you can use the pipe.

    This is running under SQL Server 7.0.

    Any help would be appreciated.

    Richard

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Are you using bulk insert or the bcp command line utility?

    '|' is used for command line re-direction, most commonly to more, find and sort. It can also be used to re-direct the output of one application to another application. I suspect the command interpriter sees the '|' and tries to re-direct the result of your bcp command to the '-UXXXXX' application which as you have found out does not exist.

    Hope this helped.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Re: bcp, queryout and pipe delimiter

    Try using -t'|'

  4. #4
    Join Date
    Oct 2007
    Posts
    1
    Quote Originally Posted by andyabel
    Try using -t'|'
    This did not work for me. Instead, I finally discovered, -t^| did the trick. (The DOS shell interpreter uses the ^ character to escape the | and interpret the whole thing as a literal to be passed to bcp, rather than as part of the DOS commandline itself.)

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would do this with t-sql and xp_cmdshell
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2011
    Posts
    3
    --Generate BCP DOS command to export data pipe delimited

    DECLARE @BCPCommandString VARCHAR(8000)
    DECLARE @FilePath VARCHAR(1000)
    DECLARE @FileName VARCHAR(1000)
    DECLARE @SPName VARCHAR(1000)
    DECLARE @ServerName VARCHAR(1000)
    DECLARE @tSQL VARCHAR(1000)

    SET @ServerName = 'myServer'
    SET @tSQL = 'Select top 10 * from myTable'
    SET @FilePath = 'C:\temp\'
    SET @FileName = 'myFileName.tmp'

    SET @BCPCommandString = 'bcp "' + @tSQL + '" queryout ';

    SET @BCPCommandString = @BCPCommandString + @FilePath + @FileName;

    SET @BCPCommandString = @BCPCommandString + ' -S ' + @ServerName;

    SET @BCPCommandString = @BCPCommandString + ' -T -c -t^| -U username -P password';

    SELECT @BCPCommandString;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •