Bulk insert from netware problems after applying SP4

I'm running MSSQL 2K on Win 2k and just upgraded SQL Server from SP3
to SP4 (that's when my problem started). I have a procedure that bulk
inserts from a text file that is located on a Netware 4.11 file server.
The procedure actually references the file using the UNC path to a
share on a NT 4 server that is connected to the novel server using
gateway services for NetWare. Now when it tries to reference the file I
get:

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\NtServer\Netware Share\file.txt'
could not be opened. Operating system error code 5(Access is denied.).

I've tried running the process as a domain administrator and get the
same results.

I can run these commands and they work as expected

master..xp_cmdshell 'dir \\NtServer\Netware Share\file.txt'
master..xp_cmdshell 'type \\ NtServer\Netware Share\file.txt'

Also if I Bulk insert the file from the local drive of the same NT
server it works fine i.e.:
BULK INSERT #imp FROM \\NtServer\d$\file.txt'

Any thoughts would be appreciated.(gchavez@.aspenres.com) writes:
> I'm running MSSQL 2K on Win 2k and just upgraded SQL Server from SP3
> to SP4 (that's when my problem started). I have a procedure that bulk
> inserts from a text file that is located on a Netware 4.11 file server.
> The procedure actually references the file using the UNC path to a
> share on a NT 4 server that is connected to the novel server using
> gateway services for NetWare. Now when it tries to reference the file I
> get:
> Server: Msg 4861, Level 16, State 1, Line 1
> Could not bulk insert because file '\\NtServer\Netware Share\file.txt'
> could not be opened. Operating system error code 5(Access is denied.).
> I've tried running the process as a domain administrator and get the
> same results.

Make sure that the account under which the SQL Server service runs has
access to the file on the share. You can check which account this is
by right-clicking My Computer, Manage, find Services, then find the
MS SQL Server server, right-click, Properties and then the Log On
tab. Note that users such as Local System do not have network access
at all.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

bulk copy(batch insert) is failed when the mssql backup the database

Hi All,
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie

bulk copy(batch insert) is failed when the mssql backup the database

Hi All
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie