Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

MS SQL Stored Procedure Syntax

by slojuggler (Beadle)
on Sep 27, 2001 at 05:33 UTC ( [id://114973] : perlquestion . print w/replies, xml ) Need Help??

slojuggler has asked for the wisdom of the Perl Monks concerning the following question:

I tried

{call sp_spaceused};

per this node SQL stored procedures

but it results in a syntax error. I also tried


That didn't seem to help. Error messages...

Can someone help me with the syntax? This seemingly "easy" problem has resulted in 2.5 hours of playing with syntax. I've looked at "Super Search" and various FAQs without success. In the same vein, I'm trying to write a script that goes through the various tables to report disk usage. Am I reinventing the wheel and has this been done before? Also, is it efficient to run through each table running stored-procedure "sp_spaceused" and gleaning the statistics, or is their better way? Thanks from this weary monk...

Replies are listed 'Best First'.
Re: MS SQL Stored Procedure Syntax
by Arguile (Hermit) on Sep 27, 2001 at 09:46 UTC

    No, what's he's trying to do here is EXEC[UTE] a stored procedure in MS SQL from the sound of it. The CALL syntax is Oracle's, you're getting your SQL dialects mixed up.

    $sth = $dbh->prepare('EXEC sp_spaceused');

    MSDN has an online transact SQL reference which I suggest you bookmark. Dealing with multiple dialects of the same language can get confusing no matter your experience level.

    Abbrv. Superset/Dialect Name Vendor
    PL/SQL Procedural Language/Structured Query Language Oracle
    T-SQL Transact[ional] Structured Query Language Microsoft
Re: MS SQL Stored Procedure Syntax
by greywolf (Priest) on Sep 27, 2001 at 05:46 UTC
    Assuming that 'sp_spaceused' is a scalar that contains the entire statement try:


    mr greywolf
Re: MS SQL Stored Procedure Syntax
by cacharbe (Curate) on Sep 27, 2001 at 06:43 UTC
    What Platform, what database connectors are you using?

    Try here for some details.


Re: MS SQL Stored Procedure Syntax
by slojuggler (Beadle) on Sep 27, 2001 at 23:27 UTC
    Platform: WinNT

    Connector: DBI::ODBC

    Here's my revised code:

    #!/usr/bin/perl -w use strict; use DBI; #Database handle object my $dbh; my $user='sa'; my $password=''; #Statement handle object my $sth; #General return code my $rc; #Connect to the SQL database CQmaster $dbh = DBI->connect("dbi:ODBC:CQmaster",$user,$password) || die "Can't + open! $!"; #Prepare the query and execute it $sth= $dbh->prepare("SELECT * FROM sysdatabases"); $sth->execute(); while (my @row = $sth->fetchrow_array) { print "@row\n"; } #Call stored procedure sp_spaceused $sth=$dbh->prepare('EXEC sp_spaceused'); $sth->execute(); while (my @row = $sth->fetchrow_array) { print "@row\n"; } $rc = $dbh->disconnect;
    And I get the following error message:

    DBI::db=HASH(0x1c9e4f4)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at C:\scripts\FIRSTODBC.PL line 33.

    How can I extract the output of the stored procedure sp_spaceused without resulting in this error message?

      DBI should clean things up for you when you read the end of the result set, but the fix is probably to call finish() manually.
      $sth->finish(); $rc = $dbh->disconnect();
      It's just a warning that the statement handle is still marked as active. That could mean you have more results to read, or that the server's allocated some resource and hasn't give it back yet. You could disable warnings, but it's probably better just to call finish.


      Doubt if this has anything to do with your problem, but I see a couple of other problems.
      This is wrong:
      $dbh = DBI->connect("dbi:ODBC:CQmaster",$user,$password) || die "Can't + open! $!";
      First, $! does not return error messages from DBI, you want $DBI::errstr instead (or RaiseError, see the next note).
      Second, you're not checking the status of the prepare and execute statements. You could either check each statement, or use RaiseError on the connect (see the DBI docs).

      I doubt that'll magically fix anything, so after that, if the warning still occurs, you can try putting $sth->finish after one or both of the fetch loops, though it seems like it should be unnecessary (maybe it gets confused with a stored procedure?).