title: SQL Server FileStream - How to acquire files path
body: <p>I'm working on this application where I need to upload large data files to my SQL Server DB, and I've been using FileStream to do it more efficiently.</p>
  
  <p>I understand the files are stored directly into my system's folders (C:\CryptoDB).</p>
  
  <p>The thing is, I need to manipulate these files (decrypt them) but I haven't been able to recover their filepath. 
  Doing so, I would be able to manipulate them directly, not having to re-download them via SQL, which is a real waste.</p>
  
  <p>What I've been able to do so far:</p>
  
  <p>My table:</p>
  
  <pre><code>CREATE TABLE [arquivo] (
  [idUsuario]   INT NOT NULL,
  [fileState]   INT NOT NULL,
  [fileContent] varbinary(max) FILESTREAM,
  [fileName]    VARCHAR (150)   NULL,
  [fileSize]    VARCHAR (50)    NULL,
  
  id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
  
  CONSTRAINT [FK_arquivo_usuario] FOREIGN KEY ([idUsuario]) REFERENCES usuario(id)
  );
  </code></pre>
  
  <p>Insert:</p>
  
  <pre><code>Insert into arquivo(id, idUsuario, fileState, fileContent, fileName, fileSize) Values(
  newId(),
  1,
  5,
  (SELECT * FROM OPENROWSET(BULK 'c:\medio.jpeg', SINGLE_BLOB) AS varbinary(max)) ,
  'medio.jpeg',
  '123'
  )
  </code></pre>
  
  <p>And when I try to recover the filepath:</p>
  
  <pre><code>DECLARE @filePath varchar(max)
  
  SELECT @filePath = fileContent.PathName()
  FROM arquivo
  
  PRINT @filepath
  </code></pre>
  
  <p>The result I get:</p>
  
  <pre><code>\\TEHORT-PC\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\CryptoDB\dbo\arquivo\fileContent\31E3697E-0576-4B0F-B0AA-6E046F4116A1\VolumeHint-HarddiskVolume2
  </code></pre>
  
  <p>Where the file actually is:</p>
  
  <pre><code>C:\CryptoDB\DATA\902a7d8d-c8c1-43b0-8c94-b12319293f42\7febdbd1-02c6-4b00-aa3c-a72bee80ef9c\
  </code></pre>