Hi to all the Monks out there. I'm trying to extract a result from a MS SQL Server Stored Procedure with this code: 
    use DBI;
    
    my $dsn = "Provider=sqloledb;Trusted Connection=yes; Server=$datab
+aseHost;Database=$databaseName";
        
    my $sqlDBHandler = DBI->connect("DBI:ADO:$dsn", $databaseUsername,
+ $databasePassword, {RaiseError=>0,AutoCommit=>1});
    
    my $sqlStatement = 'EXEC GenerateNewPickConfirmationMessage';

    my $sqlSelect = $sqlDBHandler->prepare($sqlStatement);

    $sqlSelect->execute;

    $sqlResult=$sqlSelect->dump_results();
    
    $sqlDBHandler->disconnect;

[download]

As you guessed it my SP is called GenerateNewPickConfirmationMessage, and returns a numeric value. It works well when called from a query in SQL Server, but I get an error from Perl that looks like this: "0 rows (-900: Statement handle not marked as Active.) DBD::ADO::st dump_results failed: Statement handle not marked as Active. at test GetMessageID.pl line 29."

I've searched around and tried a few things, but I always end up with this error. When I set my $sqlStatement variable to a basic select, it works well.

I've read somewhere that ADO needs a select to be "Active" or be able to receive a result.

Anyway, have any Gurus out there experienced something similar or have a simple solutions for me. By the way, I don't have to use ADO, if you feel another driver or package would do the job, I'm open minded.

Thanks

Dan

In reply to Retrieving a result from a MS SQL Stored Procedure using DBI and ADO under Win32 Perl by bougon

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.