locked
unknown file name and type in varbinary(max) RRS feed

  • Question

  • I am tasked with finding the file name and the extension for files stored in SS2008 db using a varbinary(max) field. The file types are mixed and mo file information is available. Is it possible to get the info from the binary itself? Scratching Head. Thanks.

    Saturday, January 21, 2012 6:19 PM

Answers

  • I think your best bet is to dump the files in a directory, and then find some third-party tool that is able to identify files from the format alone.

    If there such a tool with an API, you might not have to dump the files on disk, but you can pass the binary directly through the API. You might even be able to implement it as a CLR stored procedure

    So much is clear: from the realm of T-SQL this is not doable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, January 22, 2012 1:30 AM
    • Marked as answer by Kalman Toth Friday, January 27, 2012 3:38 PM
    Saturday, January 21, 2012 8:18 PM
  • No, this information can not be obtained from the binary file itself. You should have saved at least the extension of the file in another column. I remember discussion of this problem a while ago in ASP.NET forum and the answer is NO.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Sunday, January 22, 2012 4:00 PM
    • Marked as answer by Kalman Toth Friday, January 27, 2012 3:38 PM
    Sunday, January 22, 2012 1:29 AM

All replies

  • In theory you may be able to find it, dependent on how exactly the text information stored:

    DECLARE @bin varbinary(max) = convert(varbinary(max),N'Berlin Budapest Quito Istanbul')
    SELECT 'Found' WHERE @bin like N'%Budapest%'
    -- Found
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    • Edited by Kalman Toth Saturday, January 21, 2012 9:10 PM
    Saturday, January 21, 2012 6:28 PM
  • I think your best bet is to dump the files in a directory, and then find some third-party tool that is able to identify files from the format alone.

    If there such a tool with an API, you might not have to dump the files on disk, but you can pass the binary directly through the API. You might even be able to implement it as a CLR stored procedure

    So much is clear: from the realm of T-SQL this is not doable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, January 22, 2012 1:30 AM
    • Marked as answer by Kalman Toth Friday, January 27, 2012 3:38 PM
    Saturday, January 21, 2012 8:18 PM
  • No, this information can not be obtained from the binary file itself. You should have saved at least the extension of the file in another column. I remember discussion of this problem a while ago in ASP.NET forum and the answer is NO.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Sunday, January 22, 2012 4:00 PM
    • Marked as answer by Kalman Toth Friday, January 27, 2012 3:38 PM
    Sunday, January 22, 2012 1:29 AM
  • you may want to check your facts before you shame people for doing things that you have no idea if they did or not. did i state anywhere in my post that i did this? please take your browbeating style else where.

    Sunday, January 22, 2012 3:26 AM
  • I am not sure who you address your comment. In any case, the answer to your question - there is no way to find the file name and extension based on the binary content of the file in T-SQL.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, January 22, 2012 3:38 AM
  • you may want to check your facts before you shame people for doing things that you have no idea if they did or not. did i state anywhere in my post that i did this? please take your browbeating style else where.

    This is a technical forum. If you want to insult people, you are in the wrong place.

    Naomi gave you a correct answer to your question.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 22, 2012 11:59 AM
  • you may accustomed to being shamed but i am not and i will speak up for myself whenever i am spoken to that way. thank you for your opinion.
    Sunday, January 22, 2012 12:10 PM
  • Let's maintain polite and professional discussion.

    Let's get back to the topic.  Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Sunday, January 22, 2012 2:19 PM