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 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 (BULK INSERT) with only <lf> as line delimiter (not <cr><lf>)...

Hi,

This might be a noob question, but is there any way to specify just <lf> as a line delimiter, and not <cr><lf> (/n) as line delimiter in T-SQL call for BULK INSERT (or BCP utility). How please? TIA.

Usually any of these three combination would work for all kinds of files.

/n
/r
/r/n

You might try /r first, I think that's the one you need if /n doesnt' work.

/Kenneth