October 4, 2012 at 12:53 pm
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!
October 4, 2012 at 1:09 pm
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
October 4, 2012 at 2:08 pm
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).
🙂
October 4, 2012 at 2:28 pm
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
October 4, 2012 at 4:01 pm
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.
October 4, 2012 at 6:26 pm
[wistfully]
If only you could make the SQL Server reach out and determine whether a file exists on the client.
[/wistfully]
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
October 9, 2012 at 10:11 am
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.
October 9, 2012 at 6:25 pm
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 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