Capture stored procedure output

 

I've been tasked with executing a stored procedure and capturing the output.   Sounds, simple enough, sure, I can do that…

 

Naturally, I've lost my way and now can't seem to find the magic incantation to accomplish my task.   Here's what I've got.

 

I'm operating against a SQL Server 2008 R2 instance.  

I have the SQLPS module installed.

I have attempted multiple methods of executing a .sql file to execute the stored procedure.  The contents of the .sql file are

 

"Exec ds_utilities.dbo.ds_test @db = 'whatever'"

 

The ds_test stored procedure is as follows:

 

 

USE [ds_utilities]

GO

/****** Object:  StoredProcedure [dbo].[ds_test]    Script Date: 8/24/2015 1:22:05 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

 

/******* This proc checks for objects with uneeded .dbo. in definition, runs ds_check_id/ds_create_metadata,

 runs smart_indexer,checks for any PK/FK renames needed and populates the rad_sys_db_verification_history table*********/

 

-- exec [ds_PQM5_check_mbw] [empty_Retail2013_1__preview_04]

 

 

ALTER   procedure [dbo].[ds_test] @db sysname

AS

set nocount on

declare @cnt int

declare @sql nvarchar (max)

 

select @db as parameter

 

print 'developers'

Select @sql = 'select top 2 * from [ds_utilities].[dbo].[Developer] '

 

execute(@sql)

 

print 'greate developers'

select top 2 * from [ds_utilities].[dbo].[Developer] where name like '%spoo%'

 

 

#############################################

The output is as follows.

 

Results:

Parameter

1 Whatever

 

  developer_id  name                data_architect_flag   email_address

1      1                 cderrenger

  y

cderringer@flescor.com

2      2                 ccharles

N

ccharles@flescor.com

 

developer_id  name                data_architect_flag   email_address

1      7                Mmathers

  y

mmathers@flescor.com

2      142            Dmathers

N

dmathers@flescor.com

 

 

Messages

Developers

Greate developers

 

#####################################################

 

I'm past the point of receiving errors, I just can't seem to extract the above output from my results.

 

My question is, what object/property/methods can I use to capture all of the above output?   I've tried Invoke-SQLCmd but only seem to be able to retrieve the "Parameter" value of "Whatever".   I've also tried a System.Data.SqlClient.SqlCommand object with ExecuteReader() but couldn't figure out how to access the additional output in that either.  Same with System.data.sqlclient.sqldataadapter.  If figure what I'm looking for has gotta be in one of these somewhere unless I'm totally off base.   Since I don't have an unlimited amount of time I'm coming here to see if someone can point me to a path home.

 

TIA

Parents
No Data
Reply
  • On further research I discovered the NextResult() method of the SQLDataReader class that is allows the object returned from the call to SQLCommand.ExecuteReader() to move through the discrete result sets.  

    Following the call to ExecuteReader() I can access the first result set, the "whatever" value in my results above.   A subsequent call to NextResult() illuminates the next result set which can be iterated through, displaying "cderringer" & "ccharles" data rows in my results above.   And a final call to NextResult() illuminates the last result set which on iterating displays the "Mmathers" & "Dmathers" row values in my results above.

    Now I just need to figure out where the stored procedure output that shows up on the "Messages" tab (when executing the stored procedure in Management Studio) is going.

Children
No Data