Re: SQL Server SID value output as garbage using DBI

[poj]

What data type is the SID column ?. I suspect it is binary, it only looks like hex in your Management client. This small demo program shows two option, use convert in your SQL statement or unpack the fetched value.

use strict;
use DBI;
my $dbh = mssql(); # connect

# temp test table setup
my $t = '#bintest';
$dbh ->do("CREATE TABLE $t
         (bin1 varbinary(max))");  

my $hex_in = '0x151552dabe9d1ce4e8dd6b635f23d4c0';
$dbh->do("INSERT INTO $t (bin1) 
          VALUES ( CONVERT(varbinary(max), ?, 1) )",undef,$hex_in);
#          

my ($bin_out) = $dbh->selectrow_array(
  "SELECT bin1 FROM $t");

# style 1 adds 0x
my ($hex_out) = $dbh->selectrow_array(
  "SELECT CONVERT(varchar(max),bin1,1) 
   FROM $t");

print "hex input  = $hex_in\n";
print "bin out    = $bin_out\n";
print 'hex output = 0x',unpack('H*',$bin_out),"\n"; 
print "hex output = $hex_out\n";

poj

[mje]

Obviously in this case he cannot use convert as the procedure is a system defined procedure I doubt he can change.

As the current maintainer of DBD::ODBC, which I presume the OP is using, I thought you could just do "$sth->bind_col(2, undef, {TYPE => SQL_CHAR})" after the execute but it appears you cannot. If the OP wants to confirm which DBD they were using I will look into allowing a binary column's type to be overriden.