Post reply

Trying to understand SQL_Variants

  • July 1, 2010 at 12:00 pm

    #237171

    Here's my situation:

    I have a column in a table which is defined as SQL_Variant which seems to contain varbinary data... using the following query:

    Selectsql_variant_property(@Test, 'BaseType'). --> the result is "varbinary"

    I need to modify (Replace some text strings) information within this column and save it back into the same format.

    when I query the record it is obviously not readable...

    So, I convert the record into varchar... I'm able to read the content.

    Without making any changes, I convert it again into varbinary and then compare the binary values (the original value and the newly re-converted value) and they aren't identical.

    Apparently, the code that saves the contant of this column is an XML that is saved as binary... but since I'm not familiar with .net and that I would like to do everything with a Stored procedure... Would anyone know how this can be done?

    Thanks

    JG

  • July 1, 2010 at 12:16 pm

    #1187839

    Found It!

    declare @test-2 as SQL_Variant,@test2 as Varchar(max), @Test3 as SQL_Variant

    select @test-2= defaultvalue from mytable where MyColumn = 'MyTest'

    Select @test-2 -- this is a binary value

    Select @Test2 = convert(varchar(max),@Test) -- this is Readable

    Select @Test3 = convert(varbinary(8000),@Test2) -- this is reconverted to varbiary

    this last line wasn't working because I was using the varbinary, which truncates at 30 if I'm not mistaken. I had also tried varbinary(MAX) which was incompatible with SQL_Variant

    As soon as we put the varbinary(8000)... it worked.

    Comparing the original binary SQL_Variant with the newly created Binary SQL_Variant are now identical.

    I can now take it from there and replace the parts of the strings that I need to replace.

    Thanks

    JG

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply