BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

Does SQL CLR engine call UDT Parse(...) for BULK INSERT? If not, must we provide the UDT value in binary like 0x11222ccffdedd, not a string?It has been too long nobody answer this question, so bring it up again. Also turn the question in a simple way:

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Target table:
CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLBINARY"/>
</ROW>
</BCPFORMAT>

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.|||

Hi, Pkv

I'm not sure your problem is the same as mine. What I asked and later found the solution is about the UDT bulk load, and the fundamental issue is UDT's Parse(String) method won't be called during bulk op.

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

<?xml version="1.0" encoding="windows-1250"?>
<root>
<organizace>
<cislo_subjektu>65673</cislo_subjektu>
<reference_subjektu>0000002135</reference_subjektu>
<nazev_subjektu>Martin Prudky</nazev_subjektu>
<nazev_zkraceny>PRUDKY LOUKA U LITV.</nazev_zkraceny>
<ulice>U nádra?í</ulice>
<psc>435 33</psc>
<misto>Louka u Litvínova</misto>
<ico>15137970</ico>
<dic>CZ6011151003</dic>
<uverovy_limit>200000</uverovy_limit>
<stav_limitu>172081.0000</stav_limitu>
</organizace>
<organizace>
<cislo_subjektu>67125</cislo_subjektu>
<reference_subjektu>0000003301</reference_subjektu>
<nazev_subjektu>Lesní dru?stvo obcí Ledec nad Sázavou</nazev_subjektu>
<nazev_zkraceny>LESNI DRUZSTVO LEDEC</nazev_zkraceny>
<ulice>Hnevkovice 56</ulice>
<psc>582 94</psc>
<misto>Ledec nad Sázavou</misto>
<ico>25251937</ico>
<dic>CZ25251937</dic>
<uverovy_limit>50000</uverovy_limit>
<stav_limitu>0.0000</stav_limitu>
</organizace>
<organizace>
<cislo_subjektu>67159</cislo_subjektu>
<reference_subjektu>0000004170</reference_subjektu>
<nazev_subjektu>D.D.L.Servis, s.r.o.</nazev_subjektu>
<nazev_zkraceny>D.D.L. SERVIS LOMNICE</nazev_zkraceny>
<ulice>?elechy 37</ulice>
<psc>512 51</psc>
<misto>Lomnice nad Popelkou</misto>
<ico>25291521</ico>
<dic>CZ25291521</dic>
<uverovy_limit>50000</uverovy_limit>
<stav_limitu>0.0000</stav_limitu>
</organizace>
.
.
.

It goes for next 30 000 table rows.

Here is list of errors:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (cislo_subjektu).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".|||

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('*', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('*', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

I guess you may have a misconception here, for bcp.exe or BULK INSERT, a format file can be in two formats: a text one or a XML one. But both only work for a flat data file. It seems you are binding together a XML data file with a XML format file, hoping to shred data from XML into relational, no, that won't work.

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

BULK INSERT (X)

Hi,
I have to import a file, using BULK INSERT, into a #TEMP_TABLE - the file
has the following contents/format (5 cols):
"B09Z003 ","0058-003-01 ", 57, 0," "
"B09Z005 ","0053-003-R1 ", 19, 0," "
"B09Z008 ","0054-2 ", 0, 0," "
etc. etc.
Please be aware, that not all col's use quote as text qualifiers.
I'm trying with the following formatfile, but it doesn't work:
8.0
10
1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
3 SQLCHAR 0 20 "\"," 3 COL003 ""
4 SQLCHAR 0 8 "," 4 COL004 ""
5 SQLCHAR 0 8 ",\"" 5 COL005 ""
6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
What am I doing wrong?
Also, the last col is a date (if it's not all <space> chars) in the format
12/12/2004 - could this give a problem (the /-char or data-formats) of any
kind?
Best regards
JakobDoes anyone have a clue?
Thanx
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:OVFVCGKBEHA.496@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have to import a file, using BULK INSERT, into a #TEMP_TABLE - the file
> has the following contents/format (5 cols):
> "B09Z003 ","0058-003-01 ", 57, 0," "
> "B09Z005 ","0053-003-R1 ", 19, 0," "
> "B09Z008 ","0054-2 ", 0, 0," "
> etc. etc.
> Please be aware, that not all col's use quote as text qualifiers.
> I'm trying with the following formatfile, but it doesn't work:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
> 2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
> 3 SQLCHAR 0 20 "\"," 3 COL003 ""
> 4 SQLCHAR 0 8 "," 4 COL004 ""
> 5 SQLCHAR 0 8 ",\"" 5 COL005 ""
> 6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
> What am I doing wrong?
> Also, the last col is a date (if it's not all <space> chars) in the format
> 12/12/2004 - could this give a problem (the /-char or data-formats) of any
> kind?
> Best regards
> Jakob
>|||Jakob,
This works just fine. So, what error you're getting. Also, check your format
file. You might specify more columns than what your data file has (i.e. 10
instead of 6 based on your original post).
/*
--txt.txt
"B09Z003 ","0058-003-01 ", 57, 0," "
"B09Z005 ","0053-003-R1 ", 19, 0," "
"B09Z008 ","0054-2 ", 0, 0," "
*/
/*
--fmt.fmt
8.0
6
1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
3 SQLCHAR 0 20 "\"," 3 COL003 ""
4 SQLCHAR 0 8 "," 4 COL004 ""
5 SQLCHAR 0 8 ",\"" 5 COL005 ""
6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
*/
create table #tmp(i int identity,col002 varchar(12),col003 varchar(20),col00
4
varchar(8),col005 varchar(8),col006 varchar(8))
go
bulk insert #tmp
from 'c:\txt.txt'
with(formatfile='c:\fmt.fmt')
go
select * from #tmp
go
drop table #tmp
go
-oj
http://www.rac4sql.net
""Sokrates"" <somebody@.somewhere.earth> wrote in message
news:ePUpm8aBEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Does anyone have a clue?
> Thanx
> Jakob
> ""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
> news:OVFVCGKBEHA.496@.TK2MSFTNGP12.phx.gbl...
>|||THANX!
It works now!
BR
Jakob
"oj" <nospam_ojngo@.home.com> skrev i en meddelelse
news:%23NHtjFbBEHA.1544@.TK2MSFTNGP09.phx.gbl...
> Jakob,
> This works just fine. So, what error you're getting. Also, check your
format
> file. You might specify more columns than what your data file has (i.e. 10
> instead of 6 based on your original post).
> /*
> --txt.txt
> "B09Z003 ","0058-003-01 ", 57, 0," "
> "B09Z005 ","0053-003-R1 ", 19, 0," "
> "B09Z008 ","0054-2 ", 0, 0," "
> */
> /*
> --fmt.fmt
> 8.0
> 6
> 1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
> 2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
> 3 SQLCHAR 0 20 "\"," 3 COL003 ""
> 4 SQLCHAR 0 8 "," 4 COL004 ""
> 5 SQLCHAR 0 8 ",\"" 5 COL005 ""
> 6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
> */
> create table #tmp(i int identity,col002 varchar(12),col003
varchar(20),col004
> varchar(8),col005 varchar(8),col006 varchar(8))
> go
> bulk insert #tmp
> from 'c:\txt.txt'
> with(formatfile='c:\fmt.fmt')
> go
> select * from #tmp
> go
> drop table #tmp
> go
>
>
> --
> -oj
> http://www.rac4sql.net
>
> ""Sokrates"" <somebody@.somewhere.earth> wrote in message
> news:ePUpm8aBEHA.2600@.TK2MSFTNGP09.phx.gbl...
file
format
any
>

Bulk Insert (type mismatch) on datetime field containing NULL

Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>

bulk insert (again and again..)

Hi !
sorry to bother you again with that topic..but...
Bulk Insert inserts null value when it finds null string in my source
file to load...
is there any way to prevent this'
I would like to get null fields instead of NULL value in my base..
thanks again :)
++
VinceHi
You can do a post update of
UPDATE TABLE
SET Col1 = NULLIF('NULL')
or you should look at the source try to generate the file differently.
John
"Vince .>" <vincent@.<remove> wrote in message
news:38qk4150iaddn8ha1k7njss01466u2u8kv@.
4ax.com...
> Hi !
> sorry to bother you again with that topic..but...
> Bulk Insert inserts null value when it finds null string in my source
> file to load...
> is there any way to prevent this'
> I would like to get null fields instead of NULL value in my base..
>
> thanks again :)
> ++
> Vince

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.

|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.

|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.
|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT & DIRTY BUFFERS

Hi all

Using SQL 2000 MSDE

I'm bulk inserting about 3.200.000 records into a table

unfortunately all memory dissapears and never returns the dirty buffers count goes up to 48000 approx.

any ideas on how to rectify this ..... ?sqlserver is designed to take up as much as memory and only releases it back if the system needs it. If you must, restarting sqlserver will force the release.
Normally, I wouldn't want to pump that much data into the db in one shot. Rather, I would do it in batches. Please take a look at -b option of bcp or ROWS_PER_BATCH of bulk insert.|||Thanks for that
I have already figured out that restarting the service will do the trick .....
but it is a live database with many users .... (they will not thank me for doing such things)
thanks anyway ... i'll keep searching

BULK INSERT - Urgent

I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........This is a multi-part message in MIME format.
--020900050805040007080207
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
One thing I noticed is that you use "\r\n" as your row terminator in
your DTS package but only "\n" in your BULK INSERT statement. Perhaps
every row in your input file doesn't have a "\n" to terminate but only a
"\r" (from memory, although it's been a while so I could be wrong,
typically text files generated with Unix/Linux tools only use a "\r"
(ASCII 13) to terminate a line). I don't know why \r\n would work in
the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT statement
(like "\n", "\r\n", "\r").
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Sanjay wrote:
>I have a tab delimeted file which i BCP into a temp table using BULK INSERT
>When i use DTS Transform data task i get all 7000 records in the table
>In DTS data transform i choose Column Delimeter is a tab ann row delimeter
>is {CR}{LF}
>When i do Bulk insert in using command below a lot of these records are
>combined with previous records dud to improper row terminators on some records
>How do i make this work in BULK INSERT same way as it works in DTS
>BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
>Pls help..........
>
>
--020900050805040007080207
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>One thing I noticed is that you use "\r\n" as your row terminator
in your DTS package but only "\n" in your BULK INSERT statement.Â
Perhaps every row in your input file doesn't have a "\n" to terminate
but only a "\r" (from memory, although it's been a while so I could be
wrong, typically text files generated with Unix/Linux tools only use a
"\r" (ASCII 13) to terminate a line). I don't know why \r\n would work
in the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT
statement (like "\n", "\r\n", "\r").</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Sanjay wrote:
<blockquote cite="midBC983130-5E18-4E77-92BB-0FFC20421FAA@.microsoft.com"
type="cite">
<pre wrap="">I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........
</pre>
</blockquote>
</body>
</html>
--020900050805040007080207--|||I have gotten this bulk insert statement to work and all you would have to do
is change the fieldterminator to '\t'. Notice the file I was working with
was CSV and it had "," for field termination. You can try using a FORMAT
file parameter but so far I have been unsuccessful in getting that to work.
You can make a format file from the table using the following code:
EXEC master.dbo.xp_cmdshell 'BCP sqlsrv.dbo.SystemTemp FORMAT -Usa
-Ppassword -N -fc:\TBL_Format.fmt'
Which will make you a format file of the table you are trying to bulk import
into.
---
BULK INSERT [dbo].[SystemTemp]
FROM 'c:\SystemTempIn.csv'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '","',
FIRSTROW = 2,
ROWTERMINATOR = '"\n'
)
"Sanjay" wrote:
> I have a tab delimeted file which i BCP into a temp table using BULK INSERT
> When i use DTS Transform data task i get all 7000 records in the table
> In DTS data transform i choose Column Delimeter is a tab ann row delimeter
> is {CR}{LF}
> When i do Bulk insert in using command below a lot of these records are
> combined with previous records dud to improper row terminators on some records
> How do i make this work in BULK INSERT same way as it works in DTS
> BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
> Pls help..........
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk Insert - Problem with characters

Hi,
I am using bulk insert to load data from a # delimited text file into SQL
Server database table. There are some characters in the file like
"FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER B
AYER
20L". I need to load the data as it is in the file. I am using the following
code from a stored procedure.
SELECT @.SSQL='BULK INSERT ' +@.tblName+
' FROM ''' + @.in_file_name + '''' +
' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR =
''' + @.RowTer + ''', CODEPAGE = ''850'')'
EXECUTE(@.SSQL)
I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
hold the above value. Its nvarchar(255).
Could you please help me out in this. This is very urgent. Appreciate your
timely help.
Many Thanks.Add "DATAFILETYPE='widechar'" to your statement.
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLSSIGDNGER BAYER 20L" is getting converted to "FL?SSIGD?NGER BAYER
> 20L". I need to load the data as it is in the file. I am using the
> following
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
> =
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||I have tried "DATAFILETYPE='widechar'" but it doesn't help.
"oj" wrote:

> Add "DATAFILETYPE='widechar'" to your statement.
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
>
>|||This quick example is working fine here. I will need more info/data in order
to help.
use Northwind
go
create table test(i int, j nvarchar(255) collate
SQL_Latin1_General_CP1_CI_AS)
go
insert test select 1,N'FLSSIGDNGER BAYER 20L'
go
declare @.sql nvarchar(1000)
set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
+'" -T -w -t"|"'
print @.sql
exec master..xp_cmdshell @.sql
truncate table test
set @.sql='bulk insert test
from ''c:\test.out''
with(fieldterminator=''|'',datafiletype=
''widechar'')'
print @.sql
exec(@.sql)
select * from test
go
drop table test
go
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>I have tried "DATAFILETYPE='widechar'" but it doesn't help.
> "oj" wrote:
>|||The file is created by a third-party program, which among other columns
contains the description column with these characters. We are reading from
the file (as received) using bulk insert. Here is a sample record. Could
there be a problem with how the text file has been created. following is one
of the records in the file.
CH#CH030#0030#5105949253#0001#20050113#0
00000000000020379#FLüSSIGDüNGER
BAYER 20L
CHF#0020379#0301#BI-WDI- CH#BI_LOAD##0000309132#DE#AGLUKON#Düsse
ldorf
11#40549##Postfach 190 343#0000309132#DE#AGLUKON#Düsseldorf
11#40549##Postfach 190 343# 3840#L# 0000.00#EUR# 0000.04#CHF#627#S.
DERUNGS#20050401#050040# 000.00# 000#CHF
"oj" wrote:

> This quick example is working fine here. I will need more info/data in ord
er
> to help.
> use Northwind
> go
> create table test(i int, j nvarchar(255) collate
> SQL_Latin1_General_CP1_CI_AS)
> go
> insert test select 1,N'FLüSSIGDüNGER BAYER 20L'
> go
> declare @.sql nvarchar(1000)
> set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
> +'" -T -w -t"|"'
> print @.sql
> exec master..xp_cmdshell @.sql
> truncate table test
> set @.sql='bulk insert test
> from ''c:\test.out''
> with(fieldterminator=''|'',datafiletype=
''widechar'')'
> print @.sql
> exec(@.sql)
> select * from test
> go
> drop table test
> go
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>
>|||Codepage = 65001 solves my problem.
"SQL Novice" wrote:

> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER
BAYER
> 20L". I need to load the data as it is in the file. I am using the followi
ng
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
=
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||Thanks for the update.
-oj
"SQL Novice" <SQLNovice@.discussions.microsoft.com> wrote in message
news:7C34DF98-97A9-46B5-B7A3-208CB5B1347B@.microsoft.com...
> Codepage = 65001 solves my problem.
> "SQL Novice" wrote:
>

BULK INSERT - MISSING ROWS

Hello

Iam using:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

I am using BULK INSERT to import some pipe-delimited flat files into a database.
I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write).
Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement:
BULK INSERT
tempHISTORY
FROM 'C:\TEMPHISTORY.TXT'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\r'
)

NB: The first row in the file is a header row.

This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't.
I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file.
I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.

I have just made a very odd discovery regarding this data:

The final value of the final column of the file does not have a value (blank).

eg
0|0|0|0|0|0|0|1 <- PENALTIMATE LINE
0|0|0|0|0|0|0| <- FINAL LINE

If I give the final line's final column a value, it will import. If the value of that final column in the final line is blank, it discards the line, despite the fact that the destination column allows nulls!

Please help!

|||I have found that if a place a blank space on the end of the last line of each file, the import will always work.

This is not an ideal solution but it works.