Prev: GhostCleanUpTask - every 4 seconds!?
Next: TEMPDB full
From: Tim_Mac on 6 Dec 2006 07:53
hi,
when i'm in sql 2005 management studio and i execute some sql command,
the window shows 2 tabs, one for the results, and another for the
messages associated with the command.
how can i collect these messages using SMO?

SQLDMO included a method called ExecuteWithResultsAndMessages, but
there does not appear to be any such method with SMO. any ideas?

using c#, i can get the DataSet/results with the following code:
DataSet ds = CurrentDB.ExecuteWithResults(this.txtSql.Text)

thanks
tim

From: Dan Guzman on 6 Dec 2006 08:16
> SQLDMO included a method called ExecuteWithResultsAndMessages, but
> there does not appear to be any such method with SMO. any ideas?

Rather than provide a separate method, SMO allows you can handle the
ServerConnection.InfoMessage event and invoke the normal ExecuteWithResults
or ExecuteNonQuery method. This is the same pattern used in ADO.NET.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tim_Mac" <mackey.tim(a)gmail.com> wrote in message
news:1165409620.751970.137210(a)j72g2000cwa.googlegroups.com...
> hi,
> when i'm in sql 2005 management studio and i execute some sql command,
> the window shows 2 tabs, one for the results, and another for the
> messages associated with the command.
> how can i collect these messages using SMO?
>
> SQLDMO included a method called ExecuteWithResultsAndMessages, but
> there does not appear to be any such method with SMO. any ideas?
>
> using c#, i can get the DataSet/results with the following code:
> DataSet ds = CurrentDB.ExecuteWithResults(this.txtSql.Text)
>
> thanks
> tim
>

From: Tim_Mac on 6 Dec 2006 11:24
hi Dan,
many thanks for the reply. i've implemented this and although my
eventhandler responds to some InfoMessage events, the ones i really
want are not raised.
For example, when using SMO properties, a lot of events such as "USE
[Database]" are raised. But when i run an ExecuteNonQuery event, i get
no response on the eventhandler.
not sure if you're a C# programmer or not, but on the off-chance, i've
pasted a simplified version of my code here (VS 2005 console project).
i would be very grateful if you might have a minute to see what i might
be doing wrong?
i have set up the console app to run the sql stuff on a delegate and
then wait 10 seconds to give the events a chance to trickle back from
Sql Server. when i run it, i do see the StatementExecuted event, but
no InfoMessage :(
i've also tried it with and without disconnecting after executing the
command, no change.

many thanks
tim


using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Threading;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;


namespace TestSqlEvents
{
class Program
{
static Server sql = new Server();

static void Main(string[] args)
{
// run the sql stuff on a delegate, to allow the events to be raised

// after control flow would otherwise end, which would terminate the
program
RunnerDelegate del = new RunnerDelegate(Runner);
del.BeginInvoke(null, null);
Thread.Sleep(10000);
}

delegate void RunnerDelegate();
private static void Runner()
{
// set up events
sql.ConnectionContext.InfoMessage += new
System.Data.SqlClient.SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
sql.ConnectionContext.ServerMessage += new
ServerMessageEventHandler(ConnectionContext_ServerMessage);
sql.ConnectionContext.StatementExecuted += new
StatementEventHandler(ConnectionContext_StatementExecuted);

// connection properties
sql.ConnectionContext.DatabaseName = "WebManager";
sql.ConnectionContext.LoginSecure = true;
sql.ConnectionContext.ServerInstance = @".\SQLEXPRESS";

// connect and execute query
sql.ConnectionContext.Connect();
try
{
sql.ConnectionContext.ExecuteNonQuery("update Pages set Hits=0
where ID=0");
}
catch(Exception ex)
{
Console.WriteLine("ERROR: " + ex.GetBaseException().Message);
}
finally
{
sql.ConnectionContext.Disconnect();
}
}

static void ConnectionContext_StatementExecuted(object sender,
StatementEventArgs e)
{
Console.WriteLine("** Statement: " + e.SqlStatement);
}

static void ConnectionContext_ServerMessage(object sender,
ServerMessageEventArgs e)
{
Console.WriteLine("** Server Message: " + e.Error.Message);
}

static void ConnectionContext_InfoMessage(object sender,
System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
Console.WriteLine("** Info Message: " + e.Errors.Count + " errors");
Console.WriteLine("** Info Message: " + e.Message);
}
}
}

From: Dan Guzman on 6 Dec 2006 21:53
Hi. Tim.

The InfoMessage event is raised only in response to informational messages
with severity <= 10). An UPDATE statement doesn't usually raise such
messages. I ran your code replacing the UPDATE statement with USE and PRINT
statements and the output shows that both the ServerMessage and InfoMessage
delegates were invoked as expected. What sort of messages were you
expecting from the UPDATE?

** Statement: USE tempdb PRINT 'test'
** Info Message: 2 errors
** Info Message: Changed database context to 'tempdb'.
test
** Server Message: Changed database context to 'tempdb'.
** Server Message: test


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tim_Mac" <mackey.tim(a)gmail.com> wrote in message
news:1165422254.993774.288280(a)73g2000cwn.googlegroups.com...
> hi Dan,
> many thanks for the reply. i've implemented this and although my
> eventhandler responds to some InfoMessage events, the ones i really
> want are not raised.
> For example, when using SMO properties, a lot of events such as "USE
> [Database]" are raised. But when i run an ExecuteNonQuery event, i get
> no response on the eventhandler.
> not sure if you're a C# programmer or not, but on the off-chance, i've
> pasted a simplified version of my code here (VS 2005 console project).
> i would be very grateful if you might have a minute to see what i might
> be doing wrong?
> i have set up the console app to run the sql stuff on a delegate and
> then wait 10 seconds to give the events a chance to trickle back from
> Sql Server. when i run it, i do see the StatementExecuted event, but
> no InfoMessage :(
> i've also tried it with and without disconnecting after executing the
> command, no change.
>
> many thanks
> tim
>
>
> using System;
> using System.Collections.Generic;
> using System.Data.SqlClient;
> using System.Text;
> using System.Threading;
>
> using Microsoft.SqlServer.Management.Smo;
> using Microsoft.SqlServer.Management.Common;
> using Microsoft.SqlServer.Management.Trace;
>
>
> namespace TestSqlEvents
> {
> class Program
> {
> static Server sql = new Server();
>
> static void Main(string[] args)
> {
> // run the sql stuff on a delegate, to allow the events to be raised
>
> // after control flow would otherwise end, which would terminate the
> program
> RunnerDelegate del = new RunnerDelegate(Runner);
> del.BeginInvoke(null, null);
> Thread.Sleep(10000);
> }
>
> delegate void RunnerDelegate();
> private static void Runner()
> {
> // set up events
> sql.ConnectionContext.InfoMessage += new
> System.Data.SqlClient.SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
> sql.ConnectionContext.ServerMessage += new
> ServerMessageEventHandler(ConnectionContext_ServerMessage);
> sql.ConnectionContext.StatementExecuted += new
> StatementEventHandler(ConnectionContext_StatementExecuted);
>
> // connection properties
> sql.ConnectionContext.DatabaseName = "WebManager";
> sql.ConnectionContext.LoginSecure = true;
> sql.ConnectionContext.ServerInstance = @".\SQLEXPRESS";
>
> // connect and execute query
> sql.ConnectionContext.Connect();
> try
> {
> sql.ConnectionContext.ExecuteNonQuery("update Pages set Hits=0
> where ID=0");
> }
> catch(Exception ex)
> {
> Console.WriteLine("ERROR: " + ex.GetBaseException().Message);
> }
> finally
> {
> sql.ConnectionContext.Disconnect();
> }
> }
>
> static void ConnectionContext_StatementExecuted(object sender,
> StatementEventArgs e)
> {
> Console.WriteLine("** Statement: " + e.SqlStatement);
> }
>
> static void ConnectionContext_ServerMessage(object sender,
> ServerMessageEventArgs e)
> {
> Console.WriteLine("** Server Message: " + e.Error.Message);
> }
>
> static void ConnectionContext_InfoMessage(object sender,
> System.Data.SqlClient.SqlInfoMessageEventArgs e)
> {
> Console.WriteLine("** Info Message: " + e.Errors.Count + " errors");
> Console.WriteLine("** Info Message: " + e.Message);
> }
> }
> }
>

From: Tim_Mac on 7 Dec 2006 06:36
hi dan, thanks for the follow up.
when i run the same query in a management studio query window, i get
the message "x rows affected by query". similarly when i run a command
to create a stored procedure, i get a message saying the command
completed successfully. these are the ones i'm interested in.
it's not too serious if it isn't possible, because i know that specific
errors throw exceptions which i can handle. it's just for
informational purposes with the app i'm developing.

thanks
tim

 |  Next  |  Last
Pages: 1 2
Prev: GhostCleanUpTask - every 4 seconds!?
Next: TEMPDB full