ExecuteWithResultsAndMessage2 SQL-DMO and perl

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;
}Philip Bondi (pjbondi@.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, esquel@.sommarskog.se

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

ExecuteScalar Problems. Need Help

Hi all

I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.

'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:

...

<

asp:TemplateFieldHeaderText="Client"SortExpression="c_id"> <ItemTemplate> <asp:LabelID="lblClient"runat="server"Text='<%#GetClient(Eval("c_id")) %>'/> </ItemTemplate></asp:TemplateField>

...

This then calls: GetClientName - Which is as follows.

...

Public

Function GetClientName(ByVal ClientID)Dim ScalarValueAsString =""Dim myConnectionAsNew SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")Dim myCommandAsNew SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @.ClientID", myConnection)

myCommand.Parameters.Add(

"@.ClientID", SqlDbType.Int)

myCommand.Parameters(

"@.ClientID").Value = ClientID Try

myConnection.Open()

ScalarValue = myCommand.ExecuteScalar

Catch exAs Exception

Console.Write(ex.Message)

EndTry

If ScalarValue >""Then Return ScalarValue.ToString Else Return"<span style='color: #CCCCCC'>- NULL -</span>" EndIf

EndFunction

...

This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.

Any help in this matter would be great and really appreciated. Thanks.

David

Dave_Winchester:

ScalarValue = myCommand.ExecuteScalar

Since ExecuteScalar returns and object, try converting the result to a string before assigning it to the local variable.

|||

Hi

Thanks for the help. Now fixed - Also changed the Exception handling to be a bit better.

Dave

ExecuteNonQuery while dataReader still open

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:

 ...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
 ...

Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAMwhy don't you just do that in a procedure? It looks like it's a simple select then insert right?

Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId )
VALUES
SELECT 28, prodID, TypeID From WhereverYouGetThisFrom

GO|||First of all, thanks for answering so fast!

The main reason that is not inside a storedProcedure is requirements. The entire app is not using SP and this has to be done the same way.

LAM|||>>The entire app is not using SP

looks like you have bigger prbs to worry about.
anyway, to answer your q, since the connection is already open with the datareader, you need to open another connection to do the insert.

hth|||you all are making me cross.

command.CommandText = "Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId ) VALUES SELECT 28, prodID, TypeID From WhereverYouGetThisFrom";