Bulk insert of long unicode strings

Here is the situation, please let me know if you have any tips:

..TXT files in a share at \\foo

SPROCS run daily parses of many things, including data on that share. The
other day, we encountered rows in the TXT files which looked like:

column1Row1data,column2Row1data
column1Row2data,column2Row2data

...etc..

However, column2 was about 6000 bytes of unicode. We are bulk inserting
into a table specifying nvarchar(4000). When it encounters high unicode
rows, it throws a truncation error (16).

We really need information contained in the first 200 bytes of the string in
column2. However, the errors are causing the calling SPROC to abort.
Please let me know if you have any suggestions on workarounds for this
situation. Ideally, we would only Bulk Insert a sub-section of column2 if
possible.

Thanks!
/TyTy (tybala on the server at hotmail.com) writes:
> Here is the situation, please let me know if you have any tips:
> .TXT files in a share at \\foo
> SPROCS run daily parses of many things, including data on that share. The
> other day, we encountered rows in the TXT files which looked like:
> column1Row1data,column2Row1data
> column1Row2data,column2Row2data
> ..etc..
> However, column2 was about 6000 bytes of unicode. We are bulk inserting
> into a table specifying nvarchar(4000). When it encounters high unicode
> rows, it throws a truncation error (16).
> We really need information contained in the first 200 bytes of the
> string in column2.

You can use a format file like this one:

8.0
3
1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
2 SQLNCHAR 0 0 "," 0 dummy ""
3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS

Here you defined the host file to have three fields: the first is a
200-character long fixed length field, the second is closed by a ,
and the third field is close by end-of-line. By specifying a 0 in
the sixth column in the format file for the second field, you specify
that this field is is not be imported into SQL Server.

You may want to change the collation what fits with the collation you
use in your database.

Note that this only works if all occurrances of the first field is
more than 200 characters. Would there be a record with a shorter
length of this field, it will steal characters from the second field.
(You would probably get an error when importing the file, as BCP will
not find the delimiter for the second field.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I wanted to thank you for answering my question. We successfully
implemented a variant of your solution last night. The assistance is much
appreciated.

/Ty

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9401F28A16A1CYazorman@.127.0.0.1...
> Ty (tybala on the server at hotmail.com) writes:
> > Here is the situation, please let me know if you have any tips:
> > .TXT files in a share at \\foo
> > SPROCS run daily parses of many things, including data on that share.
The
> > other day, we encountered rows in the TXT files which looked like:
> > column1Row1data,column2Row1data
> > column1Row2data,column2Row2data
> > ..etc..
> > However, column2 was about 6000 bytes of unicode. We are bulk inserting
> > into a table specifying nvarchar(4000). When it encounters high unicode
> > rows, it throws a truncation error (16).
> > We really need information contained in the first 200 bytes of the
> > string in column2.
> You can use a format file like this one:
> 8.0
> 3
> 1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
> 2 SQLNCHAR 0 0 "," 0 dummy ""
> 3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS
> Here you defined the host file to have three fields: the first is a
> 200-character long fixed length field, the second is closed by a ,
> and the third field is close by end-of-line. By specifying a 0 in
> the sixth column in the format file for the second field, you specify
> that this field is is not be imported into SQL Server.
> You may want to change the collation what fits with the collation you
> use in your database.
> Note that this only works if all occurrances of the first field is
> more than 200 characters. Would there be a record with a shorter
> length of this field, it will steal characters from the second field.
> (You would probably get an error when importing the file, as BCP will
> not find the delimiter for the second field.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

BULK INSERT from secured share

Hello,
I am trying to use BULK INSERT to import data from a file that resides
on a shared drive. I've read many posts about this, but my situation
has a slight wrinkle (or two). The network people here have granted one
account that has access to this shared drive and I can map to that
drive using that user name and password:
NET USE X: \\PCName\Share password /user:username.
When run this command through a job I can then access the files through
DTS. Of course, for BULK INSERT it uses the SQL Server service account,
which is different from the SQL Server Agent service account, so it
can't access that drive (even as a UNC) because it has been mapped by a
different user. Am I correct so far?
I suspect that if I could use xp_cmdshell that I could map it with that
and the share would be available through BULK INSERT. I'm going to set
up a test on a local box to test this just for my own knowledge, but of
course on the production server xp_cmdshell is forbidden.
Possible solutions that I've come up with are:
1. Convince the network people to grant access to that drive to the SQL
Server service account (wish me LOTS of luck on that one
2. Convince the SQL Server guys that they should allow xp_cmdshell (ok,
that is impossible no matter how much luck you wish me)
3. Copy the files to the local box using a job (I'd like to avoid that
if possible)
Any other ideas?
Oh yeah, this is SQL Server 2000 on a Windows 2003 server. I don't
really have any access to the OS of the server and even my access to
the SQL side of things is pretty limited.
Thanks!
-Tom.You might be able to make use of OPENROWSET to read the text file,
instead of using DTS.|||Tracy McKibben wrote:
> You might be able to make use of OPENROWSET to read the text file,
> instead of using DTS.
Thanks, I'll look into that. Any idea on how I would set up the
connection string? I'm not familiar with doing a "connect as" through
an OLE DB provider for text files.
Thanks,
-Tom.|||Offhand, no, I'd have to look around for some examples - lots of them
on the web.
I don't think you'll need to do any sort of "connect as". You should
be able to specify the UNC path to the text file, and when you run the
code from an Agent job, it will run (and connect) under the context of
the SQL Agent user.

Bulk insert from file share

Hi - I'm trying to run a bulk insert from a file that exists on a
network share, but I'm getting the error "Could not bulk insert
because file '\\server\tester.txt' could not be opened. Operating
system error code 5(Access is denied.)".
I'm running the bulk insert command from Management Studio on the SQL
server console. The SQL server and the file server are both members
of the same domain. I'm logged in with a domain account that is in
the Administrators group on both machines. The SQL Server service is
also using a domain account that is in the Administrators group on
both machines. I can access the share and the file through Explorer
with no problem, and the same is true if I log in manually with the
SQL Server service account.
There's no reason why I should be getting the access denied message,
regardless of which account it's using. I can't find anything on the
Microsoft's site, but plenty of other people have complained about the
same symptom, and this sure feels like a bug. Am I missing something?
Thanks, S.On Oct 19, 2:47 pm, stavros <stav...@.mailinator.com> wrote:
> Hi - I'm trying to run a bulk insert from a file that exists on a
> network share, but I'm getting the error "Could not bulk insert
> because file '\\server\tester.txt' could not be opened. Operating
> system error code 5(Access is denied.)".
> I'm running the bulk insert command from Management Studio on the SQL
> server console. The SQL server and the file server are both members
> of the same domain. I'm logged in with a domain account that is in
> the Administrators group on both machines. The SQL Server service is
> also using a domain account that is in the Administrators group on
> both machines. I can access the share and the file through Explorer
> with no problem, and the same is true if I log in manually with the
> SQL Server service account.
> There's no reason why I should be getting the access denied message,
> regardless of which account it's using. I can't find anything on the
> Microsoft's site, but plenty of other people have complained about the
> same symptom, and this sure feels like a bug. Am I missing something?
> Thanks, S.
That will teach me to post late on a Friday. Refreshing the thread
here, anyone have info on this?|||I'm having exactly the same problem.
I have looked around and found that i apparently needed SQL to allow remote
connections via TCP\IP - which i've granted.
I've also granted "everyone" full control on the file i'm trying to access.
and still it doesn't work
I have two steps in a sql job... first is bulk insert the file - second is
move the file with a vbscript... the second step works fine, but bulk insert
fails.
dont' know what the problem is...
"stavros" wrote:
> On Oct 19, 2:47 pm, stavros <stav...@.mailinator.com> wrote:
> > Hi - I'm trying to run a bulk insert from a file that exists on a
> > network share, but I'm getting the error "Could not bulk insert
> > because file '\\server\tester.txt' could not be opened. Operating
> > system error code 5(Access is denied.)".
> >
> > I'm running the bulk insert command from Management Studio on the SQL
> > server console. The SQL server and the file server are both members
> > of the same domain. I'm logged in with a domain account that is in
> > the Administrators group on both machines. The SQL Server service is
> > also using a domain account that is in the Administrators group on
> > both machines. I can access the share and the file through Explorer
> > with no problem, and the same is true if I log in manually with the
> > SQL Server service account.
> >
> > There's no reason why I should be getting the access denied message,
> > regardless of which account it's using. I can't find anything on the
> > Microsoft's site, but plenty of other people have complained about the
> > same symptom, and this sure feels like a bug. Am I missing something?
> >
> > Thanks, S.
> That will teach me to post late on a Friday. Refreshing the thread
> here, anyone have info on this?
>