BCP problem

Hi,

I have downloaded data from a sybase table uing BCP without any row terminator and column terminater specified. Before "BCP in" I have used ";" for column terminator and "/n" and row terminator. If I will load data one row by row it is working fine. But if I will load more than one row, 1st row goes fine.. second row 1st column gets truncated. Please help..

Thanks,it would be easier if you should us the syntax of the commands...and this is a sql server forum btw|||give native mode a try (-n). I believe that is the default of Sybase if you do not specify row and field terminators.

bcp keepnulls (-k) option

Hello,

Do you know in what version of bcp the option keepnulls (-k) was introduced?

I use bcp version 12.5.1 on sybase 12.5.

I know that this is not a Sybase forum and that this option might not be available at all in Sybase bcp but your answer might stil help me figure out my problem.

Thank you

I believe it was intruduced in sql 7.0...

bcp errors with queryout

I am having problems running bcp with queryout. I believe some of this stems from the fact that I also have the Sybase tools installed on the database servers and it in turn added its binn directory for the Sybase bcp.exe to the environment variables. Seeing this then, I would like to resolve this issue with a solution that does not rely on the order of environment variables.

Here is what is happening:

1) If I just try to run bcp on my server I get...

exec master.dbo.xp_cmdshell 'bcp ?'
/*
output -------------------
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/

2) ...so, I add the full path to the location of the bcp program and this command works:

exec master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" ?'
/*
output --------------------------usage: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
*/

3) I then attempt a bcp out of a Northwin table... and this works as well

DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" Northwind.dbo.Categories out C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output
------
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 31
NULL
*/

4) I then attempt a queryout and it fails, indicating that it no longer understand the long filename

DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output --------------------
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/been doing some more testing on my own local SQL Server. It seems that even if the environment variables are all fine, if I fully qualify the bcp exe and include a query (with double-quotes) it fails. So, what I am wondering is, can one not fully qualify the bcp.exe and do a query at the same time?:

--the below works
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = 'bcp "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT

--...while this does NOT work!
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output ---
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/|||try to

SELECT @.cmdline = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT|||sorry, really not works

BCP error message.

First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
this newsgroup so forgive me if I should have posted this question else where.
I'm trying to use bcp to output data from a table to a file but keep getting
the error, "An error occurred while trying to process the command line."
This is what I'm trying to execute throught he Command Prompt,
bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
Downloads\output.txt" -T -c -Sqisdevsql1
I'm running this from our development server, which is not where SQL Server
lives, we have a dedicated SQL Server box, qisdevsql1.
What am I doing wrong?This works on my machine. Can you run bcp -v from a command window and see
what you get?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaRock" <DaRock@.discussions.microsoft.com> wrote in message
news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> this newsgroup so forgive me if I should have posted this question else
> where.
> I'm trying to use bcp to output data from a table to a file but keep
> getting
> the error, "An error occurred while trying to process the command line."
> This is what I'm trying to execute throught he Command Prompt,
> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
> I'm running this from our development server, which is not where SQL
> Server
> lives, we have a dedicated SQL Server box, qisdevsql1.
> What am I doing wrong?
>|||I get, BCP - Bulk Copy Program, some registration info and then Version
9.00.1399.06.
Troy
"Hilary Cotter" wrote:
> This works on my machine. Can you run bcp -v from a command window and see
> what you get?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "DaRock" <DaRock@.discussions.microsoft.com> wrote in message
> news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> > First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> > this newsgroup so forgive me if I should have posted this question else
> > where.
> >
> > I'm trying to use bcp to output data from a table to a file but keep
> > getting
> > the error, "An error occurred while trying to process the command line."
> >
> > This is what I'm trying to execute throught he Command Prompt,
> > bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> > Downloads\output.txt" -T -c -Sqisdevsql1
> >
> > I'm running this from our development server, which is not where SQL
> > Server
> > lives, we have a dedicated SQL Server box, qisdevsql1.
> >
> > What am I doing wrong?
> >
> >
>
>|||> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
Since your database name does't confirm to the rules for identifiers, you
need to enclose the name in square brackets:
bcp [450_gilroy].dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
Downloads\output.txt" -T -c -Sqisdevsql1
Hope this helps.
Dan Guzman
SQL Server MVP
"DaRock" <DaRock@.discussions.microsoft.com> wrote in message
news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> this newsgroup so forgive me if I should have posted this question else
> where.
> I'm trying to use bcp to output data from a table to a file but keep
> getting
> the error, "An error occurred while trying to process the command line."
> This is what I'm trying to execute throught he Command Prompt,
> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
> I'm running this from our development server, which is not where SQL
> Server
> lives, we have a dedicated SQL Server box, qisdevsql1.
> What am I doing wrong?
>|||Yes that was it. Thanks!
"Dan Guzman" wrote:
> > bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> > Downloads\output.txt" -T -c -Sqisdevsql1
> Since your database name does't confirm to the rules for identifiers, you
> need to enclose the name in square brackets:
> bcp [450_gilroy].dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaRock" <DaRock@.discussions.microsoft.com> wrote in message
> news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> > First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> > this newsgroup so forgive me if I should have posted this question else
> > where.
> >
> > I'm trying to use bcp to output data from a table to a file but keep
> > getting
> > the error, "An error occurred while trying to process the command line."
> >
> > This is what I'm trying to execute throught he Command Prompt,
> > bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> > Downloads\output.txt" -T -c -Sqisdevsql1
> >
> > I'm running this from our development server, which is not where SQL
> > Server
> > lives, we have a dedicated SQL Server box, qisdevsql1.
> >
> > What am I doing wrong?
> >
> >
>
>

BCP error message.

First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
this newsgroup so forgive me if I should have posted this question else wher
e.
I'm trying to use bcp to output data from a table to a file but keep getting
the error, "An error occurred while trying to process the command line."
This is what I'm trying to execute throught he Command Prompt,
bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
Downloads\output.txt" -T -c -Sqisdevsql1
I'm running this from our development server, which is not where SQL Server
lives, we have a dedicated SQL Server box, qisdevsql1.
What am I doing wrong?This works on my machine. Can you run bcp -v from a command window and see
what you get?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaRock" <DaRock@.discussions.microsoft.com> wrote in message
news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> this newsgroup so forgive me if I should have posted this question else
> where.
> I'm trying to use bcp to output data from a table to a file but keep
> getting
> the error, "An error occurred while trying to process the command line."
> This is what I'm trying to execute throught he Command Prompt,
> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
> I'm running this from our development server, which is not where SQL
> Server
> lives, we have a dedicated SQL Server box, qisdevsql1.
> What am I doing wrong?
>|||I get, BCP - Bulk Copy Program, some registration info and then Version
9.00.1399.06.
Troy
"Hilary Cotter" wrote:

> This works on my machine. Can you run bcp -v from a command window and see
> what you get?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "DaRock" <DaRock@.discussions.microsoft.com> wrote in message
> news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
>
>|||> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
Since your database name does't confirm to the rules for identifiers, you
need to enclose the name in square brackets:
bcp [450_gilroy].dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
Downloads\output.txt" -T -c -Sqisdevsql1
Hope this helps.
Dan Guzman
SQL Server MVP
"DaRock" <DaRock@.discussions.microsoft.com> wrote in message
news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
> First of all I'm brand new to SQL Server (fresh off 7 years of Sybase) and
> this newsgroup so forgive me if I should have posted this question else
> where.
> I'm trying to use bcp to output data from a table to a file but keep
> getting
> the error, "An error occurred while trying to process the command line."
> This is what I'm trying to execute throught he Command Prompt,
> bcp 450_gilroy.dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
> I'm running this from our development server, which is not where SQL
> Server
> lives, we have a dedicated SQL Server box, qisdevsql1.
> What am I doing wrong?
>|||Yes that was it. Thanks!
"Dan Guzman" wrote:

> Since your database name does't confirm to the rules for identifiers, you
> need to enclose the name in square brackets:
> bcp [450_gilroy].dbo.temp_rim out "C:\Program Files\AutoMate\AutoMate
> Downloads\output.txt" -T -c -Sqisdevsql1
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaRock" <DaRock@.discussions.microsoft.com> wrote in message
> news:81457C76-EC2B-468A-B8CF-290B396076BB@.microsoft.com...
>
>