Check for the existence of a file BEFORE processing in SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Check for the existence of a file BEFORE...

Check for the existence of a file BEFORE processing in SQL Server - SOLUTION

Rate Topic

Display Mode

Topic Options

Author

Message

Jason Tontz

Posted Thursday, October 04, 2012 12:53 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:20 PM
Points: 137, Visits: 259

I thought I should share this with SQLServerCentral.com because this site has helped me out so much. I ran across this issue and many posts on the internet saying that it cant be done, but I think I have managed to make it work.
It deals with the "xp_fileexist" extended stored procedure. This sp is designed to check for the existence of a file on the file system. Return code "1"-it exists, "0" it doesnt.

declare @File_Exists INT

EXEC xp_fileexist 'C:\users\admin\downloads\test.txt', @File_Exists OUT
Select @File_Exists

The obvious is that the user account must have permissions via the entire path. So of course, you grant the user account running the "SQL Server" service at least "read".
Once that is in place, the user account used to execute the query must be allowed to impersonate the account running the "SQL Server" service. This is easily accomplished with "sysadmin" role in SQL Server (I havent "lab"ed it enough to find the specific permission here).
Now for the stuff on the internet saying that you cannot access remote shares...
The "SQL Server" service logon account must have "read" access to the full path AND all of the above mentioned permissions.
So what if you aren't using a domain account for your SQL Server? What if it is just running as the local "Network Service"?
You can grant the permissions to the file system to the server itself using the machine name : SQLSRVR01$
Thats it! Now we can check to see if a file exists relatively easily from SQL server before running tasks. This works in Integration Services (SSIS) as well.

I hope someone finds this helpful. I spent a lot of time trying to check for a files existence before running a process that would fail and cause "downstream" processes to fail... Not anymore!

Post #1368647

GSquared

Posted Thursday, October 04, 2012 1:09 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Clever.

Not sure I'd want SQL running under permissions that can access other servers, but if you limit it to read-only, the security window on that is limited.

I've been using a CLR object to do file operations, including checking existence, for years now. More flexible than the built-in one, and less security risk than cmdshell. But as an alternative for someone who's not comfortable with .NET coding, your trick is certainly an option to look into.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1368659

Jason Tontz

Posted Thursday, October 04, 2012 2:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:20 PM
Points: 137, Visits: 259

Agreed. There are numerous other ways to check for file existence with other tools. .Net and Powershell can handle this no problem. I posted this for those situations where everything else was being handled in SQL server or if someone didnt have the other tools at thier disposal (like me).
Smile

Post #1368695

Lowell

Posted Thursday, October 04, 2012 2:28 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

One of the frequent posters here Elliot Whitlow posted a CLR project on CodePlex that does a lot of file manipulations, including a FileExists;

if adding CLR is an option in your shop , this might be of interest as well.
http://nclsqlclrfile.codeplex.com/

--MFFileExistsCheck
--  Parameters: @FilePath,@FileName
--  purpose: given a path and filename, check if the file exists 
--  usage: 
        SELECT dbo.MFFileExistsCheck('C:\Data','contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data\','contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data\','\contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data','\contents.txt') --returns 0 or 1 for false/true

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1368714

Jason Tontz

Posted Thursday, October 04, 2012 4:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:20 PM
Points: 137, Visits: 259

Thanks for the post Lowell. I wanted to address the permissions and shares issues with this XSP, but thank you for putting the link. I will certainly check it out.

Post #1368761

dwain.c

Posted Thursday, October 04, 2012 6:26 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

[wistfully]

If only you could make the SQL Server reach out and determine whether a file exists on the client.

[/wistfully]

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1368790

Jason Tontz

Posted Tuesday, October 09, 2012 10:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:20 PM
Points: 137, Visits: 259

But you CAN check for the exitence of a file on your user's workstation.
With the appropriate permissions on both the server and workstations such as domain admins, you can touch the clients and check for the existence of a file.
I did not go into details about my environment, but I am actually connecting the SQL Server to a "hidden/administrative" share to check for the file's existence.
Works great.

Post #1370469

dwain.c

Posted Tuesday, October 09, 2012 6:25 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

Jason Tontz (10/9/2012)

I suspected that the inability to do so was related to permissions. Just haven't had the time to play with it enough to solve the problem. Yet...

I do appreciate the confirmation though.

Post #1370640

« Prev Topic | Next Topic »

Permissions