sql server programming: Bulk insert on 2000 only works with varchar but not nvarchar -- sql server programming

RE: Bulk insert on 2000 only works with varchar but not nvarchar

Saleem Hakani
10/14/2007 1:23:03 AM

Hi Hao,

NVarchar uses twice the space for every charecter you enter. Check the size
of your data? If its more than 4000 charecters than NVarchar will fail.

--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.

[quoted text, click to view]

Re: Bulk insert on 2000 only works with varchar but not nvarchar

huohaodian NO[at]SPAM gmail.com
10/14/2007 11:02:51 AM

I have used follows

create table User (
UserID varchar(11),
UserName varchar(32)
)
go

bulk insert User from 'C:\Project\User.txt' with (formatfile = 'C:
\Project\User.fmt')
go

format file is something like follow

8.0
3
1 SQLCHAR 0 11 "" 1
UserD SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 32 "" 2 UserName
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 115 "\n" 0 Unused
SQL_Latin1_General_CP1_CI_AS

[quoted text, click to view]

Re: Bulk insert on 2000 only works with varchar but not nvarchar

huohaodian NO[at]SPAM gmail.com
10/14/2007 6:40:47 PM

[quoted text, click to view]

Thanks a lot for your time and helps. I was trying to figure out how
to attach the file for this thread, but no luck, now I could only post
the one line of source data here,
103364Cote,AER E 00002700002516 50 DDD
EEE NO RESTRICT 189894TH FLR CCH A 2.00
2.00 0.30ABC xx.xxPTO 122.8
48.0EIB 80.0 0.0CLB
3.3 3.3

Thanks again

Re: Bulk insert on 2000 only works with varchar but not nvarchar

huohaodian NO[at]SPAM gmail.com
10/14/2007 6:45:34 PM

[quoted text, click to view]

Here is the format file:

8.0
3
1 SQLCHAR 0 11 "" 1 UserD SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 32 "" 2 UserName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 115 "\n" 0 Unused SQL_Latin1_General_CP1_CI_AS

Re: Bulk insert on 2000 only works with varchar but not nvarchar

Erland Sommarskog
10/14/2007 10:32:06 PM

(huohaodian@gmail.com) writes:
[quoted text, click to view]

And where did you put the sample data file?

And I will have to admit that I don't really like "format file is something
like follows". Please make sure that the information you post here,
reproduces the issue you see. Yes, I am asking you do some work, but
you are the one who is asking for help, and I don't really feel like
trying at random to see what might be happening to you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Bulk insert on 2000 only works with varchar but not nvarchar

Uri Dimant
10/15/2007 12:00:00 AM

See this one
create table dbo.tb(c1 nvarchar(30),c2 nvarchar(30), c3 nvarchar(30))

go
/*

"P","ISSUE","Misc Material Issue"
"P","MOVE","Misc Material Move"
"J","POST","Misc Post Material"
"J","MOVE","Misc Material Move"

8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
*/
bulk insert dbo.tb
from 'c:\temp\txt.txt'
with (formatfile='c:\temp\fmt.fmt')
go
select *
from dbo.tb
go
drop table dbo.tb

[quoted text, click to view]

Re: Bulk insert on 2000 only works with varchar but not nvarchar

Erland Sommarskog
10/15/2007 7:40:41 AM

(huohaodian@gmail.com) writes:
[quoted text, click to view]

So put the file on a web site and post a link to it. When BCP uses a format
file, it handles the file as binary, so any tweak it with CR-LF etc matters.

And, please, post an entire scenario where you get the error, and where you
actually have verified that the issue occurs. If you are not prepared to
make that effort, at least I will not be prepared to make the effort to
understand what is going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.