Monday, February 13, 2012

Accessing return from sp_monitor

When I run the stored procedure sp_monitor through VB I onlyseem to be able to access the first 3 values returned, see code below. When I run the stored procedure through the enterprise manager I can see about 13 returned values. The reader loop only seems to have one iteration, with 3 arguments, how do I access the others?

cmd.CommandText = "sp_monitor"
cmd.CommandType = CommandType.StoredProcedure
reader = cmd.ExecuteReader()

While reader.Read()
results.AppendLine(String.Format("{0}, {1}, {2}", reader.GetName(0),reader.GetName(1), reader.GetName(2)))
results.AppendLine(String.Format("{0} {1} {2} {3}<br/>",reader(0), reader(1), reader(2), ctr))
ctr = ctr + 1
End While

If you want to retrieve multiple result sets using SqlDataReader, you can use SqlDataReader.NextResult to go to next result set. For example:

cmd.CommandText = "sp_monitor"
cmd.CommandType = CommandType.StoredProcedure
reader = cmd.ExecuteReader()
While reader.HasRows
While reader.Read()
results.AppendLine(String.Format("{0}, {1}, {2}", reader.GetName(0), reader.GetName(1), reader.GetName(2)))
results.AppendLine(String.Format("{0} {1} {2} {3}<br/>", reader(0), reader(1), reader(2), ctr))
ctr = ctr + 1
End While
reader.NextResult()
End While

Or you can useSqlDataAdapter to fill the result sets into a DataSet.

No comments:

Post a Comment