Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GO
Change the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)
|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>
|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:

Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GOChange the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
>

bulk load change in 2005?

I have a dts package that does some bulk load operations without a problem in 2000. When trying to convert them to SISS or trying to run the same dts package against the a copy of the database (in 2000 mode) running on a 2005 server I get Bulk load data conversion errors (truncation) for row xxxx....

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:

Has the bulk load process changed in 2005?

Yes, there have been certain changes in the bulk load that may cause seemingly fine SQL Server 2000 scenarios to fail when they run against SQL Server 2005: the engine now enforces stricter data purity checks and additional physical consistency checks for the input data streams both in BCP and BULK INSERT/OPENROWSET.|||

Is there any way to relax those checks? I was under the assumption that running a database in 2000 mode would allow backward compatibility. I am under a huge time crunch now, and at this point my only contingency plan is to keep a 2000 server up for this specific database.

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

bulk load change in 2005?

I have a dts package that does some bulk load operations without a problem in 2000. When trying to convert them to SISS or trying to run the same dts package against the a copy of the database (in 2000 mode) running on a 2005 server I get Bulk load data conversion errors (truncation) for row xxxx....

Has the bulk load process changed in 2005?

My Sproc is using a bulk insert command with a fmt file..

Even when you are using SSIS for this; I think this question is better asked in the databse engine forum...|||

Daveko wrote:

Has the bulk load process changed in 2005?

Yes, there have been certain changes in the bulk load that may cause seemingly fine SQL Server 2000 scenarios to fail when they run against SQL Server 2005: the engine now enforces stricter data purity checks and additional physical consistency checks for the input data streams both in BCP and BULK INSERT/OPENROWSET.|||

Is there any way to relax those checks? I was under the assumption that running a database in 2000 mode would allow backward compatibility. I am under a huge time crunch now, and at this point my only contingency plan is to keep a 2000 server up for this specific database.

Any assistance would be appreciated..

|||Unfortunately, there is no 'officially' documented way of relaxing those checks. Please contact CSS - they might be able to assist you.|||

What is CSS?

I have cleaned up my data and got the import to run successfully.

|||CSS stands for Customer Service and Support. It used to be called PSS.

Bulk Insert with FileName Variable

I am trying to run a bulk insert in a SP using the filename as a variable:
BULK INSERT #tblTemp
From '+@.FilePath+'
WITH
(ROWTERMINATOR = '\n')
My procedure is not recognizing @.Filepath as a variable file name. Is there
a way to do this without resorting to full dynamic SQL?
Larry Menzin
American Techsystems Corp.> My procedure is not recognizing @.Filepath as a variable file name. Is
> there
> a way to do this without resorting to full dynamic SQL?
No, BULK INSERT command is not parameterized that way.|||Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eeH4hL1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> No, BULK INSERT command is not parameterized that way.
>|||I think Larry knows how BULK INSERT works when you hard-code everything, the
problem is that @.FilePath is variable/unknown (in your example, it is
hard-coded as c:\newpubs.dat) .

> Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )

Bulk Insert with another outstanding result set should be run with XACT_ABORT on

All,

I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.

When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.

Thanks

Update on this - I was able to get around this problem by switching to using native SQL Server transactions. However, it still puzzles me that I can't use DTC instead. Please, any help on this problem would be greatly appreciated. Just to add a little more information - there is a SQL Server 2005 instance (destination) and a SQL Server 2000 instance (source) involved. Microsoft? Any answer on this?|||I'm getting the same problem.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Bulk Insert with another outstanding result set should be run with XACT_ABORT on."

What does it mean, 'another outstanding resultset'?

I have a bulk insert updating table A (multicast also creates data to update table A history)

This is the first task in a series of 4 with the other 3 occuring directly after the first one, all doing a similar thing. i.e. Bulk insert to table B and table B historysql

Bulk Insert with another outstanding result set should be run with XACT_ABORT on

All,

I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.

When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.

Thanks

Update on this - I was able to get around this problem by switching to using native SQL Server transactions. However, it still puzzles me that I can't use DTC instead. Please, any help on this problem would be greatly appreciated. Just to add a little more information - there is a SQL Server 2005 instance (destination) and a SQL Server 2000 instance (source) involved. Microsoft? Any answer on this?

BULK INSERT STATEMENT PROBLEM... ^^

Hi all,

I want to run BULK INSERT command from ASP.NET.. But, I get Error "You dont have permission to use BULK INSERT Command...". And i try to find out the solution from Internet and a lot of people say that I have to create user under Sysadmin or BULKadmin role.

But I dont see Sysadmin and BULKadmin from my MSSQL Enterprise manager. What I see is only Admin and Sys... Any solution??

Thanks

Suigion

Hi,

use sp_addsrvrolemember to add

SysAdmin and BulkAdmin are types of fixed server roles. You can find them under Security, Server Roles node in EM.

Hope this helps you

Bulk Insert Runs Twice!

I have a simple Bulk Insert statement I want to run in a stored procedure.
Here it is:
Set @.bulk_cmd = 'BULK INSERT MyTable
FROM ''C:\mydump.txt''
WITH (FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)
It runs fine but does the insert twice. There are 35 records in the file,
you can watch it load 35 records twice. The records are field terminated wit
h
a tab, and row terminated with CR\LF. I have tried numerous other terminatio
n
characters and all does the same. The actual txt file was created using DTS
from a table.
I am using SQL Server 2000.
Any ideas would be appreciated.
MGAre you checking the target table to confirm that it really is inserting the
rows twice, or do you think that's what is happening because you see this in
the messages:
(35 rows affected)
(35 rows affected)
You see it twice because the top message is from the BULK INSERT inside the
sp, and the bottom is the sp reported how many rows where affected. If you
want to only see the message once, put SET NOCOUNT ON at the top of your sp.
"mgcap" wrote:

> I have a simple Bulk Insert statement I want to run in a stored procedure.
> Here it is:
> Set @.bulk_cmd = 'BULK INSERT MyTable
> FROM ''C:\mydump.txt''
> WITH (FIELDTERMINATOR = ''\t'',
> ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
> EXEC(@.bulk_cmd)
> It runs fine but does the insert twice. There are 35 records in the file,
> you can watch it load 35 records twice. The records are field terminated w
ith
> a tab, and row terminated with CR\LF. I have tried numerous other terminat
ion
> characters and all does the same. The actual txt file was created using DT
S
> from a table.
> I am using SQL Server 2000.
> Any ideas would be appreciated.
> MG|||Mark,
Many thanks. I feel like a goof. That was it. The real issue was that I was
running the routine numerous times, not truncing the table every time, then
seeing more record in the table that 35. That along with the double message
threw me. I should have known better.
Thanks,
Mark
"Mark Williams" wrote:
> Are you checking the target table to confirm that it really is inserting t
he
> rows twice, or do you think that's what is happening because you see this
in
> the messages:
> (35 rows affected)
> (35 rows affected)
> You see it twice because the top message is from the BULK INSERT inside th
e
> sp, and the bottom is the sp reported how many rows where affected. If you
> want to only see the message once, put SET NOCOUNT ON at the top of your s
p.
>
> --
>
> "mgcap" wrote:
>

Bulk Insert Question

When using bulk insert to populate a table from a .txt
file, what context to access that file does bulk insert
run under? Does it run under the account that SQL Server
runs under or does it run under the authenticated user?
Basically, we have the need to put a small txt file that
will be used for a report by senior management in a
secured directory since it contains confidental info.
Then a report will be generated periodically using the
info in this file and some other info in SQL Server. What
has been proposed is to bulk insert the txt file to a temp
table, run the report and drop the temp table. DBA's
(myself) can't have access to the txt file so if bulk
insert uses the sqlserver acount to access the txt file,
it will not fit our needs since a DBA could bulk insert
this file if they wanted to. However, if the
authenticated user running the bulk insert is used to
access the txt file, then it will work because the txt
file will be in a location accessable by only those who
are allowed to see it.
Thanks for your help,
Van
Provided the login exists in the bulkadmin or sysadmin roles, then the Bulk
Insert statement will run under the context of the SQL Server Service
account. To provide a distinction between the windows SQL Server Service
account - which you know - and a separate Windows account having access to
the file, you could use a scheduled DTS package (BulkInsert task). This
would run under the context of the SQL Server agent, to which you could
assign a different account. Provided this account is in the sysadmin role
then it would be ok.
HTH,
Paul Ibison

Bulk Insert Question

When using bulk insert to populate a table from a .txt
file, what context to access that file does bulk insert
run under? Does it run under the account that SQL Server
runs under or does it run under the authenticated user?
Basically, we have the need to put a small txt file that
will be used for a report by senior management in a
secured directory since it contains confidental info.
Then a report will be generated periodically using the
info in this file and some other info in SQL Server. What
has been proposed is to bulk insert the txt file to a temp
table, run the report and drop the temp table. DBA's
(myself) can't have access to the txt file so if bulk
insert uses the sqlserver acount to access the txt file,
it will not fit our needs since a DBA could bulk insert
this file if they wanted to. However, if the
authenticated user running the bulk insert is used to
access the txt file, then it will work because the txt
file will be in a location accessable by only those who
are allowed to see it.
Thanks for your help,
VanProvided the login exists in the bulkadmin or sysadmin roles, then the Bulk
Insert statement will run under the context of the SQL Server Service
account. To provide a distinction between the windows SQL Server Service
account - which you know - and a separate Windows account having access to
the file, you could use a scheduled DTS package (BulkInsert task). This
would run under the context of the SQL Server agent, to which you could
assign a different account. Provided this account is in the sysadmin role
then it would be ok.
HTH,
Paul Ibison

Bulk Insert Question

When using bulk insert to populate a table from a .txt
file, what context to access that file does bulk insert
run under? Does it run under the account that SQL Server
runs under or does it run under the authenticated user?
Basically, we have the need to put a small txt file that
will be used for a report by senior management in a
secured directory since it contains confidental info.
Then a report will be generated periodically using the
info in this file and some other info in SQL Server. What
has been proposed is to bulk insert the txt file to a temp
table, run the report and drop the temp table. DBA's
(myself) can't have access to the txt file so if bulk
insert uses the sqlserver acount to access the txt file,
it will not fit our needs since a DBA could bulk insert
this file if they wanted to. However, if the
authenticated user running the bulk insert is used to
access the txt file, then it will work because the txt
file will be in a location accessable by only those who
are allowed to see it.
Thanks for your help,
VanProvided the login exists in the bulkadmin or sysadmin roles, then the Bulk
Insert statement will run under the context of the SQL Server Service
account. To provide a distinction between the windows SQL Server Service
account - which you know - and a separate Windows account having access to
the file, you could use a scheduled DTS package (BulkInsert task). This
would run under the context of the SQL Server agent, to which you could
assign a different account. Provided this account is in the sysadmin role
then it would be ok.
HTH,
Paul Ibison

Bulk insert Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
Kiran
I have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sho...41512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
sql

Bulk insert Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sh...141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan

Bulk insert Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan

BULK Insert Permissions question

To run a BULK INSERT, Sql BOL says I need to have

"INSERT and ADMINISTER BULK OPERATIONS permissions..."

If I am dbo of a database is that sufficient (rights wise) to bulk insert into that database?

TIA,

Barkingdog

Hey Bill,

If you run this operation under that account then you should have no problems.

Keep us posted with how you are getting on.

pace

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 not working from asp.netpage

Hi,

I am tryin to run the store procedure which has bulk insert command.when i run the code,it says you don't have permission to use bulk insert command. This query/storeprocedure is running succesfully in the backend(sqlserver2000).

solution is only user in Bulkadmin role has permission for bulk insert .But i don't know how to create one and use it in asp.net.

Can anyone help me in this. ..Here is the code.

string conn_string1 =@."Data Source=SENTHIL\TEST;Initial Catalog=EMPLOYEE;Integrated Security=SSPI";

SqlConnection objconn1=

new SqlConnection(conn_string1);

objconn1.Open();

SqlCommand command1=

new SqlCommand("bulkinsert",objconn1);

command1.CommandType=CommandType.StoredProcedure;

command1.ExecuteNonQuery();

Response.Write("Stored procedure excecuted");

objconn1.Close();

thanks,

kar

In http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Requires INSERT and ADMINISTER BULKOPERATIONS permissions. Additionally, ALTER TABLE permission isrequired if one or more of the following is true:

Constraints exist and the CHECK_CONSTRAINTS option is not specified.

Note: Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

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

BULK INSERT from another server

Hi there!

I'm trying to do a BULK INSERT on my SQL Server with a file that resides on my webserver (two different locations). I am trying to run this BULK INSERT from a stored procedure, which is called from an ASP page. This does work when I'm running everything on my local machine (both SQL Server and local IIS).

When I try this, I get an error along the lines of <file> could not be opened.

When I try to send it the url, I get the following: <file> could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

I still want to keep the files wherever they are, as I don't think I can write these files from my website.

Thank you for all and any help.The only thing I can tell you is that the bulk copy operation looks at the location of the file from the perspective of the database server, not whatever remote machine you happen to be executing from. Other than that, this sounds more like a network issue.

blindman