1

I need to query for a list of files, their bytes, and their file extensions in SQL. These files live in a directory not on the SQL server, but on another machine node in the network. I have seen some solutions like "EXEC xp_dirtree 'C:\', 10, 1" but these only get the file names. I need the file bytes too. Also, that solution only works if I'm querying the machine where the sql server instance is installed, it won't work with a remote machine that's on the network (even with the fully qualified path).

Andrew
  • 5,215
  • 1
  • 23
  • 42
MobileMon
  • 8,341
  • 5
  • 56
  • 75
  • 4
    SQL Server isn't exactly designed to enumerate remote node's file systems .... what are you trying to achieve? Can't you do this in some front-end/service layer software in C#, PHP - whatever - and then just store the results into SQL Server? – marc_s Nov 25 '13 at 20:01
  • 1
    Maybe this is a XY problem as described in http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem ??? – tucaz Nov 25 '13 at 20:05
  • Nope this is the behavior that I need. We have a virtual private network where the server who needs this data can only communicate with another sql server (the one who has access to this data) through the VPN. The communication is done via a remote procedure call. So basically the server who needs the data doesn't have access to the data, it only has communication access to the real server that has access to the data. Also I need the data real time, so having some other batch job to process it wouldn't work – MobileMon Nov 25 '13 at 20:10
  • 1
    So you need your server to request that another SQL server query a third server for file system info? Wow. Are you able to add objects to the second server (the one in the middle)? – Ann L. Nov 26 '13 at 14:19
  • @AnnL. Yes you understand the problem. And yes I can add tables or whatever I please to the intermediary server – MobileMon Nov 26 '13 at 14:55

2 Answers2

3

CLR Stored Procedures are best suited for tasks like this.

Also see this SO Answer that has some of the code you'll need. This one creates a CLR Function (code pasted below).

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
    public static IEnumerable GetFiles(SqlString path)
    {
        return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
    }

    public static void GetFiles_FillRow(object obj,out SqlString filePath)
    {
        filePath = (SqlString)obj;
    }
};
Community
  • 1
  • 1
Shiva
  • 20,575
  • 14
  • 82
  • 112
3

If CLR is not enabled on your server, and xp_cmdshell is, you could consider using this to parse the DIR command - but be aware that xp_cmdshell is turned off by default for a reason.

You can find a discussion of how to use xp_cmdshell for file sizes here and a blog article on how to do this here. You may need an administrator to configure a proxy account on the destination server per the instructions on this MSDN page.

I would suggest that you explore the CLR option first, as it is a safer and easier to audit solution, but because you would probably need the external access permission set to get to the file system, this may not be possible in your environment.

DaveGreen
  • 712
  • 6
  • 13