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]

In reply to SQL Server SID value output as garbage using DBI by Mandi

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.