SQL Server: How to Check the Status of a File Before Processing - Page 2

How to make use of the udf_CheckFileStatus function when doing BULK INSERT of a file that is being copied

When you rely on xp_fileexist, xp_cmdshell and Xp_getfiledetails to obtain the status information of a file that is being copied, the following BULK insert command fails with the error shown below. In addition, you have to keep trying the SQL Statements until the file copy is complete.

Bulk Insert Statement

BULK INSERT [PERFMON]
   FROM 'D:\PERFMON.CSV'
   WITH 
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'
      )

Output Message

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file 
  'D:\PERFMON.CSV' could not be opened. 
  Operating system error code 32
  (The process cannot access the file because 
  it is being used by another process.).

You can avoid such errors by making use of the udf_CheckFileStatus function shown below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not Zero it waits for one minute and continues trying until the status is 1. Therefore, the database administrators do not have to spend time waiting for the file to completely copy and then do the BULK INSERT. When the status is 1 the file is automatically inserted using the BULK INSERT statement.

SQL Statement

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus('D:\PERFMON.CSV') )
if dbo.udf_CheckFileStatus('D:\PERFMON.CSV') =0
begin
BULK INSERT [PERFMON]
   FROM 'D:\PERFMON.CSV'
   WITH 
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'
      )
BREAK
end

waitfor delay '00:01:00'
END

Output

Mar  1 2005  8:27AM
Status=1
Mar  1 2005  8:28AM
Status=1
Mar  1 2005  8:29AM
Status=1
Mar  1 2005  8:30AM
Status=1
Mar  1 2005  8:31AM
Status=1
Mar  1 2005  8:32AM
Status=0

(12585 row(s) affected)

Imported

How to make use of the udf_CheckFileStatus function when restoring database that is being copied

When you rely on xp_fileexist, xp_cmdshell and Xp_getfiledetails to obtain the status information of a file that is being copied, the following RESTORE statement fails, with the error shown below.

Restore Statement

Restore database master_emp from disk ='D:\master_emp.BAK' with replace,
move 'master_emp_Data' to 'd:\data\Master_Emp_Data.mdf',
move 'master_emp_log' to 'd:\data\Master_Emp_log.ldf'

Output Message

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\master_emp.BAK'. Device error or device off-line. 
  See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You can avoid such errors by using the udf_CheckFileStatus function as shown below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not zero, it waits for one minute and keeps trying until the status becomes 1. Therefore, the database administrators do not have to spend time waiting for the file to completely copy. When the status is 1, the backup file is automatically restored.

SQL Statement

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus(' D:\master_emp.BAK') )
if dbo.udf_CheckFileStatus('D:\master_emp.BAK') =0
begin
--Kill processes using this master_emp database
Restore database master_emp from disk ='D:\master_emp.BAK' with replace,
move 'master_emp_Data' to 'd:\data\Master_Emp_Data.mdf',
move 'master_emp_log' to 'd:\data\Master_Emp_log.ldf'
BREAK
end

waitfor delay '00:01:00'
END

Output Message

Processed 45520 pages for database 'master_emp', file 'master_emp' on file 1.
Processed 1 pages for database 'master_emp', file 'master_emp_log' on file 1.
RESTORE DATABASE successfully processed 45521 pages in 73.525 seconds (5.071 MB/sec).

How to use the udf_CheckFileStatus function to notify whether the file is ready for processing

If you require SQL Server to send an email to you when the copy process is complete, you can use the SQL Statement below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not zero it waits for one minute and keeps trying until the status becomes 1. When the status is 1 it sends out the email using xp_sendmail.

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus(' D:\master_emp.BAK') )
if dbo.udf_CheckFileStatus('D:\master_emp.BAK') =0
begin
xp_sendmail @recipients ='mak_999@yahoo.com',
@subject = 'Files arrived safely',
@message ='Copy complete: The File D:\master_emp.BAK has been copied'
BREAK
end

waitfor delay '00:01:00'
END

Conclusion

The intention of this article has been to demonstrate how to use the user defined function, udf_CheckFileStatus, in order to avoid waiting for a file to be copied and how to run a process more efficiently. The same function can be used in a SQL Server scheduled job for checking files before running a DTS package, etc.

» See All Articles by Columnist MAK