I'm trying to use file_stream.GetFileNamespacePath() as documented here: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/getfilenamespacepath-transact-sql?view=sql-server-2017 using this code:
use StepwiseProcedures
declare @root varchar(100)
SELECT @root = FileTableRootPath();
declare @fullPath varchar(1000);
SELECT @fullPath = @root + Code_filetable.file_stream.GetFileNamespacePath() FROM Code_filetable
WHERE Name = '118.txt';
This code does not pass a syntax check and the message is: "Cannot call method on varbinary"
The file_stream column is type varbinary(MAX) in the fixed schema enforced for every FileTable created. I've been using several FileTable's for more than a year and other operations are working as documented.
If I can't get GetFileNamespacePath() to work, I'll have to somehow hard-code the paths I seen in File Manager, or perhaps migrate to SQL Server 2016 or later.
Note that you must establish your database instance to support FILESTREAM etc. before you can create and use a FileTable and reproduce this issue. Configuration is described here:
https://learn.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-2017
I've read the following through the following links, but nothing comes close to addressing this issue.
SQL FileTable GetFileNamespacePath
https://www.sqlservercentral.com/Forums/Topic1715710-3077-1.aspx
https://sqltales.wordpress.com/2012/05/15/varbinary-vs-filestream-and-other-blob-issues-3/
https://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html
https://svenaelterman.wordpress.com/2012/07/31/pathname-versus-getfilenamespacepath/
I'm using Microsoft SQL Server 2014 (Enterprise 64-bit)
Thanks