[freetds] Bulk insert problem with nvarchar(n) where n=[odd number]
Alexander Larkin
sasha at chuhloma.ru
Wed Jul 1 04:41:47 EDT 2009
Hi,
I appreciate much your efforts in developing FreeTDS.
I have a bulk insert problem for field with type nvarchar(n) where
n=[odd number] for MSSQL 2008 (TDS7).
I'm using FreeTDS 0.82, but I tried 0.83 (development) and it is the same.
The problem is MSSQl specific.
Note that for nvarchar(n) the n is amount of symbols where each symbol
is doubled-char. So for nvarchar(3) the length would be 6 bytes.
The nvarchar(3) doesn't work for me at all (below), but nvarchar(4) is
Ok, so the problem is that someone (Microsoft? FreeTDS?) set incorrect
length in TDS protocol (like 3 instead of 6) or interpret such a length
when it is already set incorrectly. Anyway, the sending data to DB
itself works fine for nvarchar(4), so the problem is only with max field
length.
The problem is:
"
msg_handler: Server 'WL-BLADE6', msg_handler: Line 1err_handler: Msg
4895, Level 16err_handler: General SQL Server error: Check messages from
the SQL Server
bcp_done() returned error
"
If I change type nvarchar(255) to nvarchar(254) or nvarchar(2), then
everything works exelent, but if I use nvarchar(1) or nvarchar(201) or
nvarchar(255), then such an error happens.
How to reproduce a bug:
1. Create table with nvarchar(3)
USE [TableName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestNVarChar](
[testfield] [nvarchar](3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
2. Run my test program (the source code attached):
sasha at lt:~/$ ./test_nvarchar
msg_handler: Msg 4895, Level 16, State 2msg_handler: Server 'WL-BLADE6',
msg_handler: Line 1msg_handler: Unicode data is odd byte size for column
1. Should be even byte size.
msg_handler: severity 16 > 10, FATAL SQL errorerr_handler: Msg 4895,
Level 16err_handler: General SQL Server error: Check messages from the
SQL ServerSent rows 0
3. I don't change anything in my program, but I change field type from
nvarchar(3) to nvarchar(4) and then the same program runs well:
sasha at lt:~/$ ./test_nvarchar
Sent rows 1
I guess the problem is that FreeTDS fills incorrectly "max field length"
when sending NVARCHAR data. It should be "max field length"=6 for field
nvarchar(3), but FreeTDS set "max field length"=3 and then mssql returns
error "Unicode data is odd byte size for column".
If I change
int fOK = bcp_bind (dbproc, nvarchar, 0, 2, NULL, 0, SYBVARCHAR, 1);
to
int fOK = bcp_bind (dbproc, nvarchar, 0, 2, NULL, 0, SYBNVARCHAR, 1);
where
SYBNVARCHAR = 103, /* 0x67 */
then internal FreeTDS error happens:
sasha at alarkin-lt:~/Kaspersky/source/input_statistics/4larkin/bulk_insert$
./test_nvarchar
test_nvarchar: bcp.c:2474: _bcp_get_col_data: Assertion
`converted_data_size > 0' failed.
err_handler: Msg 20053, Level 4err_handler: Requested data conversion
does not exist
Could you please help me to fix this bug, because my boses tells me that
I must fix it in someway, because they don't want to change the
nvarchar(255) to nvarchar(256) in DataBase and possibly they would
prefer to stretch me in debugging FreeTDS code instead of such a simple
solution.
Also it is interesting for me: if I would record the successfull sending
two chars to nvarchar(4) using tcpdump and then replay it for
nvarchar(3), then will the same bug happen or not? I guess not, because
the "max field length" would be filled correctly.
Regards,
Sasha.
sasha at chuhloma.ru
avlarkin at iname.ru
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_nvarchar.c
Type: text/x-csrc
Size: 2707 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20090701/de8790c0/attachment.bin
More information about the FreeTDS
mailing list