August 27, 2010 at 8:26 am
Can someone figure out how to make this work?
bcp -version 9 and 10, both SQL Server 2005 and 2008 all fail
We started receiving DateTime values in data files in:
ISO 8601 YYYY-MM-DDThh:mm:ss format
If we remove the "T" it works, but with "T" fails
works: 2010-07-27 00:00:00
fails: 2010-07-27T00:00:00
not options:
- modifying the data files (removing the "T")
- importing into staging tables with datetime fields as varchar
- although bulk insert (via sqlcmd) works, it's probably not an option because it would need to be run on server
thanks in advance for help!
below are the steps to reproduce:
-------------------------------------------------
--step 1. create table:
create table DBName.dbo.test (ts datetime);
-------------------------------------------------
--step 2. create format file:
C:>bcp DBName.dbo.test format nul -c -f test-c.fmt -S ServerName -T
-- the format file contents (note: the \ n below is not renedering):
9.0
1
1 SQLCHAR 0 24 "\r" 1 ts ""
-------------------------------------------------
--two data files, "test1.txt" and "test2.txt"
-------------------------------------------------
test1.txt contents (one record with one column - no "T"):
2010-07-27 00:00:00
-------------------------------------------------
test2.txt contents (one record with one column - has a "T"):
2010-07-27T00:00:00
-------------------------------------------------
--Step 3. load record with no "T" - successful
C:>bcp DBName.dbo.test in test1.txt -f test-c.fmt -S ServerName -T -e err1.txt
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)
-------------------------------------------------
--Step 4. clear table:
truncate table DBName.dbo.test;
-------------------------------------------------
--step 5. Now try to load record with "T" - it fails
C:>bcp DBName.dbo.test in test2.txt -f test-c.fmt -S ServerName -T -e err2.txt
Starting copy...
SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
-------------------------------------------------
err2.txt file contents:
#@ Row 1, Column 1: Invalid character value for cast specification @#
2010-07-27T00:00:00
-------------------------------------------------
August 31, 2010 at 8:12 am
George,
I was able to get the first line correctly with a one column table and a one line file. Still tweaking this one. Try this out and see if it makes sense.
Arthur
----- Table
CREATE TABLE testonly(
[LatestUpdate] [datetime] NULL
) ON [PRIMARY]
----- bcpTest.fmt file
10.0
1
1 SQLCHAR 0 10 "" 1 LatestUpdate ""
----- test.txt
2010-07-15T00:00:00
2010-09-12T00:00:00
2010-04-02T00:00:00
---- Command Line
bcp test.dbo.testonly in test.txt -f bcpTest.fmt -S arossetti1 -T -e errors.out
----- Tests
select * from testonly
delete from testonly
August 13, 2012 at 9:11 am
It looks like this is a bug in BCP.
In my development environment this works for me in SQL Server 2012, but in 2008 R2, I have the same problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply