Using xp_fileexist

One of the more tedious tasks confronting DBAs today is bulk inserting data. This isn't because the task itself is difficult, it's because the users that supply the data for the insert move at a somewhat leisurely pace. For example, many companies (maybe yours) use a UNIX based accounting system. Data from that system needs to be imported into the SQL Server based HR system regularly (usually weekly or monthly). In order to import this data you need to wait for the user to supply the export file so that you can bulk insert it into you SQL Server database. The problem for you is waiting, especially if this is happening to you on a Friday just before a big weekend out of town. The best way to get around this is to automate the process by using an undocumented extended stored procedure called xp_fileexist.

You may have played with this procedure in the past and found it to be rather lackluster because the output is somewhat blasé. If you just run the procedure by itself you see something like this:

xp_fileexist "c:\autoexec.bat"

File Exists  File is a Directory   Parent Directory Exists

-----------    -------------------       -----------------------

1                 0                              1

(1 row(s) affected)

This just means that c:\autoexec.bat exists and that it is not a directory. That’s great except that you cannot base  a job step on it as they are illiterate and cannot read straight text. Job steps do understand variables though, so if you want to use this procedure in a job step, you need to place the output in a variable. That is where the following code comes into play;

declare @result int

exec xp_fileexist 'c:\autoexec.bat', @result output

If the file exists @result will hold a value of 1. If the file does not exist then the variable will hold a value of 0. Now you just need to configure a job, with a step that looks something like this:

Declare @result int

exec xp_fileexist ‘filename’, @result output

IF (@result = 1)  --if file exists import it, otherwise exit

  begin 

    bulk insert tablename from 'filename'

else

  print ‘filename has not arrived'

Now all you need to do is tell the accounting people where to stick their file (what directory I mean) and let SQL Server do the rest.

There are a few important things to remember here. There is no error checking built in to the code so be careful. Also, this is an undocumented feature which means that Microsoft can change it at any time without warning anyone, so check your procedures after an upgrade or service pack. Other than that, go automate your imports.