There have been many times that I have either seen posts on forums or have had colleagues asking me how they can kick off a process based on the existence of a file that signifies the end of another process. SQL Server provides several ways that this can be done, these include a WMI watcher in SSIS or ActiveX scripts checking for the file. However not everybody will have ability to use all of these features due to either the versions they have available or their permissions to access them.
In the first part of the “Finding Files:” blog series I will be walking you through a way of checking for files using T-SQL. Part two will cover how you can check for the existance of files using VB script in either DTS or SSIS. In the third part of the series I will cover how you can use WMI to accomplish the task. If you have never used WMI before it is very cool and opens up a new world of possibilities.
SQL Server has several undocumented system stored procedures that we can take advantage of to accomplish our aim. In this blog entry I will be concentrating on master.dbo.xp_FileExists, lets take a look at how we can use this sp to help achieve our goals:
/*If you are planning to run this code you may have to convert the single quotes, they changed to single text quotes during publishing*/
USE TempDB
GO
SET NOCOUNT ON
DECLARE @Filename VARCHAR(100)
/*First of all we are going to create a table variable to hold our results*/
DECLARE @FileExists TABLE
(File_Exists BIT,
File_Is_A_Directory BIT,
Parent_Directory_Exists BIT)
/*Now we are going to create a unique filename to check for*/
SET @Filename = ‘C:\BlogTest’+convert(CHAR(8),GETDATE(),112) +’.txt’
SELECT @Filename
INSERT INTO @FileExists (File_Exists, File_Is_A_Directory, Parent_Directory_Exists)
EXEC master.DBO.xp_fileexist @Filename
SELECT * FROM @FileExists
SET NOCOUNT OFF
GO
———————————————————————————————————
/*
Unless the unlikely event that you had a file called BlogYYMMDD.txt on the route of your c drive actually happened you will not have had the results below:
File_Exists File_Is_A_Directory Parent_Directory_Exists
———– ——————- ———————–
0 0 1
File_Exists has not been populated as we have not found the file.
File_Is_A_Directory has not been populated as BlogYYMMDD.txt was not a directory.
Thankfully Parent_Directory_Exists is populated meaning that my code has not destroyed your hard drive.
Now lets create this file programmatically in T-SQL so we can do the test again:
*/
———————————————————————————————————
USE TempDB
GO
SET NOCOUNT ON
DECLARE @Filename VARCHAR(100), @Command VARCHAR(250)
/*First of all we are going to create a table variable to hold our results*/
DECLARE @FileExists TABLE
(File_Exists BIT,
File_Is_A_Directory BIT,
Parent_Directory_Exists BIT)
/*Now we are going to create a unique filename to check for*/
SET @Filename = ‘C:\BlogTest’+convert(CHAR(8),GETDATE(),112) +’.txt’
/*This command will copy an existing file to create the file we are looking for*/
SET @Command = ‘Copy c:\autoexec.bat ’ + @Filename
SELECT @Filename, @Command
EXECUTE master.dbo.xp_cmdshell @Command, no_output
/*
If you receive the following error:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.
For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
To enable xp_cmdshell you will need to do the following (assuming OS is XP Pro and SQL version is 2005):
Click on Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server 2005 Surface Area Configuration
Choose Surface Area Configuration for Features.
Find xp_cmdshell in the tree structure on the left.
Click the enable tick box
Click Apply and then OK
*/
INSERT INTO @FileExists (File_Exists, File_Is_A_Directory, Parent_Directory_Exists)
EXEC master.DBO.xp_fileexist @Filename
SELECT * FROM @FileExists
SET NOCOUNT OFF
GO
/*
This time you should see the following resultset:
File_Exists File_Is_A_Directory Parent_Directory_Exists
———– ——————- ———————–
1 0 1
*/
In the real world you could potentially use this as the first step of a job. You would need to substitute the table variable for a physical table which you would be able to then access in other steps to call other stored procedures, DTS / SSIS packages or even another job.
Hopefully this will be of some use and will have wetted your appetite for parts two and three.
Great post!