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