Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Need to read varbinary(max) data

Answered Need to read varbinary(max) data

  • Saturday, January 19, 2013 3:03 AM
     
      Has Code
    Sign In to Vote
    0
     
    exec sp_executesql N'UPDATE CATEGORY  SET DEFINITION .WRITE (@bytes, @offset, @length) WHERE ID = @ID',N'@bytes varbinary(max) ,@offset bigint,@length bigint,@ID smallint',@bytes=0x0001000000FFFFFFFF01000000000000000C0200000047517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C05010000002D517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E07000000105F726574656E74696F6E506572696F64035F6964135F72657374726963746564466F6C64657249440E6175746F46696C696E67526F6F740E5F707265766965775265636F7264145F7265636F72644E616D6556616C696461746F720E5F656D61696C4D617070696E6773040000040404032A517565737479732E456E7469746965732E526574656E74696F6E2E526574656E74696F6E506572696F6402000000070934517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63680200000022517565737479732E456E7469746965732E5075626C69632E5265636F726444617461020000002B517565737479732E456E7469746965732E56616C696461746F72732E494669656C6456616C696461746F7202000000970153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E54656D706C6174652E54656D706C6174654D617070696E672C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5D02000000090300000027000000000000000000090400000009050000000A090600000005030000002A517565737479732E456E7469746965732E526574656E74696F6E2E526574656E74696F6E506572696F64020000000E5F726574656E74696F6E556E69740F5F726574656E74696F6E56616C7565040028517565737479732E456E7469746965732E526574656E74696F6E2E526574656E74696F6E556E697402000000080200000005F9FFFFFF28517565737479732E456E7469746965732E526574656E74696F6E2E526574656E74696F6E556E6974010000000776616C75655F5F0008020000000000000000000000050400000034517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E6368050000000B5F6E6F64655265636F7264115F6E6F646543617465676F72794E616D65055F74797065095F6368696C6472656E075F6C6576656C73040104030317517565737479732E456E7469746965732E5265636F7264020000001B517565737479732E456E7469746965732E5265636F72645479706502000000A20153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5DA80153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682B4C6576656C2C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5D020000000A0A05F8FFFFFF1B517565737479732E456E7469746965732E5265636F726454797065010000000776616C75655F5F000202000000000909000000090A000000050500000022517565737479732E456E7469746965732E5075626C69632E5265636F72644461746108000000026964046E616D6509657874656E73696F6E0863617465676F727905737461746504646174650474797065066669656C64730001010101000103090DE20153797374656D2E436F6C6C656374696F6E732E47656E657269632E44696374696F6E61727960325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D020000000000000000000000060B0000000B456E67696E656572696E67060C00000000060D000000264368616E6765204F726465727320666F72204D756C7469706C65204465706172746D656E7473090C0000000000EFEA7C2BCF08090C000000090F0000000406000000970153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E54656D706C6174652E54656D706C6174654D617070696E672C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5D03000000065F6974656D73055F73697A65085F76657273696F6E0400002B517565737479732E456E7469746965732E54656D706C6174652E54656D706C6174654D617070696E675B5D020000000808091000000000000000000000000409000000A20153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5D03000000065F6974656D73055F73697A65085F76657273696F6E04000036517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63685B5D02000000080809110000000000000000000000040A000000A80153797374656D2E436F6C6C656374696F6E732E47656E657269632E4C69737460315B5B517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682B4C6576656C2C20517565737479732E456E7469746965732C2056657273696F6E3D312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C5D5D03000000065F6974656D73055F73697A65085F76657273696F6E0400003C517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682B4C6576656C5B5D02000000080809120000000000000000000000040F000000E20153797374656D2E436F6C6C656374696F6E732E47656E657269632E44696374696F6E61727960325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D040000000756657273696F6E08436F6D7061726572084861736853697A650D4B657956616C756550616972730003000308920153797374656D2E436F6C6C656374696F6E732E47656E657269632E47656E65726963457175616C697479436F6D706172657260315B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D08E60153797374656D2E436F6C6C656374696F6E732E47656E657269632E4B657956616C75655061697260325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D5B5D08000000091300000011000000091400000007100000000001000000000000000429517565737479732E456E7469746965732E54656D706C6174652E54656D706C6174654D617070696E670200000007110000000001000000000000000434517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E6368020000000712000000000100000000000000043A517565737479732E456E7469746965732E44657369676E6572732E43617465676F7279446566696E6974696F6E2B4272616E63682B4C6576656C020000000413000000920153797374656D2E436F6C6C656374696F6E732E47656E657269632E47656E65726963457175616C697479436F6D706172657260315B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D00000000071400000000010000000800000003E40153797374656D2E436F6C6C656374696F6E732E47656E657269632E4B657956616C75655061697260325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D04EBFFFFFFE40153797374656D2E436F6C6C656374696F6E732E47656E657269632E4B657956616C75655061697260325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D02000000036B65790576616C7565010206160000000B476976656E2056616C756506170000000B456E67696E656572696E6701E8FFFFFFEBFFFFFF061900000008446F63204E616D65061A00000008446F63204E616D6501E5FFFFFFEBFFFFFF061C0000000C43757272656E742044617465061D0000000A30372F30392F3230313201E2FFFFFFEBFFFFFF061F0000000D4461746520457865637574656406200000000A30372F30392F3230313201DFFFFFFFEBFFFFFF06220000001054797065206F6620436F6E747261637406230000001054797065206F6620436F6E747261637401DCFFFFFFEBFFFFFF06250000000A436F6E74726163746F7206260000000A436F6E74726163746F7201D9FFFFFFEBFFFFFF0628000000134D756C7469706C6520466163696C69746965730629000000134D756C7469706C6520466163696C697469657301D6FFFFFFEBFFFFFF062B0000000750726F6A656374062C0000000750726F6A6563740B000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,@offset=0,@length=4566,@ID=39

    Hi,

    I have a database with many tables comprised of varbinary(max) columns. I have a general idea of that's stored in the columns, but need to be able to read the entire contents.

    I have the following 2 command examples that write the data to the table:

    exec sp_executesql N'UPDATE CATEGORY SET DEFINITION = CAST('''' AS VARBINARY(MAX)) WHERE ID = @ID',N'@ID smallint',@ID=39

    That second command in the code window is extraordinarlity long because it includes the exact data inserted. I really need to be able to decipher the @bytes value. I have seen threads all over the Internet for images stored in this manner, but these particular columns store character data. I have not been able to successfully extract it to a text file, either.

    I also realize this is probably a VB or C# project. If anyone reading this thread knows how to accomplish this in VB or C#, by all means, please feel free to pass along the code as I cannot get it figured out, can't get the code written.

    I thank you in advance as I really, really would appreciate a solution.

    Thank you!

     

All Replies

  • Saturday, January 19, 2013 7:20 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    You have to export the binary objects in varbinary(max) columns to the file system and use the appropriate software to "read" them.

    Example for exporting images:

    http://www.sqlusa.com/bestpractices/imageimportexport/

    For text you can try CONVERT to varchar(max) or nvarchar(max):

    SELECT  DocumentNode, Title, LEN(Document) AS Length, 
CONVERT(varchar(max), substring(Document,2000, 1000)) AS Document
FROM Production.Document 
WHERE DocumentNode = 0x5AC0;
/*
DocumentNode	Title	Length	Document
0x5AC0	Introduction 1	29696	E: All of the directions (right, left, front, rear, etc.) in this manual are as seen by the rider while seated on the bicycle.
Attach and use only Adventure Works Cycles brand accessories and replacement parts on the bicycle. You'll need these tools when servicing your Adventure Works Cycles bicycle.
Small Adjustable Wrench			Large Adjustable Wrench

(Jaws must open at least 9/16 inch.)			(Jaws must open at least 1 1/4 inch.)
Flat-blade Screwdriver			Phillips Screwdriver

Slip-Joint Pliers				Metric Allen Wrenches
(Needed on some models.)

*/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012