Loosing original information Manipulating varbinary(max) - BLOB data - varbinary(max)->varchar(max)->varbinary(max) RRS feed

  • Question

  • I am trying to do conversion from varbinary(max)->varchar(max)->varbinary(max), this is truncating the varbinary data, how can I do this conversion without loosing the original information in the conversion process? 

    Below is the situation I am seeing this issue (to give you a better idea on what I am trying to accomplish).

    As part of archival solution I am copying data from multiple tables to a single table and will be able to migrate back to the original table when needed, this is working fine except for blob (varbinary) datatypes.

    The arichival table has table name as the first column and have columns col_01,col_02,....col_100 (maxmium 100 columns ca be stored) all these columns are of type varchar(max).

    I created two stored procs which will take table name as parameter.

    stored proc #1 will select all rows (convert each column to vatchar(max) ) from the table (identified by table name parameter) and insert the tows into the archieve table (first column will be table name so I know what rows to retrieve when I restore the records).  This is working great no issues on sql execution but I loose all varbinary-blob information during conversion to varchar(max) data type - see example query below.

    insert into data_archieve_table (table_name,col_01,col_02,col_03,col_04,....)  
    select convert(varchar(max),customer_id) as col_01,convert(varchar(max),customer_first_name) as col_02,convert(varchar(max),customer_last_name) as col_03,convert(varchar(max),customer_dob_dt) as col_04,.... from customers

    Stored proc #2 will select all rows with first column = @table_name parameter for the proc to identify all rows I need to retrieve and based on the datatype of column (I identify this by looking at indormation_schema.columns) and use convert(datatype,col_xx) - See example query below

    This query also works great.  But now when I inspect any table which has varbinary data type (most of them are varbinary(max) which we use to store blobs) I can see that the data is truncated, this is not same as the original blob data, something is wrong with the conversion varbinary(max)->varchar(max)->varbinary(max)

    insert into customers(customer_id,customer_first_name,customer_last_name,customer_dob_dt,....) 
    select convert(bigint,col_01) as customer_id,convert(varchar(max),col_02) as customer_first_name,convert(varchar(max),col_03) as customer_last_name,convert(datetime,col_04) as customer_dob_dt,.... from data_archieve_table where table_name = 'customers'
    Wednesday, June 10, 2009 6:37 PM


  • Varbinary accepts a wider range of values than varchar, quite simply, so you can't expect a loss-less such conversion. It is like saying you have varchar, and convert it to inty and back to varchar and you are surprised that not all the letters are there. I didn't read though all your business-case behind your setup, but the technical reason for this is as I describe meaning you have some re-thiinking to do.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Kalman Toth Sunday, June 21, 2009 9:06 AM
    Wednesday, June 10, 2009 6:54 PM