How do I get the output from Database.ExecuteNonQuery?

Article
08/13/2008

Question

_{Wednesday, August 13, 2008 9:35 PM | 3 votes}

I would like to use the Database.ExecuteNonQuery or something similar to execute a sql script and then capture the output from all of the print statements that is generated. However, I cannot find a way to get standard output back from this method. I know I could just start up a sqlcmd process, but the SMO objects seem cleaner. Is there a way for me to capture the print output from sql using any of the SMO objects?

All replies (18)

_{Thursday, August 14, 2008 10:28 AM}

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

For Select statement You can use ExecuteScalar(); which will return results.

Hope it helps

_{Thursday, August 14, 2008 12:52 PM | 1 vote}

I am referring to the Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery, not DbCommand.ExecuteNonQuery. The reason I am using this one is that it allows me to execute a large batch of sql commands (ie: lots of go statements). An older version of my code used osql to capture the print output from the sql. I am trying to find something more elegant than osql or sqlcmd, which is why I am hoping to find a solution in the Microsoft.SqlServer.Management.Smo namespace. If anyone knows of a different set of classes that will allow me to execute large sql batches and capture print output from them, please let me know. I can believe I am on the wrong path.

sql:

print 'something'

print 'something else'

I want to be able to inspect the output:

something

something else

_{Friday, August 15, 2008 3:16 PM}

there is nothing ready made to do this kind of thing althoug it would be interesting to find out how exactly does SSMS populate its Message pane, sorry I could not be of more help.
Also I don't know why exactly you need to execute batches of sql, are they created during run-time or just populated with parametres during run-time if so consider writing stored procedure. It is generally quicker.

_{Monday, August 18, 2008 11:11 AM}

PS. there is an OUTPUT statement in Microsoft SQL Server 2005 and 2008, whose syntax goes like this:

DECLARE @InsertedRows TABLE (ProductID INT, ProductNumber NVARCHAR(25) )

INSERT Product ([Name], ProductNumber, ListPrice)
    OUTPUT inserted.ProductID,
           inserted.ProductNumber
    INTO @InsertedRows
SELECT 
    [Name], ProductNumber, ListPrice
FROM
    ProductsToInsert AS I
WHERE
  NOT EXISTS (SELECT 1 FROM Product 
              WHERE ProductNumber = I.ProductNumber)



This would effectively create and fill temp table that will contain nonquery results, 
as for messages that SSMS writes out still no idea.

_{Thursday, October 16, 2008 5:24 AM}

I have in my stored procedure the variable @web_message as an OUTPUT.

this example writes to a "ResultLabel":

using (var connection = new SqlConnection(connectionString))

{

const string commandText = "person_add";

var command = new SqlCommand(commandText, connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@first_name", txtFirstName);

command.Parameters.AddWithValue("@last_name", txtLastName);

command.Parameters.AddWithValue("@family_id", ddlFamily_id);

command.Parameters.AddWithValue("@email", txtEmail);

var outParameter = new SqlParameter();

outParameter.ParameterName = "@web_message";//@EmployeeID

outParameter.Direction = ParameterDirection.Output;

outParameter.DbType = DbType.String; //DbType.Int32;

outParameter.Size = 2000;

command.Parameters.Add(outParameter);

connection.Open();

int rowsAffected = command.ExecuteNonQuery();

if (rowsAffected == 1)

{

ResultLabel = "1 rows have been affected with this operation";

}

ResultLabel += "" + "" + command.Parameters["@web_message"].Value + "<br />";

_{Thursday, November 13, 2008 9:35 PM}

Well i was trying to get the message output from a stored procedure using SMO as well as was just as frustrated in finding a simple solution. I could not find a good way to do this.

sp_help_revlogin was the sp i need the message output from .

i needed the output message to use in my SMO code to migrate login passwords.

My Solution:

This may not be the best way but my solution was to save the script and execute it using sqlcmd with -o option. This outputs the message nicely in a text file and i just extract the strings i need for the rest of the code using the System.IO file access objects.

Hope this helps anyone looking for this same solution.

_{Thursday, November 13, 2008 9:48 PM}

You need to concatenate all message into a single variable and then select the variable as a last line of the stored procedure:

SELECT @myOUTPUT

Then Database.ExecuteScalar

A.D.T.

_{Thursday, November 20, 2008 6:30 PM}

I'm still interested in finding out if there is a way to do this, too. So far, none of the responses have been useful, except for the OP falling back to sqlcmd. I ONLY want the output of the PRINT statements.

So, can the output of a batch be captured, or the messages discovered somehow through smo?

Not interested in

Output parameters
Variables which are selected at the end of a file (That is a recordset, not the prints/etc)
Record counts
Etc

_{Thursday, April 1, 2010 7:10 PM}

After struggling a bit I got the solution to get the return value using Database.ExecuteNonQuery :)

you need to create a new parameter with ParameterDirection.ReturnValue as shown in the below code snippet... It should resolve your issue and the beauty of it is you need not add any parameters to the existing stored procedure.... Enjoy Coding!

    Dim db1 As Database = DatabaseFactory.CreateDatabase("MyDataBase")

           Using cmd As DbCommand = db1.GetStoredProcCommand("spMyStoredProcedure")
                Using cmdParamsHelper As DbCommandParamsHelper = DbCommandParamsHelper.Create(cmd)
                    cmdParamsHelper.AddString("Name", 200, False, name)
                    cmdParamsHelper.AddInt32("Number", False, number)
                    cmdParamsHelper.AddInt32("returnValue", False, SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    db1.ExecuteNonQuery(cmd)
                    Dim ReturnCode As Integer = cmdParamsHelper.GetInt32("returnValue")
                End Using
            End Using

Please mark the post answered your question as the answer, and mark other helpful posts as helpful.

-Mohammed Ghouse Ibne Barq Kadapavi

_{Thursday, April 1, 2010 7:13 PM}

After struggling a bit I got the solution to get the return value using Database.ExecuteNonQuery :)

  </span>Dim db1 As Database = DatabaseFactory.CreateDatabase("MyDataBase")

           Using cmd As DbCommand = db1.GetStoredProcCommand("spMyStoredProcedure")
                Using cmdParamsHelper As DbCommandParamsHelper = DbCommandParamsHelper.Create(cmd)
                    cmdParamsHelper.AddString("Name", 200, False, name)
                    cmdParamsHelper.AddInt32("Number", False, number)
                    cmdParamsHelper.AddInt32("returnValue", False, SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    db1.ExecuteNonQuery(cmd)
                    Dim ReturnCode As Integer = cmdParamsHelper.GetInt32("returnValue")
                End Using
            End Using

Please mark the post answered your question as the answer, and mark other helpful posts as helpful.

**-Mohammed Ghouse Ibne Barq Kadapavi **

_{Thursday, April 1, 2010 7:13 PM}

After struggling a bit I got the solution to get the return value using Database.ExecuteNonQuery :)

  </span>Dim db1 As Database = DatabaseFactory.CreateDatabase("MyDataBase")

           Using cmd As DbCommand = db1.GetStoredProcCommand("spMyStoredProcedure")
                Using cmdParamsHelper As DbCommandParamsHelper = DbCommandParamsHelper.Create(cmd)
                    cmdParamsHelper.AddString("Name", 200, False, name)
                    cmdParamsHelper.AddInt32("Number", False, number)
                    cmdParamsHelper.AddInt32("returnValue", False, SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    db1.ExecuteNonQuery(cmd)
                    Dim ReturnCode As Integer = cmdParamsHelper.GetInt32("returnValue")
                End Using
            End Using

Please mark the post answered your question as the answer, and mark other helpful posts as helpful.

**-Mohammed Ghouse Ibne Barq Kadapavi **

_{Monday, May 3, 2010 9:44 PM | 9 votes}

You could use Smo for this. It has a built in eventhandler SqlInfoMessageEventHandler:

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

...

Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
int rowsAffected = server.ConnectionContext.ExecuteNonQuery(sqlScript);

...

void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}

_{Thursday, March 31, 2011 1:15 AM}

Hello all. I am curious if any real solution to this has been found. While all of the posters mean well, it is clear to me that they do not have a clue what you are talking about. If anyone has any ideas, it would be most helpful for them to post here as I find myself in the same situation as harry4. I can add a handler to the serverconnection object but it just tells me that it changed the database context, not the textual result of the database.executenonquery function. Anyone have a clue?

NumbLock

pcgalen

_{Thursday, September 1, 2011 3:20 PM | 1 vote}

SqlClient also has a bulit in eventhandler for SqlInfoMessageEventHandler it is named InfoMessage.

_{Tuesday, December 6, 2011 6:48 PM}

harry4, Have you found any other solution to this problem? I also have the same need.

_{Monday, January 14, 2013 8:11 PM}

harry4, has there been any sign of a solution to this glaring omission?

I have 2000+ scripts to run in about 20 files. I do not wish to change any of them to make return parameters or other workarounds. When I run them in SQL Management Studio (or SQLCMD), I can capture the stdoutput written to the console, including PRINT output, error messages, etc.

But with SMO there seems to be no option to do this.

I can either run with Default option (which breaks on the first error, and thus fails to run all the scripts in a file) or ExecutionTypes.ContinueOnError (which fails to report any errors or console output, but does attempt to run all the scripts).

Neither option is acceptable.

_{Tuesday, January 21, 2014 1:00 PM}

can you please convert this in to powershell.. i am looking for this solution but in powershell script form... TIA

_{Wednesday, January 22, 2014 12:54 PM}

But with SMO there seems to be no option to do this.

I can either run with Default option (which breaks on the first error, and thus fails to run all the scripts in a file) or ExecutionTypes.ContinueOnError (which fails to report any errors or console output, but does attempt to run all the scripts).

Neither option is acceptable.

One method is to execute the batches individually like the console app example below (SqlClient could be used instead of SMO). The downside with this dumbed-down parser technique is that this will not properly handle a GO batch terminator embedded in a comment block.

using System;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;

namespace SMO
{
    class Program
    {

        private static string serverName = @".";
        private static string scriptFileName = @"C:\Scripts\Script.sql";

        static void Main(string[] args)
        {

            Server myServer = new Server(serverName);
            myServer.ConnectionContext.LoginSecure = true;
            myServer.ConnectionContext.Connect();
            myServer.ConnectionContext.InfoMessage += ConnectionContext_InfoMessage;

            using (var sqlScript = new StreamReader(scriptFileName))
            {
                while (!sqlScript.EndOfStream)
                {
                    var batch = getNextBatch(sqlScript);
                    try
                    {
                        myServer.ConnectionContext.ExecuteNonQuery(batch);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("ERROR: {0}", ex.ToString());
                    }
                }
            }

        }

        private static void ConnectionContext_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
        {
            Console.WriteLine("INFO: {0}", e.Message);
        }

        private static string getNextBatch(StreamReader sqlScript)
        {

            var batch = new StringWriter();
            while (!sqlScript.EndOfStream)
            {
                var sqlScriptLine = sqlScript.ReadLine();
                if (sqlScriptLine.Trim().ToUpper() == "GO")
                {
                    break;
                }
                batch.WriteLine(sqlScriptLine);
            }

            return batch.ToString();

        }
    }
}

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Share via

How do I get the output from Database.ExecuteNonQuery?

Question

All replies (18)

Additional resources