BCP Utility - Skip rows..

Hello All,

Does the BCP utility enable you to selectively import rows from a flat
file to a table ?

For example:

The first column in my flat file contains a record type - 1, 2..7
I only need to import types 1, 2, & 3

Can this be specified in the .fmt file ?

Thanks in advance
hharryhharry (paulquigley@.nyc.com) writes:
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?

It depends on the format of the records, but I would say that it is
highly unlikely. In fact, if the record types are heterogeneous, you may
not be able to write a format file to describe the file at all.

If they record types are homegeneous to fit into one format, you can bulk
load into a staging table, and the move on to the target table from there
with the interesting rows.

Also, if the records appears evenly in strict order: 12345671234567...
you can handle all as one big record. But I would not really expect
your file be like that...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There are 7 different record types within the file - all different
lengths.
I am going to parse the file via a dotnet exe.
Thanks for the assistance

Erland Sommarskog wrote:
> hharry (paulquigley@.nyc.com) writes:
> > Does the BCP utility enable you to selectively import rows from a
flat
> > file to a table ?
> > For example:
> > The first column in my flat file contains a record type - 1, 2..7
> > I only need to import types 1, 2, & 3
> > Can this be specified in the .fmt file ?
> It depends on the format of the records, but I would say that it is
> highly unlikely. In fact, if the record types are heterogeneous, you
may
> not be able to write a format file to describe the file at all.
> If they record types are homegeneous to fit into one format, you can
bulk
> load into a staging table, and the move on to the target table from
there
> with the interesting rows.
> Also, if the records appears evenly in strict order:
12345671234567...
> you can handle all as one big record. But I would not really expect
> your file be like that...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||One of our people created a table that was, essentially:

record_type int
rest_of_record varchar(long_enough)

loaded the table with a bcp format that was just a field for the record type
and a varchar field for the rest.

Then, he created a SP that used a cursor to walk through the table and T-SQL
decoded the records into various fields and did the inserts into destination
tables.

I found out what they'd done after we upgraded from SQL Server 6.5 and the
process stopped working because the rows no longer came out in
First-In/First-Out order. The SP was almost entirely unmaintainable,
anyway.

We wrote something maintainable and understandable in VB6 (with record
types, liberal use of constant declarations) in a few hours. Much better. *

I wonder if you could use DTS to accomplish what you want? If you haven't
already, why not drop the question into microsoft.public.sqlserver.dts?

* If I remember correctly, the emergency workaround was to add an identity
column to the work table and have the cursor pull rows in sorted order on
the identity column.

"hharry" <paulquigley@.nyc.com> wrote in message
news:1109366729.311250.16040@.l41g2000cwc.googlegro ups.com...
> Hello All,
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?
>
> Thanks in advance
> hharry|||dh (dh@.news.net) writes:
> One of our people created a table that was, essentially:
> record_type int
> rest_of_record varchar(long_enough)
> loaded the table with a bcp format that was just a field for the record
> type and a varchar field for the rest.
> Then, he created a SP that used a cursor to walk through the table and
> T-SQL decoded the records into various fields and did the inserts into
> destination tables.

Yeah, that's a normal approach to do it. You write a program that reads
the file. But T-SQL is a poor choice for the task, since it's not good
on string handling. And while you can add an IDENTITY, you cannot be
really sure that it works anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP utility

In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \\indiadb\ftproot\CLIENT_BRANCH_UPLOAD\branchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r \n -c -t "," -q'It doesn't...did you look at BOL?

You can however "fake it out"

It would require a view or using QUERYOUT...

Something like

SELECT 'HEADING1','HEADING2',ect
UNION ALL
SELECT Col1, Col2, ect
FROM yourTable

Just make sure you CONVERT everyhting to varchar...

bcp utf8 file

I'm outputting a file from a mySQL server that contains 1 column that
has input it many languages. The base definition for the column is
UTF8.

I output the data into a flat file and bcp it to a sql2000 server, and
the multi-byte data does not survive the translation.

Any ideas on how to input a UTF8 flat file into the server using BCP?

I've tried the -n -N -w options -- no luck.

thanks,

edAssuming that your destination column is nvarchar or nchar, then -w
should be the correct option; -n or -N are specific to MSSQL, so
they're unlikely to work. I've never used bcp to import a file like
that myself, so I can't really say for sure what the solution is, but
under "Copying Data Between Different Collations" in Books Online, it
suggests using a bcp format file with specific collations. You might
also try using DTS to import the data, as it's generally a lot smarter
than bcp, however again I've never tried importing a UTF8 file with it.

Simon|||(geekboyed@.gmail.com) writes:
> I'm outputting a file from a mySQL server that contains 1 column that
> has input it many languages. The base definition for the column is
> UTF8.
> I output the data into a flat file and bcp it to a sql2000 server, and
> the multi-byte data does not survive the translation.
> Any ideas on how to input a UTF8 flat file into the server using BCP?

Since you cannot store UTF-8 data in SQL 2000 in any civilized way,
you need to first convert the file to UCS-2. (Or UTF_16 if you prefer.
I hope you are not using surrogates.)

A simple way to do this is to open the file in Notepad, and then change
encoding when you do Save As.

I'm a little uncertain how any byte-order mark would affect BCP.
Notepad is likely to add one, but I don't know if BCP is smart
enough to ignore it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP using ODBC - problem with unique identifier

Hi guys
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version:)
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
> I'm having a nasty problem with bulk copying into a table that has
> unique identifier column. I'm coding on C++, using ODBC driver.
> I'm coping from a file containing UID description like this:
> {43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
> (I also tried a non-bracket version)
> and in the DB table I get:
> 4233347B-4235-4433-452D-353238302D34
> which seems random at first sight, but it is:
> [B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
> and my question is: what the hell?
> my code look like this:
> if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
> ret = -1;
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
> SQLUNIQUEID, colNo) == FAIL){
> ret = -1;
> }
> (I also tried a VARLEN version:)
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
> (LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
> ret = -1;
> }
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> First you need to decide in which format is the UID? It if is in text,
> you should specify SQLVARCHAR for the data type. Only if you have the
> UID as binary, you should specify SQLUNIQUEID.
> Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
> It depends on what's in delimiter, but since a GUIO is always 36
> characters (without braces), you could just as well specify 36 for the
> length.
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
> 1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
> SQL Server Driver]Invalid character value for cast specification". What
> do you meas format of the UID? You mean in the input file? in the input
> file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
> 2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
> result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

BCP using ODBC - problem with unique identifier

Hi guys
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read bi
nary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,p
Value,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
> I'm having a nasty problem with bulk copying into a table that has
> unique identifier column. I'm coding on C++, using ODBC driver.
> I'm coping from a file containing UID description like this:
> {43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
> (I also tried a non-bracket version)
> and in the DB table I get:
> 4233347B-4235-4433-452D-353238302D34
> which seems random at first sight, but it is:
> [B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read
binary as hex.
> and my question is: what the hell?
> my code look like this:
> if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
> ret = -1;
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
> SQLUNIQUEID, colNo) == FAIL){
> ret = -1;
> }
> (I also tried a VARLEN version
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
> (LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
> ret = -1;
> }
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
> First you need to decide in which format is the UID? It if is in text,
> you should specify SQLVARCHAR for the data type. Only if you have the
> UID as binary, you should specify SQLUNIQUEID.
> Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
> It depends on what's in delimiter, but since a GUIO is always 36
> characters (without braces), you could just as well specify 36 for the
> length.
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][O
DBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
> 1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][
;ODBC
> SQL Server Driver]Invalid character value for cast specification". What
> do you meas format of the UID? You mean in the input file? in the input
> file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.

> 2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
> result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP using ODBC - problem with unique identifier

Hi guys

I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.

I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)

and in the DB table I get:
4233347B-4235-4433-452D-353238302D34

which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.

and my question is: what the hell?

my code look like this:

if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;

if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}

(I also tried a VARLEN version:)

if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}

and then stuff like sendrow ans save:

if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;

I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:

INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);

So like, PLEASE help me on this. I need to get this working by last
monday :]

Thanx, M.(mpietrzyk@.autograf.pl) writes:

Quote:

Originally Posted by

I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
>
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
>
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
>
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
>
and my question is: what the hell?
>
my code look like this:
>
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
>
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
>
(I also tried a VARLEN version:)
>
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}

First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.

Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
>
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
>
>

Thanx Erland,

I tried the approaches you mentioned:

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error

Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:

Quote:

Originally Posted by

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.

The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.

Quote:

Originally Posted by

2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.

Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tha_mihau (mpietrzyk@.autograf.pl) writes:

Quote:

Originally Posted by

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.

>
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
>

Quote:

Originally Posted by

2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.

>
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.

Thx, Erland!

Running
bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 36, (LPCBYTE)NULL, 0,
SQLVARCHAR, colNo)
on a file with GUIDs with no braces did the trick.
Works like a charm.

Thanks again, M.sql

bcp queryout strange behaviour

I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with '\r\n'.

I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.

This is my bcp command:

bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:\BeBackDetail.dat" -f "D:\BeBackDetail.fmt" -e "D:\BeBackDetailErrors.dat" -U user -P pass -S server

And format file:

8.0
4
1 SQLCHAR 0 2 "" 1 DIVCODE ""
2 SQLCHAR 0 3 "" 2 COMMCODE ""
3 SQLCHAR 0 10 "" 3 CONTACTID ""
4 SQLCHAR 0 512 "\r\n" 4 NOTES ""

Has anyone seen this?

Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.

Thanks for any ideas -BTW the site trimmed my ' ' strings - those are supposed to appear as two spaces. So i.e. "...bcp is replacing ' ' with '\r\n'." should contain 2 spaces within the first string.

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 out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:
> Hi,
> I need make a scheduling job for bcp export data from databases' table to a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chensql

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:

> Hi,
> I need make a scheduling job for bcp export data from databases' table to
a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chen

bcp out file including all the columns names

How to bcp out file to also include all the table column names?

Thank you!

What is the purpose of you wanting to have the table column names inside the bcp file?

|||

Users ask for it. It was not my choice.

|||If you need to know the column names that exist for a given BCP file, then you can create a format file during your bcp out. For more information about format files, please see topic "Creating a Format File" in SQL Server 2005 Books Online.sql

bcp out empty column

I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.

use monkey
go

create table tab1 (
myColumn varchar(10) null
)
go

insert into tab1 (myColumn) values ('')
go

exec master..xp_cmdshell 'bcp monkey..tab1 out D:\data.csv -c -T'
go

drop table tab1
go

I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?try changing the extn of file to '.txt' and check it, its working fine for me using the command prompt as well as query analyser.

try as
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.csv -c -T'
or
exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.txt -c -T'

cheers,
pavan.|||Changing the file's extension didn't do it for me, perhaps it's an OS or SQLServer configuration issue? It's a Windows 2000 Server running SQL2000. I'm not sure where to start.

I don't have the issue with the analyzer either, only when exporting to a file.|||Mine is windows 2000 professional runnung sqlserver 2000.
I dont think thats the problem.

Try the same in others machine,
i had a similar problem for exports and Imports of oracle,they never worked in my colleagues PC and worked fine from my pc..

BCP out data to text files using SQL 2000

I am exporting data from a table with one column (varchar
255), to a flat text file using BCP out utility. If the
table contains around a 1000 or more rows sometimes the
records in the text file is not generated is the same
order as it was in the table.
I know if I add a clustered index column to the table and
use a select query I probably could get the results I
need. But I would like to keep that as a last resort, as
it would involve many stored procedure changes.
Note: not as much of a problem with SQL 6.5. After
upgrading to SQL 2000 it has become much more of an issue.
Thanks
Adding a clustered index does not guarantee data order from a select, bcp
etc. You must specify an ORDER BY clause to read the data in the proper
order. With that said you may want to ensure you have MAXDOP set to 1 if
you have a multiprocessor system when importing or exporting to get the best
chance of it going into the file in that order. Ideally you should have
some column that determines the proper order if that is important.
Andrew J. Kelly SQL MVP
"BC" <anonymous@.discussions.microsoft.com> wrote in message
news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
> I am exporting data from a table with one column (varchar
> 255), to a flat text file using BCP out utility. If the
> table contains around a 1000 or more rows sometimes the
> records in the text file is not generated is the same
> order as it was in the table.
> I know if I add a clustered index column to the table and
> use a select query I probably could get the results I
> need. But I would like to keep that as a last resort, as
> it would involve many stored procedure changes.
> Note: not as much of a problem with SQL 6.5. After
> upgrading to SQL 2000 it has become much more of an issue.
> Thanks
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schrieb im Newsbeitrag
news:OV6JPbbeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Adding a clustered index does not guarantee data order from a select,
bcp
> etc. You must specify an ORDER BY clause to read the data in the proper
> order. With that said you may want to ensure you have MAXDOP set to 1
if
> you have a multiprocessor system when importing or exporting to get the
best
> chance of it going into the file in that order. Ideally you should have
> some column that determines the proper order if that is important.
Additional remark: OP should never rely on the order of data in a table.
AFAIK there is no guarantee that data is drawn from a table with a certain
order other than specifying the desired order. Even if there is a
clustered index, you should explicitely specify the order. After all,
relational databases manage record sets rather lists of records.
Kind regards
robert

> --
> Andrew J. Kelly SQL MVP
>
> "BC" <anonymous@.discussions.microsoft.com> wrote in message
> news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
>

BCP out and headers

I'm no dummy when it comes to this stuff but for the life of me I can't figure out how (if it is possible) to get column headers to go when exporting data. Here is the syntax I'm using:

BCP "select ATSCLAIMNUMBER, ALTERNATECLAIMNUMBER, LNAME , FNAME, MNAME, convert(varchar(10),LOSSDATE,101), convert(varchar(10),ERNOTIFIEDDATE,101), convert(varchar(10),CLOSINGDATE,101), STATUSCODE, INDPAID,MEDPAID,REHABPAID,EXPPAID,LEGALPAID,TOTALP AID,INDFUTURERES,MEDFUTURERES,REHABFUTURERES,EXPFU TURERES,LEGALFUTURERES,TOTALFUTURERES,EXCESSRECOVE RY1,EXCESSRECOVERY2,EXCESSRECOVERY3,EXCESSRECOVERY 4,EXCESSRECOVERY5,EXCESSRECOVERY,OTHERRECOVERY1,OT HERRECOVERY2,OTHERRECOVERY3,OTHERRECOVERY4,OTHERRE COVERY5,OTHERRECOVERY FROM ##MMAACTUARY" queryout C:\mma03182004.TXT /c /t, /r \n /U demo /P demo /S ATSDEV\ATS2K

It spits out the file fine. This is all built with dynamic SQL and needs to email the file which is does perfectly. My only problem is that there are no headers which I can't have. Any suggestions?How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.|||I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott|||It's not what I imagined but it does look like it would work. I really don't care at this point how it happens so long as it does! I'll try this and report back with the results.|||Originally posted by hmscott
I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott

Hey, thnaks fo rremebering...and you know what?

bcp -c

and, what's not elegant about it?

I use Union ALL btw

AND to make sure the rows come out correctly...

For fixed width:

SELECT Col1,Col2,Col3 FROM (
SELECT 'Heading1' AS Col1
, 'Heading2' AS Col2
, 'Heading3' AS Col3
, 1 AS RowOrder
UNION ALL
SELECT Col1
, CONVERT(varchar(25), myDatetimeCol99)
, CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

For Delimeted:

SELECT datarow FROM (
SELECT 'Heading1,Heading2,Heading3' AS Datarow
, 1 AS Roworder
UNION ALL
SELECT Col1+','
+','CONVERT(varchar(25), myDatetimeCol99)
+','CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

And usually with delimeted, I wrap all the data in quotes

''''+ Col1 + ''''
','+ ''''+ Col2 + ''''|||Hey, and dig it, you can even add a trailer with audit counts...

Just make it datarow 3 and do a select count(*)...you could even sum amount if you want to go crazy...

AND you could add a row "type" to each one, to make it easier for extraction of the non data row

WHERE SUBSTRING(datarow,1,1) IN ('H','T')|||Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott|||Originally posted by hmscott
Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott

IMNSHO, I wouldn't be to quick to give up DTS. I don't think there is much you can do with BPC that you can't do with DTS. There is a bunch you can do with DTS that you can't do with BCP. There isn't an "ELEGANT" solution here just not as ugly.|||It does work but the string got too long. So, what si did was create a seondary table and inserted the header values. This table only has one row so when using that select statement unioned with the real select statement it works perfectly. If any one wants a copy of the procedure email me jfogel3@.msn.com|||Originally posted by hmscott
BTW, I changed jobs and have been thrown into what seems to be a real lion's den.


Sorry to hear that...

I avoid DTS for any production related issues...seen to many thing I couldn't explain...

Mostly with connections and changing them...Seems like it's lookin at an earlier version sometimes...no thanks...

And I love Nigels sig...

Cursors are useful if you don't know SQL
DTS can be used in a similar manner
Beer is not cold and fizzy

Or a paraphrase...since it seems like sqlteam is down...again....

Oh, and no apologies please....

(otherwise I'll have to do it all the time...)|||ooopps...here's the thread

They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bcp into view with derived column

Hi,

I have a view that looks something like this -

CREATE VIEW myview AS SELECT
myudf(col1) as col1, col2, col3
FROM mytable

The view has an 'INSTEAD OF' trigger on it to do the correct thing on
insert. When I run the bcp, it runs successfully, but the value
inserted into col1 is always NULL. I modified my trigger to get a
better idea of what was happening, and when I look at the values of
INSERTED.col1, they are all null. It appears that bcp is setting the
column value to null, presumable because the view definintion for this
column is a derived column.

Does any one know a way around this?

Thanks!On 10 Jan 2006 11:28:32 -0800, bsandell@.gmail.com wrote:

>Hi,
>I have a view that looks something like this -
>CREATE VIEW myview AS SELECT
> myudf(col1) as col1, col2, col3
>FROM mytable
>The view has an 'INSTEAD OF' trigger on it to do the correct thing on
>insert. When I run the bcp, it runs successfully, but the value
>inserted into col1 is always NULL. I modified my trigger to get a
>better idea of what was happening, and when I look at the values of
>INSERTED.col1, they are all null. It appears that bcp is setting the
>column value to null, presumable because the view definintion for this
>column is a derived column.
>Does any one know a way around this?
>Thanks!

Hi bsandell,

By default, triggers are not fired for bulk copy statements.

To override this default, add the option
-h "FIRE_TRIGGERS"

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
being executed. I have added the following line to my trigger code -

SELECT inserted.* into pubs..dummy from inserted

My input file looks like this -
aaa,aaa,aaa
bbb,bbb,bbb
ccc,ccc,ccc

but when the code above runs in my trigger, the output is -
NULL,aaa,aaa
NULL,bbb,bbb
NULL,ccc,ccc

so it looks like bcp has somehow determined that the first column in my
view does not have a 1-to-1 mapping with a column on the database
(because it's based on a udf) and set it to null? I'd like to find a
way to work around this if possible, without changing the view
definition or the underlying table.

Any ideas?

Thanks,
Bruce|||(bsandell@.gmail.com) writes:
> Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
> being executed. I have added the following line to my trigger code -
> SELECT inserted.* into pubs..dummy from inserted
> My input file looks like this -
> aaa,aaa,aaa
> bbb,bbb,bbb
> ccc,ccc,ccc
> but when the code above runs in my trigger, the output is -
> NULL,aaa,aaa
> NULL,bbb,bbb
> NULL,ccc,ccc
> so it looks like bcp has somehow determined that the first column in my
> view does not have a 1-to-1 mapping with a column on the database
> (because it's based on a udf) and set it to null? I'd like to find a
> way to work around this if possible, without changing the view
> definition or the underlying table.

Smells bug to me. To wit, it works with BULK INSERT, which uses OLE DB
in difference of BCP which uses ODBC. The behaviour is the same in
SQL 2000 and SQL 2005, so I've field a bug for it,
http://lab.msdn.microsoft.com/Produ...ackId=FDBK43711
You can vote for it, if you like.

The repro below is also in the bug report:

USE tempdb
go
CREATE TABLE sandell (col1 int NULL,
col2 int NULL,
col3 int NULL)
go
CREATE FUNCTION myudf(@.c int) RETURNS int AS
BEGIN
RETURN (SELECT 80 - @.c)
END
go
CREATE VIEW sandell_vy AS
SELECT col1 = dbo.myudf(col1), col2, col3
FROM sandell
go
CREATE TRIGGER sandell_tri ON sandell_vy INSTEAD OF INSERT AS
INSERT sandell(col1, col2, col3)
SELECT 80 - col1, col2 + 20, col3 + 20 FROM inserted
go
INSERT sandell_vy (col1, col2, col3)
SELECT 12, 98, 23
go
SELECT * FROM sandell_vy
go
EXEC master..xp_cmdshell 'ECHO 9, 2, 98 > C:\TEMP\bulk.csv', 'no_output'
EXEC master..xp_cmdshell 'ECHO 76, 23, 87 >> C:\TEMP\bulk.csv', 'no_output'
go
BULK INSERT sandell_vy FROM 'C:\temp\bulk.csv'
WITH (FIELDTERMINATOR = ',',
DATAFILETYPE = 'char',
FIRE_TRIGGERS)
go
DECLARE @.bcp nvarchar(4000)
SELECT @.bcp = 'bcp tempdb..sandell_vy in C:\temp\bulk.csv -T -c -t, -h "FIRE_TRIGGERS" -S ' + @.@.servername
EXEC master..xp_cmdshell @.bcp
go
SELECT * FROM sandell_vy
go
DROP VIEW sandell_vy
DROP FUNCTION myudf
DROP TABLE sandell
EXEC master..xp_cmdshell 'DEL C:\temp\bulk.csv', 'no_output'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

As always, I really appreciate your help. Any guesses on the liklihood
that this would get fixed? I didn't think there was much going on with
bcp support these days. Thanks again for you time and effort.

Bruce|||(bsandell@.gmail.com) writes:
> As always, I really appreciate your help. Any guesses on the liklihood
> that this would get fixed? I didn't think there was much going on with
> bcp support these days. Thanks again for you time and effort.

I would guess that it is more likely that it will be fixed in SQL 2005 than
in SQL 2000. Whether the fix will come in SP1 for SQL 2005, I don't want to
speculate in.

If this is critical for you, and you need a hotfix, you will have to open
a case with Microsoft, and try to convince them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS