'Before' is presumably in v2.6? As in v3.0/3.1/3.5 it 'executes' (see below) the procs if you check the checkbox in the step David referred to, inside a transaction (which is rolled back) and passes 'null' as the parameter. It executes the procs as 'schemaonly', so it doesn't really run them. The datatable returned as the 'schema' is the resultset chosen. If there are multiple resultsets, it will simply obtain these using the same mechanism. It's below:
private bool ExecuteResultsetRetrievalQuery(DbConnection openConnection, DbCommand command, DBStoredProcedure storedProcedure, CommandBehavior behavior,
string elementTypeName)
{
bool succeeded = false;
using(DbTransaction transaction = openConnection.BeginTransaction())
{
try
{
command.Transaction = transaction;
using(DbDataReader reader = command.ExecuteReader(behavior))
{
int resultsetCounter = 1;
reader.Read();
DataTable metaData = reader.GetSchemaTable();
DBStoredProcedureResultset toAdd = CreateResultsetFromSchemaTable(storedProcedure, metaData, resultsetCounter);
if(toAdd != null)
{
storedProcedure.AddResultset(toAdd);
while(reader.NextResult())
{
reader.Read();
metaData = reader.GetSchemaTable();
resultsetCounter++;
toAdd = CreateResultsetFromSchemaTable(storedProcedure, metaData, resultsetCounter);
if(toAdd != null)
{
storedProcedure.AddResultset(toAdd);
}
}
}
reader.Close();
}
succeeded = true;
}
catch(Exception ex)
{
// check if the behavior was SchemaOnly. if so, we'll silently ignore this exception as it will cause the caller to call this method again
// with normal behavior. If the behavior wasn't schemaonly, this error isn't caused by schemaonly related issues so we've to report it and
// ignore the proc. It still is a non fatal error, so skip the proc altogether
if(behavior != CommandBehavior.SchemaOnly)
{
storedProcedure.ContainingSchema.LogError(ex,
string.Format("{0} '{0}' caused an exception during resultset retrieval. Its resultsets (if any) probably aren't determined in full: {2}",
elementTypeName, storedProcedure.Name, ex.Message), "DBSchemaRetriever::RetrieveResultsetsForStoredProcedure");
}
succeeded = false;
}
finally
{
transaction.Rollback();
}
}
return succeeded;
}
I think sqlserver will simply report 2 resultsets for the proc, as the proc isn't executed in full so the 'if' doesn't run. In previous versions we did: SET FMTONLY ON; <run proc; SET FMTONLY OFF; but that could lead to problems with procs which called extended procs.
If you uncheck the checkbox, it won't retrieve the resultset for that proc and it will assume 0 resultsets. You then can set the # of resultsets to 1. If you keep the checkbox checked during refresh, it will pull the resultsets and changing the value has no real effect, as it still has 2 resultsets read.
Is this enough for your situation or do you need the typed resultset (and thus have to have the proc's checkbox checked during refresh) ?
