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 Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam TjaiTry brackets around the table name'
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name'
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net|||You can also use the quotename() function
--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
--
Ekrem Önsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name'
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

bcp right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
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" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
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" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

BCP results in 3 rows

Good Day

I want to know if bcp can do this for me,(I get my results nicely from fetch statement) lets say I do this
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA
and my results are

1 80 6.0 34

I want my text file that I will use bcp to recreate to be like in this format
VersonNo
Fileid
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA

6.0
34
1 80 6.0 34Refer to the books online for BCP Format file option to get the task.|||Refer to the books online for BCP Format file option to get the task.sql

bcp problem

I'm not having a syntax problem, I don't think, but the following is telling 0 rows copied, but I do know the file contains data...anyone have any ideas?
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?

and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file

The redirected out put is essentially 1 column

The table has 3 columns and I forgot to "zero out" the column in the format file

Thanks guys...sql

BCP Out Slow?

I am running the following BCP to extract a table with 156641604 rows.

bcp TestDB..data out test3.bcp -T -b1000000 -a32000

When running this i notice that the disk read bytes\sec counter in performance monitor on the drive that has the database devices is only reading 30mb\sec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?

Hi Andy,

Try not using the -a option, and reducing the batch size to 100000.

What is the purpose of extracting the data. Do you need it to be in char format.

If use -n for native, it will be a bit quicker.

Jag

|||

Hi Jag

That does help with throughput. The thing I don't understand is that even if I run a Select * From TestDB..data i get low read IO on this table. I know the disk IO is far more capable and if I run a DBCC SHOWCONTIG command for instance, the disk read bytes\sec counter in performance monitor jumps to over 70mb\sec. My question is, why doesn't BCP or a Select statement achieve this IO?

|||That's because it might not need to. The DBCC is going to explicitly hit the disk drive. Doing a BCP or a SELECT is going to utilize the normal infrastructure. It is going to read it from memory and then go out to disk as needed. Since SQL Server has an intelligent read ahead capability, it might only need 30mb/sec to keep up with the write process out the other side.|||Ok i can see what you are saying but the write IO is hovering around 30mb\sec also when it can write much faster than that. Is this IO limitation possibly due to the row count being so high, or some other factor?|||The row count shouldn't have any effect on this. BCP is going to run just as fast, regardless of the number of rows. It simply starts at the beginning and is done when SQL Server doesn't send anything else to it. I would suggest comparing this to the performance you get using SSIS. From everything that I've seen thus far, SSIS is going to move stuff a lot faster than BCP. Has to do with the basic interface that is used to connect to and get the data.

bcp out and bcp in don't give the same number of rows

Hi,
I'm trying to export and import one big table in sql
server 2000 using bcp. Original table has 7,416,000 rows,
and after I bcp out this table into file in native format,
and bcp in from this file, also in native format, table
gets 7,478,000 rows and the data takes significantly less
space than before!
This table has char, varchar, numeric, datetime, and text
data types.
Can anybody tell me why the difference?
Thanks,
OJ
Hi,
Significatly less space:-
This is because your source table is fragmented heavily. The fragmentation
can be viwed by
DBCC SHOWCONTIG (Reger books online for info) command. By using this command
see the scan density, if the scan density is
too low than execute a DBCC DBREINDEX (Refer books online for usage and
info) command to remove the fragmention.
Cause for the fragmentation is because of the DML commands (Insert, Update
and Delete).
Row Difference:-
This may be bause of the inconsistency in sysindexes table. Execute the
below command to correct the inconsistency
and give the correct rows in the table. (Use the source table_name)
USE <dbname>
go
sp_spaceused <table_name>,@.updateusage = 'TRUE'
Thanks
Hari
MCDBA
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
> Hi,
> I'm trying to export and import one big table in sql
> server 2000 using bcp. Original table has 7,416,000 rows,
> and after I bcp out this table into file in native format,
> and bcp in from this file, also in native format, table
> gets 7,478,000 rows and the data takes significantly less
> space than before!
> This table has char, varchar, numeric, datetime, and text
> data types.
> Can anybody tell me why the difference?
> Thanks,
> OJ
|||Table is not fragmented at all. It is rebuilt before bcp
out. Statistics are also updated because of rebuilding the
table (clustered index with fillfactor 100)...
Thanks anyway,
OJ
>--Original Message--
>Hi,
>Significatly less space:-
>--
>This is because your source table is fragmented heavily.
The fragmentation
>can be viwed by
>DBCC SHOWCONTIG (Reger books online for info) command. By
using this command
>see the scan density, if the scan density is
>too low than execute a DBCC DBREINDEX (Refer books online
for usage and
>info) command to remove the fragmention.
>Cause for the fragmentation is because of the DML
commands (Insert, Update
>and Delete).
>Row Difference:-
>--
>This may be bause of the inconsistency in sysindexes
table. Execute the
>below command to correct the inconsistency
>and give the correct rows in the table. (Use the source
table_name)
>USE <dbname>
>go
>sp_spaceused <table_name>,@.updateusage = 'TRUE'
>--
>Thanks
>Hari
>MCDBA
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1b69d01c45082$2712ca90$a301280a@.phx.gbl...
rows,[vbcol=seagreen]
format,[vbcol=seagreen]
less[vbcol=seagreen]
text
>
>.
>
|||OJ (anonymous@.discussions.microsoft.com) writes:
> Table is not fragmented at all. It is rebuilt before bcp
> out. Statistics are also updated because of rebuilding the
> table (clustered index with fillfactor 100)...
How was it rebuilt? I've seen that when I have run DBCC DBREINDEX, there
has been a lot of extra space around, as it to have room next time
DBREINDEX is run.
How did you conclude that the number of rows were different? Did you
actually run a SELECT COUNT(*)? Or did you look at sysindexes.rows?
To get accurate values in this column - and in the space column,
run DBCC UPDATEUSAGE on the table.
If you indeed get different results with SELECT COUNT(*) before and
after running BCP, something is fishy. One alternative is that there
was some other data in the target table before you started. Also
check that the schemas of the tables are entirely identical.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

bcp operation within transaction

hello,
I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.

Salik.BCP operation is not affected by a transaction ...
This might help...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31640|||Well...

wouldn't you say that it's an implicit TRANS?

I mean if the bcp fails in the middle (in it's own spid) then that rolls back..

But as Enigma has said...if you do

BEGIN TRAN

master..xp_cmdshell 'bcp...

ROLLBACK TRAN

IF the bcp was successful, then you won't be able to roll it back...

EDIT:

And...

When I do that...SQL server hangs

I would say that's not true...do sp_lock

I would say your spids are blocking each other...

been there...done that....|||Well ... its a thing to be left alone ...
as you say ... been there .. done that (discussion)|||My main issue is that I am writing out data based on a 'events' table which tracks the changes users make to a product table. from the time I have built the strings and inserted them into a temporary table and then output the file, then removed the 'events' - a user could change a field in a product I am writing out - which would be missed by the system. Not good.

The only thing I can think of is to lock the appropriate rows in the product table for the duration. WHich I'm not aware of the syntax.

Any other strategies?

Salik.|||Data is fluid ny bature...what's the difference if it's updated during your export, or immediatley after...the data will still be changed...

And is your issue a matter of concurrency? Why build a temp table?

Also why not schedule a batch window? Grab the date from the system...use that as the window close date, grab all rows added or updated between the last time the window close and this windoes close...

So if they update it during your export it wouldn't be part of this batch...

How long is your transaction...and how many rows on average do you export?|||The output data is '*' delimited for another system and requires padding of numbers, calculation etc.. that's why I build the strings into a temporary table and then ship the temp table out using bcp.

We use triggers to track the events a user makes to a product. on certain fields a product event is created. and on others a price event. The price events are held util evening processing as they can only be sent out once a day. the product files are output every 15 minutes. There are four different files output at the same time, ecah containing slightly different data.

The events table prevents duplicates ie: we don't append. if a existing price event exists we don't add another row.

when I have finished the bcp the appropriate rows are removed from the events table.

sooo - between the time the row for the product has been read and the time I delete the events row for that product. and event could happen...no duplicate would be added..and the new data not written out and the system thinks it has.

I could snatch the rows from the events table into a temptable. delete them and if an error occurs add them back in to the events table...

BCP into image columns

I'm trying to use the bcp executable to insert rows into a table
containing two guids and two image columns. I'm using an explicit
character string for the field delimiter, and another for the row
delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
command I get an error.
C:\>bcp tempdb..t_test in test.bcp -t "||^t^||" -r "||^r^||" -S
myserver -U uname -P pwd -c -N
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered
in BCP data-file
0 rows copied.
I tried the same bcp command with a format file provided, and still
received that same errror. The format file is as follows.
8.0
4
1 SQLUNIQUEID 1 16 "||^t^||" 1
guid1 ""
2 SQLUNIQUEID 1 16 "||^t^||" 2
guid2 ""
3 SQLIMAGE 4 0 "||^t^||" 3
img1 ""
4 SQLIMAGE 4 0 "||^r^||" 4
img2 ""
Am I missing something here? Or are image columns just a pain to work
with?
--ian(ian_jacobsen@.hotmail.com) writes:
> I'm trying to use the bcp executable to insert rows into a table
> containing two guids and two image columns. I'm using an explicit
> character string for the field delimiter, and another for the row
> delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
> command I get an error.
And how does the data in the file look like? Judging from your description
there is newlines in it. Is that correct?

> I tried the same bcp command with a format file provided, and still
> received that same errror. The format file is as follows.
> 8.0
> 4
> 1 SQLUNIQUEID 1 16 "||^t^||" 1 guid1 ""
> 2 SQLUNIQUEID 1 16 "||^t^||" 2 guid2 ""
> 3 SQLIMAGE 4 0 "||^t^||" 3 img1 ""
> 4 SQLIMAGE 4 0 "||^r^||" 4 img2 ""
That's a format file for a binary file. Your BCP command was for a
character file.

> Am I missing something here? Or are image columns just a pain to work
> with?
image are indeed difficult to deal with, but I have a feeling you have
not reached those problems yet. :-) SQL 2005 has varbinary(MAX) which
can fit as much data as image, but is a lot easier to deal with.
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|||>From your reply I'm guessing that my approach is wrong. The data that
I am trying to insert into the image column is an xml file that has
been compressed. Since it is very costly to store xml in its native
state, I would like to compress this data and store it in the database.
I'm exporting a dataset to xml, compressing that data, and then I
append this data onto my bcp file containing the two guids. In fact I
have two datasets that I'm trying to append. Is there a better way to
go about compression? Or a better way to format my bcp file?

>And how does the data in the file look like?
I'd rather not post the contents of my bcp file here (since its filled
with binary data and would be a mess), but here is a sample of the
layout with the binary cut down.
035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-5a0bbfcb=
a033||^t^||PK
Ti4||^t^||PK Ti4||^r^||

>Judging from your description there is newlines in it. Is that correct?
I'm presuming that a binary file can contain any characters, including
newlines.
--ian|||(ian_jacobsen@.hotmail.com) writes:
> From your reply I'm guessing that my approach is wrong. The data that
> I am trying to insert into the image column is an xml file that has
> been compressed. Since it is very costly to store xml in its native
> state, I would like to compress this data and store it in the database.
Undoubtedly, SQL 2005 should be very appealing to you, as it has a
built-in xml data type.

> I'd rather not post the contents of my bcp file here (since its filled
> with binary data and would be a mess), but here is a sample of the
> layout with the binary cut down.
Ah, that clarifies things quite a bit. This format file could work:
8.0
4
1 SQLCHAR 0 0 "||^t^||" 1 guid1 ""
2 SQLCHAR 0 0 "||^t^||" 2 guid2 ""
3 SQLIMAGE 0 0 "||^t^||" 3 img1 ""
4 SQLIMAGE 0 0 "||^r^||" 4 img2 ""
Since the GUIDs appears as text in the data file, the correct data
type for these is SQLCHAR. I've also set the prefix length to 0,
as your file does not seem to include length prefixes. (Although this
could be a better choice for the image columns, rather than using
a separator which in case of bad luck could appear in the data.

> 035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-
5a0bbfcba033||^t^||PK
> Ti4||^t^||PK Ti4||^r^||
And the next GUID appears directly after ||, that is no newline?
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|||When using the format file that you provided, this fixed the problem I
was having.
Thanks
--IAN|||(ian_jacobsen@.hotmail.com) writes:
> When using the format file that you provided, this fixed the problem I
> was having.
Ah! I am glad to hear that! I was afraid that it would not be that simple,
but we would hit some other snag.
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 into image columns

I'm trying to use the bcp executable to insert rows into a table
containing two guids and two image columns. I'm using an explicit
character string for the field delimiter, and another for the row
delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
command I get an error.
C:\>bcp tempdb..t_test in test.bcp -t "||^t^||" -r "||^r^||" -S
myserver -U uname -P pwd -c -N
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered
in BCP data-file
0 rows copied.
I tried the same bcp command with a format file provided, and still
received that same errror. The format file is as follows.
8.0
4
1 SQLUNIQUEID 1 16 "||^t^||" 1
guid1 ""
2 SQLUNIQUEID 1 16 "||^t^||" 2
guid2 ""
3 SQLIMAGE 4 0 "||^t^||" 3
img1 ""
4 SQLIMAGE 4 0 "||^r^||" 4
img2 ""
Am I missing something here? Or are image columns just a pain to work
with?
--ian
(ian_jacobsen@.hotmail.com) writes:
> I'm trying to use the bcp executable to insert rows into a table
> containing two guids and two image columns. I'm using an explicit
> character string for the field delimiter, and another for the row
> delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
> command I get an error.
And how does the data in the file look like? Judging from your description
there is newlines in it. Is that correct?

> I tried the same bcp command with a format file provided, and still
> received that same errror. The format file is as follows.
> 8.0
> 4
> 1 SQLUNIQUEID 1 16 "||^t^||" 1 guid1 ""
> 2 SQLUNIQUEID 1 16 "||^t^||" 2 guid2 ""
> 3 SQLIMAGE 4 0 "||^t^||" 3 img1 ""
> 4 SQLIMAGE 4 0 "||^r^||" 4 img2 ""
That's a format file for a binary file. Your BCP command was for a
character file.

> Am I missing something here? Or are image columns just a pain to work
> with?
image are indeed difficult to deal with, but I have a feeling you have
not reached those problems yet. :-) SQL 2005 has varbinary(MAX) which
can fit as much data as image, but is a lot easier to deal with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||>From your reply I'm guessing that my approach is wrong. The data that
I am trying to insert into the image column is an xml file that has
been compressed. Since it is very costly to store xml in its native
state, I would like to compress this data and store it in the database.
I'm exporting a dataset to xml, compressing that data, and then I
append this data onto my bcp file containing the two guids. In fact I
have two datasets that I'm trying to append. Is there a better way to
go about compression? Or a better way to format my bcp file?

>And how does the data in the file look like?
I'd rather not post the contents of my bcp file here (since its filled
with binary data and would be a mess), but here is a sample of the
layout with the binary cut down.
035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-5a0bbfcb=
a033||^t^||PK
Ti4||^t^||PK Ti4||^r^||

>Judging from your description there is newlines in it. Is that correct?
I'm presuming that a binary file can contain any characters, including
newlines.
--ian
|||(ian_jacobsen@.hotmail.com) writes:
> From your reply I'm guessing that my approach is wrong. The data that
> I am trying to insert into the image column is an xml file that has
> been compressed. Since it is very costly to store xml in its native
> state, I would like to compress this data and store it in the database.
Undoubtedly, SQL 2005 should be very appealing to you, as it has a
built-in xml data type.

> I'd rather not post the contents of my bcp file here (since its filled
> with binary data and would be a mess), but here is a sample of the
> layout with the binary cut down.
Ah, that clarifies things quite a bit. This format file could work:
8.0
4
1 SQLCHAR 0 0 "||^t^||" 1 guid1 ""
2 SQLCHAR 0 0 "||^t^||" 2 guid2 ""
3 SQLIMAGE 0 0 "||^t^||" 3 img1 ""
4 SQLIMAGE 0 0 "||^r^||" 4 img2 ""
Since the GUIDs appears as text in the data file, the correct data
type for these is SQLCHAR. I've also set the prefix length to 0,
as your file does not seem to include length prefixes. (Although this
could be a better choice for the image columns, rather than using
a separator which in case of bad luck could appear in the data.

> 035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-
5a0bbfcba033||^t^||PK
> Ti4||^t^||PK Ti4||^r^||
And the next GUID appears directly after ||, that is no newline?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||When using the format file that you provided, this fixed the problem I
was having.
Thanks
--IAN
|||(ian_jacobsen@.hotmail.com) writes:
> When using the format file that you provided, this fixed the problem I
> was having.
Ah! I am glad to hear that! I was afraid that it would not be that simple,
but we would hit some other snag.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

BCP into image columns

I'm trying to use the bcp executable to insert rows into a table
containing two guids and two image columns. I'm using an explicit
character string for the field delimiter, and another for the row
delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
command I get an error.
C:\>bcp tempdb..t_test in test.bcp -t "||^t^||" -r "||^r^||" -S
myserver -U uname -P pwd -c -N
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered
in BCP data-file
0 rows copied.
I tried the same bcp command with a format file provided, and still
received that same errror. The format file is as follows.
8.0
4
1 SQLUNIQUEID 1 16 "||^t^||" 1
guid1 ""
2 SQLUNIQUEID 1 16 "||^t^||" 2
guid2 ""
3 SQLIMAGE 4 0 "||^t^||" 3
img1 ""
4 SQLIMAGE 4 0 "||^r^||" 4
img2 ""
Am I missing something here? Or are image columns just a pain to work
with?
--ian(ian_jacobsen@.hotmail.com) writes:
> I'm trying to use the bcp executable to insert rows into a table
> containing two guids and two image columns. I'm using an explicit
> character string for the field delimiter, and another for the row
> delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
> command I get an error.
And how does the data in the file look like? Judging from your description
there is newlines in it. Is that correct?
> I tried the same bcp command with a format file provided, and still
> received that same errror. The format file is as follows.
> 8.0
> 4
> 1 SQLUNIQUEID 1 16 "||^t^||" 1 guid1 ""
> 2 SQLUNIQUEID 1 16 "||^t^||" 2 guid2 ""
> 3 SQLIMAGE 4 0 "||^t^||" 3 img1 ""
> 4 SQLIMAGE 4 0 "||^r^||" 4 img2 ""
That's a format file for a binary file. Your BCP command was for a
character file.
> Am I missing something here? Or are image columns just a pain to work
> with?
image are indeed difficult to deal with, but I have a feeling you have
not reached those problems yet. :-) SQL 2005 has varbinary(MAX) which
can fit as much data as image, but is a lot easier to deal with.
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|||>From your reply I'm guessing that my approach is wrong. The data that
I am trying to insert into the image column is an xml file that has
been compressed. Since it is very costly to store xml in its native
state, I would like to compress this data and store it in the database.
I'm exporting a dataset to xml, compressing that data, and then I
append this data onto my bcp file containing the two guids. In fact I
have two datasets that I'm trying to append. Is there a better way to
go about compression? Or a better way to format my bcp file?
>And how does the data in the file look like?
I'd rather not post the contents of my bcp file here (since its filled
with binary data and would be a mess), but here is a sample of the
layout with the binary cut down.
035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-5a0bbfcb=a033||^t^||PK
Ti4||^t^||PK Ti4||^r^||
>Judging from your description there is newlines in it. Is that correct?
I'm presuming that a binary file can contain any characters, including
newlines.
--ian|||(ian_jacobsen@.hotmail.com) writes:
> From your reply I'm guessing that my approach is wrong. The data that
> I am trying to insert into the image column is an xml file that has
> been compressed. Since it is very costly to store xml in its native
> state, I would like to compress this data and store it in the database.
Undoubtedly, SQL 2005 should be very appealing to you, as it has a
built-in xml data type.
>>And how does the data in the file look like?
> I'd rather not post the contents of my bcp file here (since its filled
> with binary data and would be a mess), but here is a sample of the
> layout with the binary cut down.
Ah, that clarifies things quite a bit. This format file could work:
8.0
4
1 SQLCHAR 0 0 "||^t^||" 1 guid1 ""
2 SQLCHAR 0 0 "||^t^||" 2 guid2 ""
3 SQLIMAGE 0 0 "||^t^||" 3 img1 ""
4 SQLIMAGE 0 0 "||^r^||" 4 img2 ""
Since the GUIDs appears as text in the data file, the correct data
type for these is SQLCHAR. I've also set the prefix length to 0,
as your file does not seem to include length prefixes. (Although this
could be a better choice for the image columns, rather than using
a separator which in case of bad luck could appear in the data.
> 035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-
5a0bbfcba033||^t^||PK
> Ti4||^t^||PK Ti4||^r^||
And the next GUID appears directly after ||, that is no newline?
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|||When using the format file that you provided, this fixed the problem I
was having.
Thanks
--IAN|||(ian_jacobsen@.hotmail.com) writes:
> When using the format file that you provided, this fixed the problem I
> was having.
Ah! I am glad to hear that! I was afraid that it would not be that simple,
but we would hit some other snag.
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 into image columns

I'm trying to use the bcp executable to insert rows into a table
containing two guids and two image columns. I'm using an explicit
character string for the field delimiter, and another for the row
delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
command I get an error.
C:\>bcp tempdb..t_test in test.bcp -t "||^t^||" -r "||^r^||" -S
myserver -U uname -P pwd -c -N
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountere
d
in BCP data-file
0 rows copied.
I tried the same bcp command with a format file provided, and still
received that same errror. The format file is as follows.
8.0
4
1 SQLUNIQUEID 1 16 "||^t^||" 1
guid1 ""
2 SQLUNIQUEID 1 16 "||^t^||" 2
guid2 ""
3 SQLIMAGE 4 0 "||^t^||" 3
img1 ""
4 SQLIMAGE 4 0 "||^r^||" 4
img2 ""
Am I missing something here? Or are image columns just a pain to work
with?
--ian(ian_jacobsen@.hotmail.com) writes:
> I'm trying to use the bcp executable to insert rows into a table
> containing two guids and two image columns. I'm using an explicit
> character string for the field delimiter, and another for the row
> delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
> command I get an error.
And how does the data in the file look like? Judging from your description
there is newlines in it. Is that correct?

> I tried the same bcp command with a format file provided, and still
> received that same errror. The format file is as follows.
> 8.0
> 4
> 1 SQLUNIQUEID 1 16 "||^t^||" 1 guid1 ""
> 2 SQLUNIQUEID 1 16 "||^t^||" 2 guid2 ""
> 3 SQLIMAGE 4 0 "||^t^||" 3 img1 ""
> 4 SQLIMAGE 4 0 "||^r^||" 4 img2 ""
That's a format file for a binary file. Your BCP command was for a
character file.

> Am I missing something here? Or are image columns just a pain to work
> with?
image are indeed difficult to deal with, but I have a feeling you have
not reached those problems yet. :-) SQL 2005 has varbinary(MAX) which
can fit as much data as image, but is a lot easier to deal with.
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|||>From your reply I'm guessing that my approach is wrong. The data that
I am trying to insert into the image column is an xml file that has
been compressed. Since it is very costly to store xml in its native
state, I would like to compress this data and store it in the database.
I'm exporting a dataset to xml, compressing that data, and then I
append this data onto my bcp file containing the two guids. In fact I
have two datasets that I'm trying to append. Is there a better way to
go about compression? Or a better way to format my bcp file?

>And how does the data in the file look like?
I'd rather not post the contents of my bcp file here (since its filled
with binary data and would be a mess), but here is a sample of the
layout with the binary cut down.
035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-5a0bbfcb=
a033||^t^||PK
Ti4||^t^||PK Ti4||^r^||

>Judging from your description there is newlines in it. Is that correct?
I'm presuming that a binary file can contain any characters, including
newlines.
--ian|||(ian_jacobsen@.hotmail.com) writes:
> From your reply I'm guessing that my approach is wrong. The data that
> I am trying to insert into the image column is an xml file that has
> been compressed. Since it is very costly to store xml in its native
> state, I would like to compress this data and store it in the database.
Undoubtedly, SQL 2005 should be very appealing to you, as it has a
built-in xml data type.

> I'd rather not post the contents of my bcp file here (since its filled
> with binary data and would be a mess), but here is a sample of the
> layout with the binary cut down.
Ah, that clarifies things quite a bit. This format file could work:
8.0
4
1 SQLCHAR 0 0 "||^t^||" 1 guid1 ""
2 SQLCHAR 0 0 "||^t^||" 2 guid2 ""
3 SQLIMAGE 0 0 "||^t^||" 3 img1 ""
4 SQLIMAGE 0 0 "||^r^||" 4 img2 ""
Since the GUIDs appears as text in the data file, the correct data
type for these is SQLCHAR. I've also set the prefix length to 0,
as your file does not seem to include length prefixes. (Although this
could be a better choice for the image columns, rather than using
a separator which in case of bad luck could appear in the data.

> 035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-
5a0bbfcba033||^t^||PK
> Ti4||^t^||PK Ti4||^r^||
And the next GUID appears directly after ||, that is no newline?
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|||When using the format file that you provided, this fixed the problem I
was having.
Thanks
--IAN|||(ian_jacobsen@.hotmail.com) writes:
> When using the format file that you provided, this fixed the problem I
> was having.
Ah! I am glad to hear that! I was afraid that it would not be that simple,
but we would hit some other snag.
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 inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million row
s
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time th
e
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server
.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is bei
ng
> truncated? How is the BCP process (the one that is misbehaving) called?
Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million rows
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time the
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!
No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!
|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is being
> truncated? How is the BCP process (the one that is misbehaving) called? Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp file format...its killing me...pleaseeeeeeeee help...

Hi,
i wanted to ask on the same context of bcp...i m trying to insert two rows into a table using the bcp command prompt and the bcp file looks as follow(format.fmt):
7.0
3
1 SQLCHAR 0 4 "\"," 1 numbers
2 SQLCHAR 0 15 "\"" 2 values
3 SQLCHAR 0 2 "\r\n" 3 finish

and the input file has the following contents
1234,"other"
5678,"column"

now when i m running the command as
bcp master.dbo.two_column in c:\temp\input.txt -fc:\temp\format.fmt -Sserver -Uuser -Ppasword -T

it gives me the error as sqlstate 07009 with native state =0 and the error as = [Microsoft][SQL Native Client] Invalid descriptor index

i m using sql server 2005 (YUKON)...can anyone pleaseeeeeeeeeeeeee tell me how can i solve this problem???????so that i can insert these rows into my table two_column...
thanks ...pleaseeeeeeeeee help me...First off, line one should not be 7.0 for 2005.

For SQL 2k it.s8.0

I guess 2005, it should be 9.0, but I don't know that for sure.

Did you get the 2005 BOL Beta as well?|||Just curious, but what's the DDL for creating your table? I suspect it only has two columns.

-PatP|||Thanks for your replies...but now...i have changed the version of format file as follow...n yeah i have 2columns only:
9.0
2
1 SQLCHAR 0 4 "," 1 in_numbers SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\r\n" 2 values SQL_Latin1_General_CP1_CI_AS

and the input file is
1234,other
5678,column

but now its givin me the error as I\O error while reading BCP format file...

it was workin for sometime..it copied these two rows as well...but again its givin me this error again n again...i cant understand...wat m i suppose to do now...PLEASEEEEE TRY TO HELPPPP
thnks in advance|||Can you post the exact error ?|||hi,
the error was just that I\O error while reading the BCP file format...but now finally its over since i had to press enter twice...after the last row in the format file...just some empty rows in the format files had solved my problem
thnks.

BCP Exporting Duplicate Rows

Hi everyone.
I am BCP-ing out data from one table into separate files based on a date
range. Each file has anywhere from 15 to 30 million rows. The source table
has a primary key, and I have verified that there is no duplicate rows. I
have also double checked the date ranges and they are good.
I then bcp the data back into another empty table, with the same primary key
as the source table. But I get a primary key violation error. I then bcp-ed
the data into a temporary table with no primary key. Then I ran a query to
check for duplicates. And sure enough, there were over 300K duplicate rows.
Has anybody seen this kind of behavior with BCP?
OS: Window Server 2003 R2, Standard - SP2.
SQL: 2005 Enterprise, SP2
Thank you!
J> Has anybody seen this kind of behavior with BCP?
I haven't run into this. Can you provide more details on the BCP command
you are using (i.e. out, queryout)?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> Hi everyone.
> I am BCP-ing out data from one table into separate files based on a date
> range. Each file has anywhere from 15 to 30 million rows. The source table
> has a primary key, and I have verified that there is no duplicate rows. I
> have also double checked the date ranges and they are good.
> I then bcp the data back into another empty table, with the same primary
> key
> as the source table. But I get a primary key violation error. I then
> bcp-ed
> the data into a temporary table with no primary key. Then I ran a query to
> check for duplicates. And sure enough, there were over 300K duplicate
> rows.
> Has anybody seen this kind of behavior with BCP?
> OS: Window Server 2003 R2, Standard - SP2.
> SQL: 2005 Enterprise, SP2
> Thank you!
> J|||My BCP statement is pretty simple. I am using queryout to select data from
date ranges. Here's the string:
bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
"Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
"Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
-a8192
Thanks!
"Dan Guzman" wrote:
> > Has anybody seen this kind of behavior with BCP?
> I haven't run into this. Can you provide more details on the BCP command
> you are using (i.e. out, queryout)?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> > Hi everyone.
> > I am BCP-ing out data from one table into separate files based on a date
> > range. Each file has anywhere from 15 to 30 million rows. The source table
> > has a primary key, and I have verified that there is no duplicate rows. I
> > have also double checked the date ranges and they are good.
> > I then bcp the data back into another empty table, with the same primary
> > key
> > as the source table. But I get a primary key violation error. I then
> > bcp-ed
> > the data into a temporary table with no primary key. Then I ran a query to
> > check for duplicates. And sure enough, there were over 300K duplicate
> > rows.
> > Has anybody seen this kind of behavior with BCP?
> >
> > OS: Window Server 2003 R2, Standard - SP2.
> > SQL: 2005 Enterprise, SP2
> >
> > Thank you!
> >
> > J
>|||If the table is updated during a scan with the NOLOCK hint (or READ
UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
and you might get uncommitted data. This issue applies to any READ
UNCOMMITTED query, not just BCP.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> My BCP statement is pretty simple. I am using queryout to select data from
> date ranges. Here's the string:
> bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> -a8192
> Thanks!
> "Dan Guzman" wrote:
>> > Has anybody seen this kind of behavior with BCP?
>> I haven't run into this. Can you provide more details on the BCP command
>> you are using (i.e. out, queryout)?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> > Hi everyone.
>> > I am BCP-ing out data from one table into separate files based on a
>> > date
>> > range. Each file has anywhere from 15 to 30 million rows. The source
>> > table
>> > has a primary key, and I have verified that there is no duplicate rows.
>> > I
>> > have also double checked the date ranges and they are good.
>> > I then bcp the data back into another empty table, with the same
>> > primary
>> > key
>> > as the source table. But I get a primary key violation error. I then
>> > bcp-ed
>> > the data into a temporary table with no primary key. Then I ran a query
>> > to
>> > check for duplicates. And sure enough, there were over 300K duplicate
>> > rows.
>> > Has anybody seen this kind of behavior with BCP?
>> >
>> > OS: Window Server 2003 R2, Standard - SP2.
>> > SQL: 2005 Enterprise, SP2
>> >
>> > Thank you!
>> >
>> > J|||Thanks for the reply.
This table is not updated, it's static data. I tried the same query with an
"insert into ... select" command, and it worked fine.
"Dan Guzman" wrote:
> If the table is updated during a scan with the NOLOCK hint (or READ
> UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
> and you might get uncommitted data. This issue applies to any READ
> UNCOMMITTED query, not just BCP.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> > My BCP statement is pretty simple. I am using queryout to select data from
> > date ranges. Here's the string:
> > bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> > -a8192
> >
> > Thanks!
> >
> > "Dan Guzman" wrote:
> >
> >> > Has anybody seen this kind of behavior with BCP?
> >>
> >> I haven't run into this. Can you provide more details on the BCP command
> >> you are using (i.e. out, queryout)?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> >> > Hi everyone.
> >> > I am BCP-ing out data from one table into separate files based on a
> >> > date
> >> > range. Each file has anywhere from 15 to 30 million rows. The source
> >> > table
> >> > has a primary key, and I have verified that there is no duplicate rows.
> >> > I
> >> > have also double checked the date ranges and they are good.
> >> > I then bcp the data back into another empty table, with the same
> >> > primary
> >> > key
> >> > as the source table. But I get a primary key violation error. I then
> >> > bcp-ed
> >> > the data into a temporary table with no primary key. Then I ran a query
> >> > to
> >> > check for duplicates. And sure enough, there were over 300K duplicate
> >> > rows.
> >> > Has anybody seen this kind of behavior with BCP?
> >> >
> >> > OS: Window Server 2003 R2, Standard - SP2.
> >> > SQL: 2005 Enterprise, SP2
> >> >
> >> > Thank you!
> >> >
> >> > J
> >>
>|||The only explanation I can think of is that the IAM pages (scanned due to
NOLOCK hint) are out-of-sync with the linked list (used with default READ
COMMITTED). Did you try the BCP queryout without the NOLOCK hint? You
might try running a DBCC CHECKDB.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:9875560F-42C7-46F8-ABF4-44C10C76B310@.microsoft.com...
> Thanks for the reply.
> This table is not updated, it's static data. I tried the same query with
> an
> "insert into ... select" command, and it worked fine.
>
> "Dan Guzman" wrote:
>> If the table is updated during a scan with the NOLOCK hint (or READ
>> UNCOMMITTED transaction isolation level), rows may be skipped or
>> duplicated
>> and you might get uncommitted data. This issue applies to any READ
>> UNCOMMITTED query, not just BCP.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
>> > My BCP statement is pretty simple. I am using queryout to select data
>> > from
>> > date ranges. Here's the string:
>> > bcp "select * from MARS.dbo.CardTransaction (nolock) where
>> > TRANSACTIONTIME
>> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'"
>> > queryout
>> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
>> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01
>> > -T
>> > -a8192
>> >
>> > Thanks!
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Has anybody seen this kind of behavior with BCP?
>> >>
>> >> I haven't run into this. Can you provide more details on the BCP
>> >> command
>> >> you are using (i.e. out, queryout)?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> >> > Hi everyone.
>> >> > I am BCP-ing out data from one table into separate files based on a
>> >> > date
>> >> > range. Each file has anywhere from 15 to 30 million rows. The source
>> >> > table
>> >> > has a primary key, and I have verified that there is no duplicate
>> >> > rows.
>> >> > I
>> >> > have also double checked the date ranges and they are good.
>> >> > I then bcp the data back into another empty table, with the same
>> >> > primary
>> >> > key
>> >> > as the source table. But I get a primary key violation error. I then
>> >> > bcp-ed
>> >> > the data into a temporary table with no primary key. Then I ran a
>> >> > query
>> >> > to
>> >> > check for duplicates. And sure enough, there were over 300K
>> >> > duplicate
>> >> > rows.
>> >> > Has anybody seen this kind of behavior with BCP?
>> >> >
>> >> > OS: Window Server 2003 R2, Standard - SP2.
>> >> > SQL: 2005 Enterprise, SP2
>> >> >
>> >> > Thank you!
>> >> >
>> >> > J
>> >>

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
Message posted via http://www.sqlmonster.com
did you use "select count(*)" ?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQLMonster.c om...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
> --
> Message posted via http://www.sqlmonster.com

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
--
Message posted via http://www.sqlmonster.comdid you use "select count(*)" ?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQLMonster.com...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[servername]
> --
> Message posted via http://www.sqlmonster.com

BCP Export rowcount

Any idea why, when my BCP export completed that it stated it exported
2,698,467 rows, but when I do a row count in the table, the row count is
2,665,081?
The table is being used exclusively by myself, as currently I am the only
user that has permissions to it.
The error log from the BCP export reveals no errors.
This is the syntax of my BCP export:
bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out "e:\backups\
archive.bcp"
-n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[serv
ername]
Message posted via http://www.droptable.comdid you use "select count(*)" ?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:d5932af1b4344880a6c310e39590d9f5@.SQ
droptable.com...
> Any idea why, when my BCP export completed that it stated it exported
> 2,698,467 rows, but when I do a row count in the table, the row count is
> 2,665,081?
> The table is being used exclusively by myself, as currently I am the only
> user that has permissions to it.
> The error log from the BCP export reveals no errors.
> This is the syntax of my BCP export:
> bcp "cms user messaging archive"."dbo"."ifsmessagesarchive" out
> "e:\backups\
> archive.bcp"
> -n -e"e:\backups\error.txt" -q -U[username] -P[password] -S[se
rvername]
> --
> Message posted via http://www.droptable.com