Help with DBI to get HDD size of C drive of remote PC running MS-SQL Express 2005

[David Evans]

Hi Guys
 
I hope someone can help me.  I know the SQL works fine, but I can't work
out how to get it to work with DBIx::SQLEngine.

Cheers


Dave
 
This is the SQL I want to execute (and works from SQL Management Studio
2005):
  *********Begin SQL Code*********
IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'GetDriveSize'
        AND ROUTINE_SCHEMA = 'dbo'
        AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
        DROP FUNCTION dbo.GetDriveSize
        PRINT 'Dropped dbo.GetDriveSize'
END

GO

CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1)) RETURNS
NUMERIC(20)
BEGIN
        DECLARE @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv
INTEGER, @drivesize VARCHAR(20)
        SET @getdrive = 'GetDrive("' + @driveletter + '")'
        EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT
        IF @rs = 0
        EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
        IF @rs = 0
        EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
        IF @rs<> 0
        SET @drivesize = NULL
        EXEC sp_OADestroy @drv
        EXEC sp_OADestroy @fso
        RETURN @drivesize
END

GO

SELECT round(dbo.GetDriveSize('C')/(1073741824),0)
  *********End SQL Code*********

This is my Perl:

  *********Begin Perl Code*********
use DBIx::SQLEngine;

$sqllist1 = "IF EXISTS (\nSELECT 1 FROM
INFORMATION_SCHEMA.ROUTINES\nWHERE ROUTINE_NAME = 'GetDriveSize'\nAND
ROUTINE_SCHEMA = 'dbo'\nAND ROUTINE_TYPE = 'FUNCTION'\n)\nBEGIN\nDROP
FUNCTION dbo.GetDriveSize\nEND\nGO";
$sqllist2 = "CREATE FUNCTION dbo.GetDriveSize (\...@driveletter CHAR(1))
RETURNS NUMERIC(20)\nBEGIN\nDECLARE  \...@rs INTEGER, \...@fso INTEGER,
\...@getdrive VARCHAR(13), \...@drv INTEGER, \...@drivesize VARCHAR(20)\nSET
\...@getdrive = 'GetDrive(\"' + \...@driveletter + '\")'\nEXEC \...@rs =
sp_OACreate 'Scripting.FileSystemObject', \...@fso OUTPUT\nIF \...@rs =
0\nEXEC \...@rs = sp_OAMethod \...@fso, \...@getdrive, \...@drv OUTPUT\nIF 
\...@rs =
0\nEXEC \...@rs = sp_OAGetProperty \...@drv,'TotalSize', \...@drivesize
OUTPUT\nIF \...@rs<> 0\nSET \...@drivesize = NULL\nEXEC sp_OADestroy
\...@drv\nexec sp_OADestroy \...@fso\nreturn \...@drivesize\nend\ngo";
$sqllist3 = "SELECT round(dbo.GetDriveSize('C')/(1073741824),0)";


$ip = "1.1.1.1";
print STDERR "ip = **$ip**\n";

my $dsnSQL_Store = "Driver={SQL Server};Server=$ip;Database=DB";

if (my $dbhstr = DBI->connect( "dbi:ODBC:$dsnSQL_Store", "sa", "wibble"
)) {
        print STDERR "Connected to $device_id database!\n";
        print STDERR "sql = $sql\n";
        my $fish1;
        print STDERR "sqllist = $sqllist1\n";
        $fish1 = $dbhstr->prepare($sqllist1);
        $fish1->execute();
        print STDERR "sqllist = $sqllist2\n";
        $fish2 = $dbhstr->prepare($sqllist2);
        $fish2->execute();
        my $fish = $dbhstr->selectall_array($sqllist3);
        print STDERR "Free Gb = $fish\n"
}
else {
        warn "Can't connect to $ip";
        print "***ERR***\n";
}
  *********End Perl Code*********


  *********Start Sys Info*********
Windows XP SP3
Perl 5.8.8 Build 822
DBI 1.607
DBIx-SQLEngine 0.93
MS-SQL 2005 Express
  *********End Sys Info*********

David Evans
Monsoon Accessorize Limited
1 Nicholas Road
London
 W11 4AN
 
Tel:+44(0)2033723411
Fax:+44(0)2033723040
E-mail:dev...@monsoon.co.uk
Please consider the environment before printing this email.
 
 

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs