Skip to Content.
Sympa Menu

freetds - Re: [freetds] Having trouble doing a BULK INSERT

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Wes Gamble <weyus AT att.net>
  • To: konrad AT jax01.payplus.com, FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Having trouble doing a BULK INSERT
  • Date: Mon, 06 Nov 2006 18:48:13 -0600

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@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






Archive powered by MHonArc 2.6.24.

Top of Page