How to test DOS "file in use" status?
-
On a periodic basis (Job), I look for any new files deposited in a certain directory. When I see a new file, I BULK INSERT from it, and process the data etc...
The problem is, the remote user depositing the file maybe connected very-very slowly, and NOT yet finished copying the file to the server before SQL server tries to import it.
In the old days, the file size would remain zero until finished, but in WIN2k, the file size appears right away. This used to be my "key"... anyway...
Is there a way (T-SQL) to test the "file in use" status of a file?
Once you understand the BITs, all the pieces come together
-
How does the user deposit the file. Is it an automated process? Same file name each time?
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
File is deposited from a WINPC connected to server using a "UNC Share", then VB application performs a WINAPI "File Copy".
Each mobile WINPC deposits it's own, same name every time file. There may be hundreds of mobiles doing this throughout the day.
I poll for new files 1/minute.
Once you understand the BITs, all the pieces come together -
xp_fileexist will wait for file to be free but could cause you problems if the file is never released.
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry xp_fileexist will only wait if file is being written too not copied.
Could write your own extended procedure.
Edited by - davidburrows on 12/18/2003 07:02:07 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Is it possible to drop a semaphore file that is copied immediately after the actual data file? A 1-byte file or something of that sort. Or is possible to have the mobiles make a single connection to SQL Server to insert into a status table?
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Thanks David.
Seems like xp_fileexist ruturns "OK" data as soon as the file exists, but prior to the file being available to import. I'm looking for a "file property" something like "file in use" accessable in TSQL. I use TEXTCOPY to import the file data, and it seems to console output "ERROR: Problem with file ....". Guess I'll just have to try to do the work, and then test if the work was done properly.
Once you understand the BITs, all the pieces come together -
Brian, both are good ideas, but changing the mobile application in this manner is not feasable. WINPC deposits multiple other files also etc. and complexity of this issue is more than I've written above.
BTW, It is possible the WINPC app will NOT complete it's file copy due to hard disconnect.
Once you understand the BITs, all the pieces come together -
Found something interesting...
xp_getfiledetails [Last Written Time] has the timestamp of the mobile PC file until it has completed it's copy. After completion, [Last Written Time] shows target server time. Even though this is "something", I've got no clue what the datetime on the mobile PC is. i.e. could be seconds, minutes in the past or future. If by chance the copy completion time is the same as the time on the mobile PC when it was finished creating the file, I could not "trigger" on it:(.
Once you understand the BITs, all the pieces come together -
Could use fso but would have to trap errors
DECLARE @fso int
DECLARE @hr int
DECLARE @file int
DECLARE @iomode int
DECLARE @filename varchar(255)
SET @filename = 'filename'
SET @iomode = 1
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso
-- Error Processing
END
EXEC @hr=sp_OAMethod @fso, 'OpenTextFile',@file OUT, @filename, @iomode
IF @hr = 0
BEGIN
EXEC @hr=sp_OAMethod @file, 'Close'
EXEC @hr=sp_OADestroy @file
END
ELSE
BEGIN
select @hr
--Check @hr error here
--@hr = -2146828235 (0x800A0035) File Not Found
--@hr = -2146828218 (0x800A0046) Permission Denied (in use)
END
EXEC @hr=sp_OADestroy @fso
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David, I'll give it a try and report back.... setting up a "very slow test senario" takes me a few.
Once you understand the BITs, all the pieces come together -
Did it, works! Thanks.
Called the function a couple of thousand times in a about a 12 sec loop just to test... got -2146828235 result until file existed, then result changed to -2146828218, once file was "avaialble", result changed to 0.
BTW, I tried puting a WAITFOR DELAY command in the function, but SQL does not like it :(. Guess I have to put the "retry delay" in the sorrounding code.
Thanks again 🙂
Create Function fn_FileAvailable
(@FileName VarChar(255))
Returns Int
As
Begin
/*
Return Values
= 0 FileSystemObject can open the file, so deemed "avaialble"
= -1 means FileSystemObject could not be created
= -2146828235 (0x800A0035) File Not Found
= -2146828218 (0x800A0046) Permission Denied (in use)
...etc
*/
DECLARE @fso int, @hr int, @file int, @Result Int
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @fso -- Error Processing
Return -1
END
EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @file OUT, @FileName, 1
SELECT @Result = @hr
IF @hr = 0 BEGIN
EXEC @hr = sp_OAMethod @file, 'Close'
EXEC @hr = sp_OADestroy @file
END
EXEC @hr = sp_OADestroy @fso
Return @Result
End
Once you understand the BITs, all the pieces come together
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply