0

My goal is to convert VARBINARY to VARCHAR. This is my code:

   DECLARE @objectSidAsByteArray varbinary(max)
   execute sp_executesql N'SELECT * FROM OPENQUERY("MyLinkedServer", 
   ''SELECT objectGUID FROM ''''LDAP://bla1.bla2'''' WHERE name = ''''myName 
    mySurname'''' '')', N'@ VARBINARY(max) OUTPUT', 
    @objectSidAsByteArray OUTPUT
    SELECT @objectSidAsByteArray

   DECLARE @objectSIDAsString VARCHAR(max) = CONVERT (NVARCHAR(max),@objectSidAsByteArray)
   SELECT @objectSIDAsString

Output:

   0x8585EEB0CBFD34449644B2A44B886FCC
   NULL

Event though @objectSidAsByteArray outputs the right value, there is something wrong with it. Its type seems to be ok (VARBINARY), isn't it? If I use the code below instead of @objectSidAsByteArray, the conversion works perfectly:

 DECLARE @objectSID varbinary(max)
 set @objectSID =  0x8585EEB0CBFD34449644B2A44B886FCC
 CONVERT (NVARCHAR(max),@objectSID)

So what am I doing wrong with the @objectSidAsByteArray?

JerryBox
  • 131
  • 1
  • 2
  • 13
  • Maybe look here https://stackoverflow.com/questions/12139073/sql-server-converting-varbinary-to-string – Piero Alberto Sep 07 '17 at 12:05
  • Why do you have the goal to convert VARBINARY to VARCHAR? Seems like you're trying to fix the symptom, not the underlaying problem. – jarlh Sep 07 '17 at 12:06
  • I suspect you want `CONVERT(NVARCHAR(max),@objectSidAsByteArray, 2)` for a string containing the hex digits as opposed to trying to convert the binary into utf? – Alex K. Sep 07 '17 at 12:06
  • PieroAlberto I've already seen that. It's totally not my case. Please read the question more carefully. jarlh Because I want to save the converted value as a string in another table on a linked server. Alex K. Didn't understand everything you said, but I want to convert the binary array into a string. In C# I use the System.Security.Principal.SecurityIdentifier class for that. – JerryBox Sep 07 '17 at 12:13

4 Answers4

1

Your sp_executesql statement didn't return the result in the variable @objectSidAsByteArray, try to execute this query:

DECLARE @objectSidAsByteArray varbinary(max)
execute sp_executesql N'SELECT TOP 1 @objectSidAsByteArray = objectGUID FROM OPENQUERY("MyLinkedServer", 
        ''SELECT objectGUID FROM ''''LDAP://bla1.bla2'''' WHERE name = ''''myName 
        mySurname'''' '')', N'@objectSidAsByteArray VARBINARY(max) OUTPUT', 
        @objectSidAsByteArray OUTPUT
SELECT @objectSidAsByteArray

DECLARE @objectSIDAsString VARCHAR(max) = CONVERT (NVARCHAR(max),@objectSidAsByteArray)
SELECT @objectSIDAsString
Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
0

Shouldn't it be

DECLARE @objectSIDAsString NVARCHAR(max)
Serg
  • 22,285
  • 5
  • 21
  • 48
0
    DECLARE @objectSidAsByteArray varbinary(max)
     execute sp_executesql N' select @i = 0x8585EEB0CBFD34449644B2A44B886FCC', N'@i VARBINARY(max) OUTPUT',   @objectSidAsByteArray OUTPUT
     SELECT @objectSidAsByteArray
    DECLARE @objectSIDAsString NVARCHAR(max) = CONVERT (NVARCHAR(max),@objectSidAsByteArray)
    SELECT @objectSIDAsString
    go      
DECLARE @objectSidAsByteArray varbinary(max)
 execute sp_executesql N' select 0x8585EEB0CBFD34449644B2A44B886FCC', N'@ VARBINARY(max) OUTPUT',   @objectSidAsByteArray OUTPUT
 SELECT @objectSidAsByteArray
Rahul Richhariya
  • 514
  • 3
  • 10
  • Ok , i think issue with you output variable. you have to declare it and store the value otherwise it will print . updated the answer. – Rahul Richhariya Sep 07 '17 at 13:02
0

You need to declare output variable which is not declared in your code. As mentioned in example below:-

DECLARE @objectSidAsByteArray varbinary(max)
execute sp_executesql N'SELECT @objectSidAsByteArray1 =0x21232F297A57A5A743894A0E4A801FC3', N'@objectSidAsByteArray1 VARBINARY(max) OUTPUT', @objectSidAsByteArray OUTPUT
SELECT @objectSidAsByteArray

DECLARE @objectSIDAsString VARCHAR(max) = CONVERT(NVARCHAR(max),@objectSidAsByteArray,1) 
SELECT @objectSIDAsString
chanchal
  • 26
  • 4