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.

RichardAre 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.|||Try using -t'|'|||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.)|||I would do this with t-sql and xp_cmdshellsql

BCP output with header and trailer

I'm copying data out to a file with pipe delimiters. I would like to
add a header and trailer.

Is this possible? If so, please help me with the steps.

Also, is it possible to append to a text file while doing the bcp? If
so, how?"sbh" <stephanie.herbert@.tdh.state.tx.us> wrote in message
news:1d68006.0403040850.23a28ccb@.posting.google.co m...
> I'm copying data out to a file with pipe delimiters. I would like to
> add a header and trailer.
> Is this possible? If so, please help me with the steps.
> Also, is it possible to append to a text file while doing the bcp? If
> so, how?

Not directly, but you might be able to use -queryout or osql.exe -o to
execute a query or stored procedure which returns your header and trailer
information as well as the data. Since you don't say what you want in your
header/trailer, it's hard to be more precise. As for appending, I believe
that both bcp and osql overwrite any existing file, but a simple script in
Perl, VBScript etc. would be a more flexible solution.

Simonsql

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David Lozzi
Hi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John
|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John
|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We nee
d
> to use another delimiter and I'm wondering if we can use multiple characte
rs
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>> Howdy,
>> I have a fmt file used for importing data from a IBM database into SQL.
>> Currently we're using the pipe | as a delimiter and it works great. We
>> need
>> to use another delimiter and I'm wondering if we can use multiple
>> characters
>> like %$. If so, is it just as easy as changing the delimiter in the fmt
>> file?
>> Thanks,
>> David Lozzi
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> > Hi David
> >
> > "David Lozzi" wrote:
> >
> >> Howdy,
> >>
> >> I have a fmt file used for importing data from a IBM database into SQL.
> >> Currently we're using the pipe | as a delimiter and it works great. We
> >> need
> >> to use another delimiter and I'm wondering if we can use multiple
> >> characters
> >> like %$. If so, is it just as easy as changing the delimiter in the fmt
> >> file?
> >>
> >> Thanks,
> >>
> >> David Lozzi
> >>
> > The field terminator can be a string of up to 10 printable characters. See
> > http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> > http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> > file is a text file so it can be edited with any text editor.
> >
> > John
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP Converting character ° to ¦

A BCP process is converting a degree character (°) to a pipe character (¦).
This is the only conversion problem that has been identified. Is there a
method to correct this?Try adding -C RAW' to your BCP command. This will prevent code page
conversion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KingRo" <KingRo@.discussions.microsoft.com> wrote in message
news:A0D44DC3-915F-45B8-9BD1-EF16DCA4865D@.microsoft.com...
>A BCP process is converting a degree character (°) to a pipe character (¦).
> This is the only conversion problem that has been identified. Is there a
> method to correct this?