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

  • 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!

  • 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

  • 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).

    🙂

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • [wistfully]

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

    [/wistfully]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    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?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • 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.

  • Jason Tontz (10/9/2012)


    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.

    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.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    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?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply