How to Insert SQL message tab output into a table?

Question

How do I insert the message tab result of the below sql query into a TableTest2?

CREATE TABLE TableTest2 (InsertOutput VARCHAR(100))
CREATE TABLE TestTable (ID INT, msg VARCHAR(50))    
INSERT TestTable (ID, msg)
VALUES (1, 'Message 1'), (2,'Message 2')    
DROP table TestTable

Here's the result of running the above query:

(2 row(s) affected)

How do I insert the above result into TableTest2?

Just the row count specifically or all messages in general? If all messages you are out of luck through TSQL except if you do some crazy thing with `DBCC OUTPUTBUFFER`. You can access it in C# etc through `Connection.InfoMessage ` — Martin Smith, Aug 05 '13 at 18:31
Immediately after the insert `@@ROWCOUNT` contains `2` which you can insert into an appropriate integer field — Alex K., Aug 05 '13 at 18:32
I want to insert the output on the message tab. Which on this case is:- (2 row(s) affected) — John W. Mnisi, Aug 05 '13 at 18:33
@MartinSmith My C# knowledge is very limited. But I can run the C# code on SSIS via the script task, can you please provide me with an example code? — John W. Mnisi, Aug 05 '13 at 18:41
There is [example code here](http://msdn.microsoft.com/en-us/library/a0hee08w(v=vs.80).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1) but I'm not sure whether it will be of any use in SSIS or not. According to [the answer here](http://stackoverflow.com/questions/6251434/retrieve-error-message-in-ssis) SSIS already has built in functionality for that. — Martin Smith, Aug 05 '13 at 18:46

score 3 · Answer 1 · answered Aug 05 '13 at 18:52

The only way to capture the actual output is by using C# (or any other non-sql language) to execute the command. That can be done in a CLR extension. The tSQLt database unit testing framework contains the procedure tSQLt.CaptureOutput to capture output for testing purposes. You can see how it uses an OnInfoMessage event handler in http://sourceforge.net/p/tsqlt/code/219/tree/tSQLtCLR/tSQLtCLR/TestDatabaseFacade.cs within the executeCommand method (starting at line 64):

    public SqlDataReader executeCommand(SqlString Command)
    {
        infoMessage = SqlString.Null;
        connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
        SqlCommand cmd = new SqlCommand();

        cmd.Connection = connection;
        cmd.CommandText = Command.ToString();

        SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

        return dataReader;
    }

    protected void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
    {
        if (infoMessage.IsNull)
        {
            infoMessage = "";
        }
        infoMessage += args.Message + "\r\n";
    }

If you just want to use this for testing purposes have a look at tSQLt.org The opensource tSQLt unit testing framework helps with a lot of things that are frequently encountered during automated testing.

score 2 · Answer 2 · answered Aug 05 '13 at 18:34

2

In SQL SERVER @@ROWCOUNT return number of rows affected by an operation. You can do something like

DECLARE @iRec int

INSERT TestTable (ID, msg)
VALUES (1, 'Message 1'), (2,'Message 2')    

SET @iRec = @@ROWCOUNT;

INSERT TableTest2(InsertOutput) 
VALUES (CAST(@iRec as VARCHAR(10)) + ' row(s) affected')

answered Aug 05 '13 at 18:34

Yuriy Galanter

38,833
15
69
136

1

@@Rowcount return the number of inserted records. But running the above query will return: **(2 row(s) affected)** on the message tab. I want to insert what is returned on the message tab. Your answer doesn't solve my problem. – John W. Mnisi Aug 05 '13 at 18:45
The message inserted into your TableTest2 will be `2 rows(s) affected.` Feel free to add open/closed parentesses as needed. @@ROWCOUNT will do the same for SELECT, DELETE, UPDATE etc. – Yuriy Galanter Aug 05 '13 at 18:46
I can't add / close parenthesis because if the query returns an error message on the message tab then I'm screwed. I still want to insert that error message on the message tab. @YuriyGalanter – John W. Mnisi Aug 05 '13 at 18:56
That's different then. In that case use the other solution to capture actual output. But if you need to capture SQL errors in your own code - you don't need to go to such extremes - it will be propagated to you anyway so you can catch it and log it. – Yuriy Galanter Aug 05 '13 at 19:02

How to Insert SQL message tab output into a table?

2 Answers2