241b897f   
   From: esquel@sommarskog.se   
      
   sauly (saulysw@gmail.com) writes:   
   > I have a problem that I have been working on most of the afternoon and   
   > I was hoping someone here could point me in the right direction. In a   
   > nutshell, I have a stored procedure that is receiving data from a   
   > PocketPC device. Due to limitations of the sync, the binary image data   
   > is broken on the handheld into smaller pieces and transmitted to the   
   > SQL 2000 database in parts. This string is a text representation of   
   > the hex as shown below (the numbers don't make a real JPG, but imagine   
   > more parts and bigger strings)   
   >   
   > @Pic_Part = '0xFFD8FFE000104A46494600010101004800480000F', @Part=1   
   > then   
   > @Pic_Part = '0xF4AEB7C39CE9706070381F8122ADEC44BA22C4', @Part=2   
   > ..   
   >   
   > When I read it back using ...   
   >   
   > READTEXT [dbo].[Picture].[Pic] @ptrval 0 @i   
   >   
   > I get ..   
   >   
   > 0x30784634414542374333394345....   
   >   
   > and not the ..   
   >   
   > 0xFFD8FFE000104A46494600010....   
      
   If you do   
      
       select convert(varchar, 0x30784634414542374333394345)   
      
   you will get a revelation. Apparently Picture is of datatype Image. But   
   your input is not binary data, but hexstrings. So you first need to convert   
   the hexstring to binary.   
      
   Unfortunately, there is no quick way to do this in SQL 2000. There is an   
   undocumented system-shipped user-defined function to it one direction,   
   but I don't recall which. But "SELECT name FROM master.dbo.sysobjects   
   WHERE type = 'TF'" may reveal something interesting.   
      
   Overall, working with the image type is difficult and labourously. If you   
   are able to upgrade to SQL 2008, you could use varbinary(MAX), and also   
   use built-in conversion of hexstrings.   
      
   If you cannot move on from SQL 2000, I would suggest that doing this   
   client-side in a regular programming language is a lot better.   
      
   Of course, one could ask if there are limitations. why the binary parts   
   are converted to hexstrings in the first place...   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)