locked
Filestream Pathname RRS feed

  • Question

  • Hello,

    I'm trying to run the query given below but iFile.Pathname is underlined in red.

    Query: SELECT top 1 iFile.Pathname AS 'PathName' FROM Videos

    Error: Cannot find either column "iFile" or the user-defined function or aggregate "iFile.Pathname", or the name is ambiguous.

    Do I need to run a procedure?

    Regards

    Farook



    Farook

    Tuesday, November 3, 2015 5:40 AM

Answers

  • Hello Visakh,

    I've made amendments to automatically delete the physical file.

    Thanks for your support.

    Regards


    Farook

    • Marked as answer by Farook Chana Wednesday, November 4, 2015 9:02 AM
    Wednesday, November 4, 2015 9:02 AM

All replies

  • DO you have the column iFile in the table Videos?

    Is it defined as of FILESTREAM type?

    see

    https://msdn.microsoft.com/en-us/library/bb895239.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 3, 2015 6:34 AM
  • Filestream is working perfectly. I can upload large files.

    Farook

    Tuesday, November 3, 2015 6:37 AM
  • Filestream is working perfectly. I can upload large files.

    Farook

    1. iFile is of type Varbinary(MAX)

    2. SELECT name FROM sys.columns WHERE is_filestream=1 (Returns iFile as result)


    Farook

    Tuesday, November 3, 2015 7:10 AM
  • Filestream is working perfectly. I can upload large files.


    Farook

    1. iFile is of type Varbinary(MAX)

    2. SELECT name FROM sys.columns WHERE is_filestream=1 (Returns iFile as result)


    Farook

    DId you try putting braces around function?

    ie like

    SELECT top 1 iFile.Pathname() AS 'PathName' FROM Videos


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 3, 2015 7:30 AM
  • Braces are in the function, in my quote they are missing.

    SELECT top 1 iFile.Pathname() AS 'PathName' FROM Videos

    SELECT top 1 iFile.Pathname(0) AS 'PathName' FROM Videos



    Farook

    Tuesday, November 3, 2015 7:34 AM
  • Hi Farook,

    According to your description, you get the error “Cannot find either column "iFile" or the user-defined function or aggregate "iFile.Pathname", or the name is ambiguous.”

    Based on my test, “N” in iFile.PathName() should be uppercase. You can query successfully using the following code.

    SELECT top 1 iFile.PathName() AS 'PathName' FROM Videos

    Regards,
    Ice Fan 


    Ice Fan
    TechNet Community Support


    • Proposed as answer by Visakh16MVP Tuesday, November 3, 2015 9:43 AM
    Tuesday, November 3, 2015 9:41 AM
  • Hello Ice Fan and Visakh,

    Ice, I am glad this error was encountered for the fact to know that certain SQL commands are case sensitive. Generally we use RecordSet and DataSet and it's a habit, luckily I missed out on PathName(). However I wish to let you know the command is still underlined in red but does give results. My other issue is the results display RowGuid. I want to get the path to the file itself which is stored in C:\Program Files\Microsoft SQL Server\MSSQL12.OASIS\MSSQL\DATA\FSGroupData\THE FOLDER/FILE. So if a record is deleted then the large file in this folder should be deleted too.

    Thanks & regards


    Farook

    Wednesday, November 4, 2015 5:13 AM
  • Hello Ice Fan and Visakh,

    Ice, I am glad this error was encountered for the fact to know that certain SQL commands are case sensitive. Generally we use RecordSet and DataSet and it's a habit, luckily I missed out on PathName(). However I wish to let you know the command is still underlined in red but does give results. My other issue is the results display RowGuid. I want to get the path to the file itself which is stored in C:\Program Files\Microsoft SQL Server\MSSQL12.OASIS\MSSQL\DATA\FSGroupData\THE FOLDER/FILE. So if a record is deleted then the large file in this folder should be deleted too.

    Thanks & regards


    Farook

    See this for similar discussion on the same

    https://social.msdn.microsoft.com/forums/sqlserver/en-US/6ce780cd-5f86-43c2-b76e-3db1d63832b2/how-to-obtain-actual-file-name-of-a-filestream-file


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 4, 2015 7:09 AM
  • Hello Visakh,

    I've made amendments to automatically delete the physical file.

    Thanks for your support.

    Regards


    Farook

    • Marked as answer by Farook Chana Wednesday, November 4, 2015 9:02 AM
    Wednesday, November 4, 2015 9:02 AM