ExecuteWithResultsAndMessage2 SQL-DMO and perl

[Philip Bondi]

Hello to all:

I want to get the output from xp_msver through
ExecuteWithResultsAndMessage2 SQL-DMO and perl. This is the script
I've got so far. Can anyone give me a few more pointers?

use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO Object Library';

my ($server, $obj, $job, $jobs, $pathname, $c, $qr, $m );
my $srv;
my @servers = qw( KORTRIGHT );

$server = Win32::OLE->new('SQLDMO.SQLServer2')
or die "Could not create SQLDMO object.";

$server->{LoginSecure} = 1;

foreach $srv (@servers) {

$server->connect($srv, '', '');
if (Win32::OLE->LastError()) {
die "***Err: Could not connect to $srv.";
}

printf qq[\n%10.10s %-50.50s %10.10s %s\n], "Hostname:",
$server->HostName, "Server:", $srv;

my $dbs = $server->Databases();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the alerts collection in $srv.";
}

foreach $obj (in($dbs)) {
printf qq[%10.10s %-50.50s %10.10s %s\n], "Database:",
$obj->Name, "Size:", $obj->Size." Mb";
}

$jobs = $server->Jobserver;

foreach $obj (in($jobs->Jobs() ) ) {
if( $obj->Name =~ /'(.*)'/ ) {
$job = $1;
}
foreach my $step ( in($obj->Jobsteps()) ) {
if( $step->command =~ /-BkUpDB "(\S+)"/ ) {
$pathname = $1;
printf qq[%10.10s %s\n], "Job:", $job;
printf qq[%10.10s %s\n], "pathname:", $pathname;
#print $step->command.qq[\n];
}
}
}

$c = "xp_msver";
$qr = $server->ExecuteWithResultsAndMessages2( $c, $m );
$server->DisConnect;
}

[Erland Sommarskog]

Philip Bondi (pj*****@SystemDatabase.com) writes:

I want to get the output from xp_msver through
ExecuteWithResultsAndMessage2 SQL-DMO and perl. This is the script
I've got so far. Can anyone give me a few more pointers?
And the problem is?

OK, so I ran your script, and this line:
$qr = $server->ExecuteWithResultsAndMessages2( $c, $m );

resulted in:

Win32::OLE(0.1701) error 0x8002000f: "Parameter not optional"
in METHOD/PROPERTYGET "ExecuteWithResultsAndMessages2" at slask.pl line 51

This is because you are passing an undef in $m. The correct call would be:

$qr = $server->ExecuteWithResultsAndMessages2( $c, \$m );

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp