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 Tuesday, September 11, 2012 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
Hi everyone

I have to populate a table with a pretty large text file and I'm happily using the very good BULK INSERT statement and this "import" procedure is one of many I have on my DB.

A series of condition must be met before the parent procedure starts to call all the child procedures and one is (obviously) to check if all needed files are in the import directory.

From time to time the copy of this big file takes more than usual and the procedure starts with this file still in use by the system (still being copied to the destination directory).

Is there any technique that I can use to detect if the file is still in use without generating the error and delay the parent import start?
I'm using SQL Server 2008

Thanks a lot in advance
Post #1357367
Posted Tuesday, September 11, 2012 2:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
adminorama (9/11/2012)
Hi everyone

I have to populate a table with a pretty large text file and I'm happily using the very good BULK INSERT statement and this "import" procedure is one of many I have on my DB.

A series of condition must be met before the parent procedure starts to call all the child procedures and one is (obviously) to check if all needed files are in the import directory.

From time to time the copy of this big file takes more than usual and the procedure starts with this file still in use by the system (still being copied to the destination directory).

Is there any technique that I can use to detect if the file is still in use without generating the error and delay the parent import start?
I'm using SQL Server 2008

Thanks a lot in advance


Yes. Set a flag.

Let the process that generates the offending file create a second - even empty - file after it completes creating the file your process need then your process has to check for the existence of the flag-file, if not there sleep for a couple of minutes and try again, if there, do whatever has to be done and delete the flag-file.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1357708
Posted Tuesday, September 11, 2012 5:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
Thanks
The problem is that I don't have any power over the file generation and getting the flag file from the client could take a lot of time.
I was hoping for a SQL solution, but if there isn't any I'd better try the tecnique you suggested
Post #1357746
Posted Wednesday, September 12, 2012 4:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
Wandering around the web I found that OLE Automation could be a solution, so I want to share this

I created a function that returns "0" if the file is free and "1" if it's used by another process or deas not exists

CREATE FUNCTION fnc_IsFileInUse(@filename VARCHAR(1000))
RETURNS BIT
AS
BEGIN
DECLARE @FS INT
DECLARE @OLEResult INT
DECLARE @FileID INT
DECLARE @source NVARCHAR(255)
DECLARE @description NVARCHAR(255)
DECLARE @flag BIT
SET @source = 'Exist'
SET @description = 'Exist'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT
GOTO displayerror
END
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @filename, 1
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT
GOTO displayerror
END
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

displayerror:
IF @source IS NULL
AND @description IS NULL
BEGIN
SET @flag = 1
END
ELSE
BEGIN
SET @flag = 0
END
RETURN @flag
END

I've tested this function on SQL Server 2008 and SQL Server 2005, it needs OLE Automation to be enabled and the user permission on the included stored procedures, so this might not be a solution suitable for everyone
Post #1357871
Posted Wednesday, September 12, 2012 3:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Nice reseach, congrats! Thank you for providing a working solution, I'm sure people will benefit from it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1358231
Posted Wednesday, September 12, 2012 9:59 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
Yes... at the beginnning of your proc, try to bulk insert the first row from the file into a single wide column on a dummy temp table using the "LASTROW" option set to "1". If you get an "Error 21" (just look for an error), then the file isn't ready. Then you can either exit or set a WAITFOR DELAY and try again. This keeps you from having to do things like turning on OLE Automation.

--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 #1358325
Posted Thursday, September 13, 2012 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
Thank you too Jeff, this can be a good solution too!
Post #1358354
Posted Thursday, September 13, 2012 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
adminorama (9/12/2012)
Wandering around the web I found that OLE Automation could be a solution, so I want to share this

I created a function that returns "0" if the file is free and "1" if it's used by another process or deas not exists

CREATE FUNCTION fnc_IsFileInUse(@filename VARCHAR(1000))
RETURNS BIT
AS
BEGIN
DECLARE @FS INT
DECLARE @OLEResult INT
DECLARE @FileID INT
DECLARE @source NVARCHAR(255)
DECLARE @description NVARCHAR(255)
DECLARE @flag BIT
SET @source = 'Exist'
SET @description = 'Exist'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT
GOTO displayerror
END
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @filename, 1
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT
GOTO displayerror
END
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

displayerror:
IF @source IS NULL
AND @description IS NULL
BEGIN
SET @flag = 1
END
ELSE
BEGIN
SET @flag = 0
END
RETURN @flag
END

I've tested this function on SQL Server 2008 and SQL Server 2005, it needs OLE Automation to be enabled and the user permission on the included stored procedures, so this might not be a solution suitable for everyone


To by pass OLE Automation which you would typically want to do anyway you can put code along these
lines in a SQL CLR Function to test for the readiness of a file to be read:

 try
{
using (File.Open(filepath, FileMode.Open, FileAccess.Read, FileShare.None)) { }
}
catch (Exception)
{
return false;
}
return true;

Post #1358647
Posted Thursday, September 13, 2012 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
OTF (9/13/2012)
To by pass OLE Automation which you would typically want to do anyway you can put code along these
lines in a SQL CLR Function to test for the readiness of a file to be read:

 try
{
using (File.Open(filepath, FileMode.Open, FileAccess.Read, FileShare.None)) { }
}
catch (Exception)
{
return false;
}
return true;



Thanks for this approach, I tought about using a CLR function but in that case I would have to deal with CLR authorizations and enabling (pretty much the same to enablig OLE automation) and maintaining an external piece of code.

Nonetheless it's a valid solution.
Post #1358660
Posted Tuesday, September 18, 2012 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 1, 2014 5:36 AM
Points: 9, Visits: 85
Hi Everyone

I just found that in some cases the OLEAutomation check fails, giving me the "green light" when the file is still being copied

I've gone through the CLR solution with a little twist, this peace of function also tell me if a file is nonexistent

Here's the c# part

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;


namespace IsFileInUse
{
public class IsFileInUse
{

[SqlFunction()]
public static SqlInt32 isFileInUse(string fullFileName)
{
SqlInt32 FILE_IS_FREE = 0;
SqlInt32 FILE_IS_IN_USE = 1;
SqlInt32 FILE_IS_MISSING = 2;

SqlInt32 intResult = FILE_IS_FREE;

try
{
using (File.Open(fullFileName, FileMode.Open, FileAccess.Read, FileShare.None)) { }
}
catch (Exception e)
{
if (e.GetType() == typeof(FileNotFoundException))
{
intResult = FILE_IS_MISSING;
}
else
{
intResult = FILE_IS_IN_USE;
}
}

return intResult;
}
}
}

The SQl bit to register the assembly

CREATE ASSEMBLY [IsFileInUse]
FROM 'C:\IsFileInUse.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

The sql bit to create the function

CREATE FUNCTION fnc_IsFileInUse(@fullFileName NVARCHAR(MAX))
RETURNS INT
EXTERNAL NAME IsFileInUse.[IsFileInUse.IsFileInUse].isFileInUse

And the example on how to use this function

DECLARE @intResult INT
SELECT @intResult = dbo.fnc_IsFileInUse('c:\bigSoonToBeImportedFile.txt')
IF @intResult = 1
BEGIN
--report file in use
END
ELSE IF @intResult = 2
BEGIN
-- report file missing
END
ELSE
BEGIN
-- import the file
END

Obviously in order to register the assebly and to use it, CLR must be enabled and the right permissions must be set

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE databaseNameHere SET TRUSTWORTHY ON
GO

I'd like to thank everyone who helped me
Post #1360711
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse