I have written a script that takes in a list of server names from a text file, connects to each (one at a time) and runs a list of stored procedures (also from a text file). The output is then sent to a text file as tab delimited rows.

Everything works great until it tries to output the results of a stored procedure like sp_helplogins where one of the columns includes an hex formated SID value (e.g. 0x151552dabe9d1ce4e8dd6b635f23d4c). Perl seems to be converting this to ascii and ends up outputting garbage (To make matters worse the garbage includes random tabs and carriage returns).

Is there some way that I can either tell perl to pull in the data as-is or to convert back to a meaningful value once it's fetched? I'd like to avoid writing special code per stored procedure.

Please help, and thank you!

PS: I am a perl novice, so please speak slowly and use small words :)

Here is my code:

#!/usr/bin/perl -w

use strict;
use DBI;
use Term::ReadKey;

#Declarations

my ($File, $Entry, $username, $password, $entry, $dsn, $resultfile, $d
+bserver, @dbserverlist, $dbh, @data, $data, $sth);
my ($sp, $output, @splist, $command, @mtable, $mtable, @databases, $da
+tabase, @rtable, $rtable, $sth2, $sth3, @tablesps);
my (%attr, $tablesp, @names, $name, $sth4, $sth5, $fh, $dsn2, $dbh2, $
+dsn3, $dbh3);

print "What is the username you would like to use to connect? (e.g. 's
+a')\n";
print "-->";
$Entry = <STDIN>;
chomp($Entry);
$username = $Entry;
print "\n";
print "What is the password? (Password will not show on the console as
+ you type.)\n";
ReadMode 'noecho';
print "-->";
$Entry = <STDIN>;
chomp($Entry);
$password = $Entry;
ReadMode 'restore';
print "\n";
open(ERRFILE, ">>script\\Output\\Errorlog.txt") or print "An error log
+ file could not be created.  The error given is: $!\n";
open(INFILE, "Script\\Input\\DB_Server_List.txt") or print "The input 
+file could not be opened. The error given is: $!\n";
@dbserverlist = <INFILE>;
%attr = (PrintError => 0, RaiseError => 0);

DB: foreach $dbserver (@dbserverlist) {
    #Create DB handle
    chomp $dbserver;
    $dsn = "driver={SQL Server};Server=$dbserver;";
    $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $password, \%attr)
+ or print ERRFILE join("\t", $dbserver, $DBI::errstr, "\n", "\n") and
+ next DB;
    print "Connected to $dbserver.\n  Running data scripts...\n";
    #Run subs 
    storedprocedures ($dbserver);
    #mastertables ($dbserver);
    #tabsps ($dbserver);
    #regtables ($dbserver);
    #$dbh->disconnect;
    SKIP:
    print "  Complete.\n";
    }
$password = "";
close(INFILE);
close(ERRFILE);
exit;

sub storedprocedures {
    open(INFILE, "script\\Input\\Master_Level_Stored_Procedures.txt") 
+or print "The input file could not be opened.  The error given is: $!
+\n";
    @splist = <INFILE>;
    my $dbserver=shift;
    SP: foreach $sp (@splist) {    
        chomp $sp;
        #Open output file
        $output = 'Script\\Output\\' . $sp . '.txt';
        open(OUTFILE, ">>$output") or warn "The Output file could not 
+be opened.";
        binmode(OUTFILE, ":utf8");
        #Send commands
        $command = 'exec ' . $sp;
        print "$command. \n";
        $sth = $dbh->prepare("$command");
        $sth->execute or print ERRFILE join("\t", $dbserver, "$DBI::er
+rstr", $sp, "\n") and next SP;
        while(@data = $sth->fetchrow_array) { 
            foreach (@data) {
                  $_ = 'Nada' unless defined;
                }
            print OUTFILE join("\t", $dbserver, @data), "\n";
            }
        close(OUTFILE);
        }
    close(INFILE);
    }
[download]