1

I have been trying to convert a vabinary column to a string in SQL Server 2000. I have tried the recommended methods on this post: varbinary to string on SQL Server but not had any luck yet.

I have it working in SQL Server 2008, just not in SQL Server 2000.

My binary is 

0x000000000000000000000000000000000000000000000000000000000000C0FFFF000000000000

When I use select 

CONVERT(VARCHAR(1000), @data, 0)

in SQL Server 2008 it works fine, but when I use it in SQL Server 2000 the output I get is 

'ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ'

Has anyone come across this issue?

Community
  • 1
  • 1
Jeffrey
  • 2,095
  • 3
  • 20
  • 36

1 Answers1

0

you can do it SQL Server 2005/2000 by using XML.

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x000000000000000000000000000000000000000000000000000000000000C0FFFF000000000000

SELECT @bin AS actualvalue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS StringValue
bmsqldev
  • 2,627
  • 10
  • 31
  • 65
  • 3
    I don't think this code works on 2000. Aside from the use of `MAX` (but that's just used for the example here), it's also using the XML data type. Which was [New in SQL Server 2005](https://technet.microsoft.com/en-us/library/ms170809(v=sql.90).aspx) – Damien_The_Unbeliever Mar 11 '16 at 09:20
  • yes..i tested it in sql server 2005 but don't have the environment to test in 2000 – bmsqldev Mar 11 '16 at 09:29
  • Unfortuently this doesnt seem to work with 2000 either, I get: 37000 (170) [Microsoft][ODBC SQL Server Driver][SQL Server] Line 5: Incorrect syntax near '.'. – Jeffrey Mar 11 '16 at 10:07
  • @Jeffrey Damien has detailed the reason why in [the comment above](http://stackoverflow.com/questions/35936070/convert-varbinary-to-string-on-sql-server#comment59529210_35936152), XML data type wasn't introduced in SQL Server until SQL Server 2005. – user692942 Mar 11 '16 at 11:04
  • Yeh I've had a look at that, no ideas how else I can do it? – Jeffrey Mar 11 '16 at 11:22