Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Bulk insert of large files (more then 450MB) Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 5:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
Unfortunately BULK INSERT does not play nice sometimes so we have to apply a workaround. We cannot CATCH errors from BULK INSERT related to gaining exclusive access to the file straightaway. We must use a proc and a nested TRY/CATCH, i.e. two TRY/CATCH blocks and a context switch to catch one error.

A variation of Jeff's solution is the one I would go with.

I would just try loading the file straightaway and catch the exception from the inner proc call. If it is an exception about 'exclusive access' like this ...

Cannot bulk load because the file "%s" could not be opened. Operating system error code 32(The process cannot access the file because it is being used by another process.).

... then wait 5 seconds and try again.

The technique requires no additional features be enabled which helps you on the security and auditing side and while managing program flow using exceptions is generally frowned upon, checking for exclusive file access is actually one of the few scenarios where it is acceptable. How do you know if a file is ready unless you try opening it? And if you simply test for access and proceed if you get exclusive access, by the time you drop into your processing code something else could have grabbed exclusive access in between. Now, is it likely that someone will grab exclusive access after it initially your particular file becomes available? Maybe not in your scenario but it never hurts to use a known design pattern that simply works.

Something like this:

1. create new folder called C:\@
2. save the text file attached to this post to C:\@\
3. Open the text file in Excel to block others from gaining exclusive access to the file.
4. Run this SQL on a local instance:

USE tempdb;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.incoming_data')
AND type IN (N'U') )
DROP TABLE dbo.incoming_data;
GO
CREATE TABLE dbo.incoming_data (identifier VARCHAR(50));
GO

IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.load_data_inner')
AND type IN (N'P', N'PC') )
DROP PROCEDURE dbo.load_data_inner;
GO
CREATE PROC dbo.load_data_inner
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.incoming_data;

BEGIN TRY

BULK INSERT dbo.incoming_data
FROM 'c:\@\1.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =''
);

END TRY
BEGIN CATCH
-- notice this code will not be hit when an exclusive access error occurs
RAISERROR('dbo.load_data_inner CATCH',10,1) WITH NOWAIT;
END CATCH

END
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.load_data_outer')
AND type IN (N'P', N'PC') )
DROP PROCEDURE dbo.load_data_outer;
GO
CREATE PROC dbo.load_data_outer
AS
BEGIN
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN

BEGIN TRY
EXEC dbo.load_data_inner;
BREAK;
END TRY
BEGIN CATCH
RAISERROR('dbo.load_data_outer CATCH',10,1) WITH NOWAIT;

DECLARE @error_number INT = ERROR_NUMBER(),
@error_message NVARCHAR(2048)= ERROR_MESSAGE();
RAISERROR('%d',10,1,@error_number) WITH NOWAIT;
RAISERROR('%s',10,1,@error_message) WITH NOWAIT;

IF (
@error_number = 4861
AND @error_message LIKE '%Operating system error code 32(The process cannot access the file because it is being used by another process.).%'
)
BEGIN
RAISERROR('waiting for exclusive access...',10,1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';
END
ELSE
BEGIN
-- something we did not expect happened so bubble up the error, i.e. exit this proc
RAISERROR( @error_message,11,1);
END
END CATCH

END
END
GO

EXEC dbo.load_data_outer
GO

SELECT *
FROM dbo.incoming_data
GO

5. Switch to the messages Tab in the SSMS window to see what the code is doing.
6. When you have had enough close the file from Excel and the code should succeed.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
1.txt (3 views, 9 bytes)
Post #1361698
Posted Monday, September 24, 2012 1:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:43 AM
Points: 9, Visits: 82
Thanks a lot for this powerful alternative, I cannot test it right now but makes perfect sense and I'll surely give it a try soon.
I had a very complete overview of the problems thanks to your and other people's answer, this forum is so good!

Thank you very much
Post #1363350
Posted Monday, September 24, 2012 5:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
opc.three (9/19/2012)
I would just try loading the file straightaway and catch the exception from the inner proc call. If it is an exception about 'exclusive access' like this ...



+1


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363432
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse