3

I have a document table in a SQL Server 2008 R2 database with a structure like this:

id | date_created | file_path             | file_type
---+--------------+-----------------------+--------------------
1  | 2016-11-14   | \\server\docs\123.doc | application/msword
2  | 2016-11-15   | \\server\imgs\456.png | image/png

I need to determine the file size of a subset of documents. So I have a query that will select certain rows from the document table (based on their ID) and I would need to find out what the total file size is of that set of documents. I did some Googling (before coming here of course) but most things I can find related to files/SQL is about log file sizes which is obviously NOT what I want.

Any and all help is appreciated as always! Thanks!

D.R.
  • 1,199
  • 5
  • 19
  • 42
  • check out xp_getfiledetails and xp_dirtree among others. Sounds like what you are trying to get at. – S3S Nov 15 '16 at 21:30
  • One way to do this you can find in this answer I gave [here](http://stackoverflow.com/a/34843612/243373) on SO. It uses OLE automation. There are other ways, the answer illustrates one of them. – TT. Nov 15 '16 at 21:53
  • Another way (out of several others) is from the [SQL#](http://www.sqlsharp.com/features/) library. The function is `GetFileInfo`, sadly it is in the non-free section. Yet another way is to use shell commands and use `xp_cmdshell` to get the results. – TT. Nov 15 '16 at 21:58
  • Does the fact that it is a network path and not an attached disk affect the usage of either of those options? – D.R. Nov 16 '16 at 14:23
  • It also looks like I would likely need to use a cursor to iterate over all the rows of files – D.R. Nov 16 '16 at 14:24
  • Did the references help you in any way? – TT. Nov 17 '16 at 12:58
  • I'm still working on my solution, so when it's finalized, I'll select an answer or ask you to submit yours as an answer. Thanks! – D.R. Nov 22 '16 at 15:48

1 Answers1

1

I'm answering my own question for completeness...

I was unable to use any of the options provided in the comments on the question, due to limitations in the Production environment where this query needed to be run. Instead, I ran the query to select the desired rows and exported it to a CSV file. I then wrote a quick and dirty Java program (only because it's my most comfortable language and I had similar projects from the past that I could reuse) that took a CSV file as an argument and parsed the CSV and checked each of the files and output a total file size in the console. While it doesn't solve the original question in SQL, it did resolve the problem in this particular case.

Note: If anyone has a SQL solution they can submit as an answer that I can verify as a valid answer, I will switch to that as the accepted answer.

D.R.
  • 1,199
  • 5
  • 19
  • 42