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 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 not exporting the "{" or "}" characters

Hello,

I am running a bcp command to export all data from one single table. One of the table field has the flower brackets at the begining and the end and the column is defined as of data type uniqueidentifier (length 16).

The original command that I used is :

c:\bcp dbo.Product out c:\product.csv -w -t"|" -r\n

The ProdID column in Product table have values like : {3E116F82-5E52-4EF9-9A97-8756EA6E9A16}

But in the out put file, the flower brackets are being omitted.

So, I tried the following query :

c:\bcp "Select ID,Name,"+'"{"+'+"convert(varchar(100),ProdID)"+'+"}"'+",ProdType,IsActive,TitleCode,CreationDate,CreatorID,AllowView,AllowRead,AllowWrite,AllowDelete,AdminURL,ReportProc from dbo.Product" queryout c:\Product16Feb.csv -w -t"|" -r\n

But it is giving the following error message:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

The SQL statement is correct because when I run it individuall, it works but not with the bcp command.

I just want the ProdID column to have the flower brackets in the output file using bcp. How can I achieve this?

Many thanks

Ratan

You can define your SQL statement as a view and then BCP the view. Otherwise, consider editing the file after the BCP operation and applying the brackets then.

bcp invalid date format

Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruHi
/*
Kelly,Reynold,kelly@.reynold.com,5/30/2007 9:27:35 AM
John,Smith,bill@.smith.com,5/30/2007 9:27:35 AM
Sara,Parker,sara@.parker.com,5/30/2007 9:27:35 AM
*/
Make a column for dates as VARCHAR(50) and try insert
CREATE TABLE Tmp
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL,
dt varchar (100)
)
BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
SELECT * FROM Tmp
Later on you can convert VARCHAR to DATETIME datatype
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>|||On Nov 1, 4:47 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> /*
> Kelly,Reynold,ke...@.reynold.com,5/30/2007 9:27:35 AM
> John,Smith,b...@.smith.com,5/30/2007 9:27:35 AM
> Sara,Parker,s...@.parker.com,5/30/2007 9:27:35 AM
> */
> Make a column for dates as VARCHAR(50) and try insert
> CREATE TABLE Tmp
> (
> stFName varchar (50) NOT NULL,
> stLName varchar (50) NOT NULL,
> stEmail varchar (100) NOT NULL,
> dt varchar (100)
> )
> BULK INSERT tmp FROM 'c:\myfile.txt' WITH (FIELDTERMINATOR = ',')
> SELECT * FROM Tmp
> Later on you can convert VARCHAR to DATETIME datatype
> "Thiru.net" <thirudot...@.gmail.com> wrote in message
> news:1193915642.828573.240090@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have problem in BCP utility while exporting data to sql server.
> > It works in my system and my friend's system , but it gives the
> > following when its run in production
> > server.
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > SQLState = 22007, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Invalid date format
> > BCP copy in failed
> > there are about 365000 records in the data file...
> > the date values go like below;
> > 5/30/2007 9:27:35 AM
> > 5/30/2007 9:28:37 AM
> > 5/30/2007 5:28:16 PM
> > 5/30/2007 5:28:32 PM
> > 5/30/2007 5:28:16 PM
> > please help me for this problem...
> > thanks,
> > Thiru- Hide quoted text -
> - Show quoted text -
Hi,
Thanks for your reply, actually I am using bcp command-lind utility
here. I cant change the datatype in the production server..
Thanks,
Thiru

BCP invalid date

Hi,
I have problem in BCP utility while exporting data to sql server.
It works in my system and my friend's system , but it gives the
following when its run in production
server.
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid date format
BCP copy in failed
there are about 365000 records in the data file...
the date values go like below;
5/30/2007 9:27:35 AM
5/30/2007 9:28:37 AM
5/30/2007 5:28:16 PM
5/30/2007 5:28:32 PM
5/30/2007 5:28:16 PM
please help me for this problem...
thanks,
ThiruThiru,
This could be because the regional settings of the server are different, or
your login's language setting on SQL Server is different. The problem is
that going into your server it is probably interpreting 5/30/2007 as: Day 5
of Month 30, which is invalid, of course.
If that is the problem, create a login on your SQL Server with a
default_language that uses the proper regional settings. For example:
Create Login AmericanImport WITH Password '####',
default_language='us_english'
Grant the AmericanImport login enough rights to bcp in the data to your
database, then run bcp using the AmericanImport login, which is using a
language setting that will properly interpret dates in this format.
RLF
"Thiru.net" <thirudotnet@.gmail.com> wrote in message
news:1193915612.728350.46490@.i13g2000prf.googlegroups.com...
> Hi,
> I have problem in BCP utility while exporting data to sql server.
> It works in my system and my friend's system , but it gives the
> following when its run in production
> server.
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> SQLState = 22007, NativeError = 0
> Error = [Microsoft][SQL Native Client]Invalid date format
> BCP copy in failed
> there are about 365000 records in the data file...
> the date values go like below;
> 5/30/2007 9:27:35 AM
> 5/30/2007 9:28:37 AM
> 5/30/2007 5:28:16 PM
> 5/30/2007 5:28:32 PM
> 5/30/2007 5:28:16 PM
> please help me for this problem...
> thanks,
> Thiru
>

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 exporting data, what's t

Hi
You may want to post DDL and sample data for this so it can be re-produced.
If you try bcp with the format option, does the format file differ to the
one you have?
John
"Scott A. Keen" wrote:

> I'm using BCP to export data.
> When I look at the data with Notepad, some empty fields have this "?"
> character, but this character does not appear in the database table.
> This is apparently causing problems with the company we're exchange data
> with. They are asking for the field to be empty, not to contain this "?"
> character.
> What can I do to make sure the field is blank instead of containing this "
?"
> character? Is this a character set issue? How do I correct this?
> Thanks,
> Scott
>
> For your reference, I've set up a format file which looks like this...
> 8.0
> 55
> 1 SQLCHAR 0 36 "|" 1 Order_GUID SQL_Latin1_General_Cp437_BIN
> ...snip...
> 54 SQLCHAR 0 50 "|" 54 PlaceReceipt SQL_Latin1_General_Cp437_BIN
> 55 SQLCHAR 0 50 "\r\n" 55 container_SealNumber SQL_Latin1_General_Cp437_BI
N
>
>We have a UNIX system that exports that character when a blank field should
be returned. That strange character typically represents a place holder for
the table / field DB structure. On the UNIX system we have it uses Informix
as its DB and informix uses the ? to repersent a blank field. Thus, when I
import into MS SQL the character gets loaded into the table.
When exporting the data, I have got around this by using the replace
command, replace(Field, '?','') to get the blank field, but I do not know i
f
you can do this with bcp, I typically use dts.
-David
"John Bell" wrote:
> Hi
> You may want to post DDL and sample data for this so it can be re-produced
.
> If you try bcp with the format option, does the format file differ to the
> one you have?
> John
> "Scott A. Keen" wrote:
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:

> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>