[freetds] Having trouble doing a BULK INSERT

Konrad J Hambrick konrad at jax01.payplus.com
Mon Nov 6 19:17:49 EST 2006


Wes --

Since the NULL PKey field is the last field in
your data, could you be having a DOS (CRLF) vs
*IX (LF) Line Ending issue ?

I thought you said you were able to load a 2-col
test table ...

-- kjh


Wes Gamble wrote:
> Fiddling with CONCAT_NULL_YIELDS_NULL doesn't seem to help.
> 
> [root at exmit2 upload_list_data]# tsql -H DBServe -p 1433 -U webuser -P 
> webusercb
> locale is "en_US"
> locale charset is "ISO-8859-1"
> 1> SET CONCAT_NULL_YIELDS_NULL OFF
> 2> go
> 1> BULK INSERT FAXNET FROM 'D:\FaxServe\upload_list_data\TTT.upload' 
> WITH (FIELDTERMINATOR='|')
> 2> go
> Msg 4864, Level 16, State 1, Server DBSERVE, Line 1
> Bulk insert data conversion error (type mismatch) for row 1, column 43 
> (UniqueID).
> (return status = -6)
> 1> SET CONCAT_NULL_YIELDS_NULL ON
> 2> BULK INSERT FAXNET FROM 'D:\FaxServe\upload_list_data\TTT.upload' 
> WITH (FIELDTERMINATOR='|')
> 3> go
> Msg 4864, Level 16, State 1, Server DBSERVE, Line 1
> Bulk insert data conversion error (type mismatch) for row 1, column 43 
> (UniqueID).
> (return status = -6)
> 
> WG
> 
> Wes Gamble wrote:
> 
> 
>>James,
>>
>>Interesting.  Is OSQL == Query Analyzer?  I assume so.
>>
>>Looks like the odd option out on the Linux box is 
>>"CONCAT_NULL_YIELDS_NULL" (value of 256) - which sounds promising.
>>
>>Can I modify these options for just my session or are they database wide?
>>
>>Here is some preliminary data:
>>
>> 
>>
>>
>>>When you connect with an ODBC client, Microsoft servers automatically
>>>set certain connection options.  Try these commands on your Windows box:
>>>
>>>	$ osql -E -S yourserver -Q"select @@options" (I get 5176)
>>>	$ isql -E -S yourserver -Q"select @@options" (I get 0)
>>>
>>>
>>>
>>>   
>>>
>>
>>I get the same values.
>>
>> 
>>
>>
>>>Then try:
>>>
>>>$ printf "select @@options\ngo\n" | tsql -Smpquant -U$U -P$P
>>>locale is "C"
>>>locale charset is "646"
>>>1> 2>
>>>5432
>>>
>>>
>>>   
>>>
>>
>>I also get 5432 - I get locale of "en-US" and charset of "ISO-8859-1" 
>>however
>>
>> 
>>
>>
>>>$ printf "select @@options\ngo\n" | bsqldb -Smpquant -U$U -P$P
>>>
>>>-----------
>>>        0
>>>1 rows affected
>>>
>>>
>>>   
>>>
>>
>>Same
>>
>> 
>>
>>
>>>$ printf "select @@options\ngo\n" | sqsh -Smpquant -U$U -P$P
>>>
>>>-----------
>>>      5432
>>>
>>>
>>>
>>>   
>>>
>>
>>I do not have sqsh on my box.
>>
>> 
>>
>>
>>>The unixODBC isql also returns 5432.  
>>>
>>>
>>>
>>>   
>>>
>>
>>Same.
>>
>>WG
>>_______________________________________________
>>FreeTDS mailing list
>>FreeTDS at lists.ibiblio.org
>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> 
>>
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 


More information about the FreeTDS mailing list