SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk insert of large files (more then 450MB)


Bulk insert of large files (more then 450MB)

Author
Message
adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 4639
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.
adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
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
adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 4639
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)

Group: General Forum Members
Points: 271141 Visits: 42207
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
Thank you too Jeff, this can be a good solution too!
OTF
OTF
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 4128
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;


adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
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.
adminorama
adminorama
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 94
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search