Capture Multiple Result Sets within T-SQL Statement

As I mentioned in my last blog, I am going to give you some examples of using CLRRemoteExecute procedure. Before the example, let me wrap it up and make it easy of use. In this example I set show detail message on. We can see how the script to be run.

create procedure dbo.Ex
(
 @Server varchar(128), 
 @Database varchar(128),
 @SQL varchar(max),
 @ConnectionTimeout int = 30,
 @CommandTimeout int = 3600
  
) 
as
begin
 declare @connectionString nvarchar(4000)
 exec @connectionString = dbo.ConnectionStringBuilder @Server = @Server, @Database = @Database, @ConnectionTimeout = @ConnectionTimeout
 exec dbo.CLRRemoteExecute @ID = null,
        @ConnectionType = N'NewConnection',
        @ConnectionString = @connectionString,
        @CommandTimeout = @CommandTimeout,
        @SQL = @SQL,
        @CommandError = 'Raise',
        @Transaction_Type = 'NoTransaction',
        @Transaction_Success = 'NoAction',
        @Transaction_Failure = 'NoAction',

        @InfoMessage_IncludeDetail = 'True',
        @InfoMessage_ReturnType = 'Console',
        @InfoMessage_Connection_ConnectionType = 'NoConnection',
        @InfoMessage_Connection_ConnectionString = null,
        @InfoMessage_Connection_CommandTimeout = 86400,
        @InfoMessage_OnMessage = null,
        @InfoMessage_OnEventError = 'StopProcess',

        @ErrorMessage_ReturnType = 'None',
        @ErrorMessage_Connection_ConnectionType = 'NoConnection',
        @ErrorMessage_Connection_ConnectionString = null,
        @ErrorMessage_Connection_CommandTimeout = 86400,
        @ErrorMessage_OnMessage = null,
        @ErrorMessage_OnEventError = 'StopProcess',

        @ResultSets_ReturnType = 'Console',
        @ResultSets_Connection_ConnectionType = 'NoConnection',
        @ResultSets_Connection_ConnectionString = null,
        @ResultSets_Connection_CommandTimeout = 86400,
        @ResultSets_OnMessage = null,
        @ResultSets_OnEventError = 'StopProcess'
end
go

Execute a query on local server

exec Ex '.', 'master',' select top 3 name from sys.objects'
go

Message (2009-07-02 23:24:36) Connecting . ...
Message (2009-07-02 23:24:36) Server . connected...
Message (2009-07-02 23:24:36) 1 commands found.
Message (2009-07-02 23:24:36) Starting command 0...
Message (2009-07-02 23:24:36) Retrieving result sets ...
name
-------------------------
sysrscols
sysrowsets
sysallocunits

(3 row(s) affected)

Message (2009-07-02 23:24:36) Result sets retrieved ...
Message (2009-07-02 23:24:36) Command 0 finished ...
Message (2009-07-02 23:24:36) Connection closed ....
Message (2009-07-02 23:24:36) Finished....

Run multiple queries and return multiple results

exec Ex '.', 'master',' select top 3 name from sys.objects 
      select top 10 name from sys.tables
     '
go

Message (2009-07-02 23:26:40) Connecting . ...
Message (2009-07-02 23:26:40) Server . connected...
Message (2009-07-02 23:26:40) 1 commands found.
Message (2009-07-02 23:26:40) Starting command 0...
Message (2009-07-02 23:26:40) Retrieving result sets ...
name
----------------------------------------------
sysrscols
sysrowsets
sysallocunits

(3 row(s) affected)

name
----------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 row(s) affected)

Message (2009-07-02 23:26:40) Result sets retrieved ...
Message (2009-07-02 23:26:40) Command 0 finished ...
Message (2009-07-02 23:26:40) Connection closed ....
Message (2009-07-02 23:26:40) Finished....

Run SQL statements separated by "go"

exec Ex 'jvs01', 'master',' select top 3 name from sys.objects go print ''second command'' go select top 10 name from sys.tables'
go

Message (2009-07-02 23:28:50) Connecting jvs01 ...
Message (2009-07-02 23:28:50) Server jvs01 connected...
Message (2009-07-02 23:28:50) 3 commands found.
Message (2009-07-02 23:28:50) Starting command 0...
Message (2009-07-02 23:28:50) Retrieving result sets ...
name
--------------------------------------------------------------------------------------------------------------------------------
sysrowsetcolumns
sysrowsets
sysallocunits

(3 row(s) affected)

Message (2009-07-02 23:28:50) Result sets retrieved ...
Message (2009-07-02 23:28:50) Command 0 finished ...
Message (2009-07-02 23:28:50) Starting command 1...
Message (2009-07-02 23:28:50) second command
Message (2009-07-02 23:28:50) Retrieving result sets ...
Message (2009-07-02 23:28:50) Result sets retrieved ...
Message (2009-07-02 23:28:50) Command 1 finished ...
Message (2009-07-02 23:28:50) Starting command 2...
Message (2009-07-02 23:28:50) Retrieving result sets ...
name
--------------------------------------------------------------------------------------------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 row(s) affected)

Message (2009-07-02 23:28:50) Result sets retrieved ...
Message (2009-07-02 23:28:50) Command 2 finished ...
Message (2009-07-02 23:28:50) Connection closed ....
Message (2009-07-02 23:28:50) Finished....

Run a SQL that will fail….

exec Ex 'jvs01', 'master','raiserror(''error'',16,1)'

Message (2009-07-02 23:29:57) Connecting jvs01 ...
Message (2009-07-02 23:29:57) Server jvs01 connected...
Message (2009-07-02 23:29:57) 1 commands found.
Message (2009-07-02 23:29:57) Starting command 0...
Message (2009-07-02 23:29:57) Connection closed ....
Message (2009-07-02 23:29:57) Finished with error ....
Msg 6522, Level 16, State 1, Procedure CLRRemoteExecute, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLRRemoteExecute": 
System.Data.SqlClient.SqlException: error
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at SqlRemoteExecute.ExecutionEngine.Run()
   at SqlRemoteExecute.SQLRExecute.CLRRemoteExecute(SqlString ID, SqlString Destination_Connection_ConnectionType, SqlString Destination_Connection_ConnectionString, SqlInt32 Destination_Connection_CommandTimeout, SqlString Destination_SQL, SqlString Destination_CommandError, SqlString Destination_Transaction_Type, SqlString Destination_Transaction_Success, SqlString Destination_Transaction_Failure, SqlString InfoMessage_IncludeDetail, SqlString InfoMessage_ReturnType, SqlString InfoMessage_Connection_ConnectionType, SqlString InfoMessage_Connection_...

Emmm….result is nasty...let's change the procedure to not show error messages. This may useful, for instance, if you want to confirm a record is in a table, you could just execute insert statement to it without to be bothered with error messages as you don't care and error means correct…

alter procedure dbo.Ex
(
 @Server varchar(128), 
 @Database varchar(128),
 @SQL varchar(max),
 @ConnectionTimeout int = 30,
 @CommandTimeout int = 3600
  
) 
as
begin
 declare @connectionString nvarchar(4000)
 exec @connectionString = dbo.ConnectionStringBuilder @Server = @Server, @Database = @Database, @ConnectionTimeout = @ConnectionTimeout
 exec dbo.CLRRemoteExecute @ID = null,
        @ConnectionType = N'NewConnection',
        @ConnectionString = @connectionString,
        @CommandTimeout = @CommandTimeout,
        @SQL = @SQL,
        @CommandError = 'None',
        @Transaction_Type = 'NoTransaction',
        @Transaction_Success = 'NoAction',
        @Transaction_Failure = 'NoAction',

        @InfoMessage_IncludeDetail = 'True',
        @InfoMessage_ReturnType = 'Console',
        @InfoMessage_Connection_ConnectionType = 'NoConnection',
        @InfoMessage_Connection_ConnectionString = null,
        @InfoMessage_Connection_CommandTimeout = 86400,
        @InfoMessage_OnMessage = null,
        @InfoMessage_OnEventError = 'StopProcess',

        @ErrorMessage_ReturnType = 'None',
        @ErrorMessage_Connection_ConnectionType = 'NoConnection',
        @ErrorMessage_Connection_ConnectionString = null,
        @ErrorMessage_Connection_CommandTimeout = 86400,
        @ErrorMessage_OnMessage = null,
        @ErrorMessage_OnEventError = 'StopProcess',

        @ResultSets_ReturnType = 'Console',
        @ResultSets_Connection_ConnectionType = 'NoConnection',
        @ResultSets_Connection_ConnectionString = null,
        @ResultSets_Connection_CommandTimeout = 86400,
        @ResultSets_OnMessage = null,
        @ResultSets_OnEventError = 'StopProcess'
end
go

let's have a test.

create table test (id int primary key)
insert into test values(1)
exec Ex '.', 'test','insert into test values(1)'--error
go

Message (2009-07-02 23:33:30) Connecting . ...
Message (2009-07-02 23:33:30) Server . connected...
Message (2009-07-02 23:33:30) 1 commands found.
Message (2009-07-02 23:33:30) Starting command 0...
Message (2009-07-02 23:33:30) Connection closed ....
Message (2009-07-02 23:33:30) Finished with error ....

It does not seem to be hard to use if we wrap it up, however, wrapped procedure has less functionalities than the original one. Well, there's always a tradeoff, flexibility or usability. Let me give you another example, as I mentioned in the title -- Capture Multiple Result Sets within T-SQL Statement.

--create event handler
create procedure ProcessMyResult @ID varchar(100), @Ind int, @TableName varchar(128)
as
begin
 declare @SQL varchar(max) 
 select @SQL = 'UPDATE '+QUOTENAME(@TableName)+' set Name = '''+ISNULL(@ID, 'null')+'_'+CAST(@Ind as varchar(10))+'_'+QUOTENAME(@TableName) + '_''+ Name' 
 exec(@SQL)
end
go
declare @Server varchar(128), @Database varchar(128), @SQL varchar(max), @ConnectionTimeout int = 30, @CommandTimeout int = 3600
declare @connectionString nvarchar(4000)
select @SQL = 'select top 5 cast(left(name, 30) as varchar(100)) Name from sys.tables 
    select top 4 cast(left(name, 30) as varchar(100)) Name from sys.tables', @Server = '.', @Database = 'msdb'
exec @connectionString = dbo.ConnectionStringBuilder @Server = @Server, @Database = @Database, @ConnectionTimeout = @ConnectionTimeout
declare @connectionString1 nvarchar(4000)
exec @connectionString1 = dbo.ConnectionStringBuilder @Server = @Server, @Database = 'test', @ConnectionTimeout = @ConnectionTimeout
exec dbo.CLRRemoteExecute @ID = 'test',
       @ConnectionType = N'NewConnection',
       @ConnectionString = @connectionString,
       @CommandTimeout = @CommandTimeout,
       @SQL = @SQL,
       @CommandError = 'None',
       @Transaction_Type = 'NoTransaction',
       @Transaction_Success = 'NoAction',
       @Transaction_Failure = 'NoAction',

       @InfoMessage_IncludeDetail = 'True',
       @InfoMessage_ReturnType = 'Console',
       @InfoMessage_Connection_ConnectionType = 'NoConnection',
       @InfoMessage_Connection_ConnectionString = null,
       @InfoMessage_Connection_CommandTimeout = 86400,
       @InfoMessage_OnMessage = null,
       @InfoMessage_OnEventError = 'StopProcess',

       @ErrorMessage_ReturnType = 'None',
       @ErrorMessage_Connection_ConnectionType = 'NoConnection',
       @ErrorMessage_Connection_ConnectionString = null,
       @ErrorMessage_Connection_CommandTimeout = 86400,
       @ErrorMessage_OnMessage = null,
       @ErrorMessage_OnEventError = 'StopProcess',

       @ResultSets_ReturnType = 'Event',
       @ResultSets_Connection_ConnectionType = 'CurrentContext',
       @ResultSets_Connection_ConnectionString = @connectionString1 ,
       @ResultSets_Connection_CommandTimeout = 86400,
       @ResultSets_OnMessage = ' exec dbo.ProcessMyResult @_ID, @_Index, @_TableName',
       @ResultSets_OnEventError = 'StopProcess'
--check result
declare @table varchar(100)
select top 1 @table = QUOTENAME(name) from sys.tables where name like '%test%'
while @table is not null
begin
 exec('select * from '+@table + '; drop table '+@table)
 select @table = (select top 1 QUOTENAME(name) from sys.tables where name like '%test%')
end

Message (2009-07-02 23:35:12) Connecting . ...
Message (2009-07-02 23:35:12) Server . connected...
Message (2009-07-02 23:35:12) 1 commands found.
Message (2009-07-02 23:35:12) Starting command 0...
Message (2009-07-02 23:35:12) Retrieving result sets ...
Message (2009-07-02 23:35:12) Result sets retrieved ...
Message (2009-07-02 23:35:12) Command 0 finished ...
Message (2009-07-02 23:35:12) Connection closed ....
Message (2009-07-02 23:35:12) Finished....
id
-----------
1

(1 row(s) affected)

Name                                                                                                 Primary Key Field - __test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__
---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
test_0_[__test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__]_sysproxies                                  1
test_0_[__test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__]_sysdbmaintplan_history                      2
test_0_[__test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__]_syscollector_execution_stats_i              3
test_0_[__test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__]_syssubsystems                               4
test_0_[__test - ecad54f2-a23b-4c0d-9f2e-455d4365b0a2__]_backupmediaset                              5

(5 row(s) affected)

Name                                                                                                 Primary Key Field - __test - b933087d-1056-4b66-981a-e1b61d226908__
---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
test_1_[__test - b933087d-1056-4b66-981a-e1b61d226908__]_sysproxies                                  1
test_1_[__test - b933087d-1056-4b66-981a-e1b61d226908__]_sysdbmaintplan_history                      2
test_1_[__test - b933087d-1056-4b66-981a-e1b61d226908__]_syscollector_execution_stats_i              3
test_1_[__test - b933087d-1056-4b66-981a-e1b61d226908__]_syssubsystems                               4

(4 row(s) affected)

In the event SQL, you could use @_ID, @_TableName and @_Index in your OnMessage event. If you use this feature, I would suggest writing a procedure with those 3 as parameters and only putting "Exec YourProc @ _ID, @TableName, @_Index" in your event code, then you will be able to deal with result sets within your procedure. This also provides ability for T-SQL ---- Capture Multiple Result Sets within T-SQL Statement. This procedure will be extremely useful when you manage hundreds or thousands SQL Server instance. In later post, I will explain how to call multiple SQL statements in one connection firing multi-threads to run them.