[freetds] Having trouble doing a BULK INSERT

Wes Gamble weyus at att.net
Mon Nov 6 19:48:13 EST 2006


Could be.  Although i've tried to "windows-ify" the file by adding 
CTRL-Ms to the end.

As it turns out though, I've decided to go with a prepared statement + 
loop insert approach, since this turns out to be much faster than a bulk 
insert anyway.

Thanks,
Wes

Konrad J Hambrick wrote:

>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
>>
>>    
>>
>_______________________________________________
>FreeTDS mailing list
>FreeTDS at lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>  
>


More information about the FreeTDS mailing list