question

chrisrdba avatar image
0 Votes"
chrisrdba asked ErlandSommarskog commented

How to tie filestream GUID value to actual file name?

Greetings. I've seen a few people ask this question on google w no real solution.

We're using filestream, and I want to run a query in the DB that will allow me to tie the result of that query to the actual name of the filestream file.

This link shows the column filestream_value_name which is precisely what I'm after, but only shows stuff waiting to be backed up, while running in DAC. I need something that can tie every row of data in my table to the actual file name as seen in Explorer (for example).

I'm also aware of column.pathname , but that doesn't truly show what I need.

I'm also aware that manually manipulating these files is not supported, and could lead to corruption. I have no intention of doing that.

Any ideas?

sql-server-general
· 11
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't understand what you are trying to achieve here, may can you explain it with samples, please?

0 Votes 0 ·
chrisrdba avatar image chrisrdba OlafHelper-2800 ·

201508-fsfiles.jpg



See attached. From the first link above, the query would suggest that the files are named "0x5048....." and "0xFFD....." while the OS clearly has no files w those names.

How do I write a query that will give me the real names the OS sees?

Thanks!

0 Votes 0 ·
fsfiles.jpg (80.7 KiB)

The column "file" is the actual binary contents of the file, not a filename.

column.pathname , but that doesn't truly show what I need.


Why? This provides the full path to the actual file.

What are you trying to accomplish by getting the filename? The physical file is completely managed by SQL Server and is really meaningless.

0 Votes 0 ·
Show more comments

Hi @chrisrdba,

Please check if below thread could help you.

SQL Filestream - tying up rows with File Stream files


0 Votes 0 ·

Thanks, but as mentioned I'm aware of column.pathname , but that doesn't truly show what I need.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

It may be better to play it safe and write a program that trawls the Filestream table and extracts the files with the OpenSqlFilestream API and writes the file and the metadata. Yes, this means that you need to find a programmer that can write such a program. Then again, this is not 200 lines of code.

This also gives you a consistent view of the contents. If you read the raw file area directly, you will get old versions and deleted files that have not been garbage-collected yet.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.