CommandBehavior.SchemaOnly fails for some stored procedures

Question

Making an ADO.NET call to run a stored procedure using IDbCommand.ExecuteReader(SchemaOnly) and it throws an exception if the stored procedure uses a temp table. The exception is "Invalid object name '#tempPCBBHolidays'." (that's the name of the table).

So...

Is using CommandBehavior.SchemaOnly a bad idea for stored procedures? If so, when all I need is the metadata should I use CommandBehavior.SingleRow instead?

Or should I put the call in a try/catch and only on an exception switch to SingleRow? (This is for a SQL tool we ship so I don't know what stored procedures will be called.)

Or is there another approach that is better?

thanks - dave

Answer 1

According to the MSDN forum, this is a known problem with Sql Server. The solution is to call again requesting SingleRow.