[SqlServer] welke columns bij resultset van stored proc?

[EfBe]

Ik heb het vermoeden dat Sqlserver de gegevens niet opslaat, maar het schijnt dat de ODBC-JDBC bridge van MS wel degelijk de info kan tonen, dus moet het ergens opgeslagen zijn, vandaar dat ik deze poging waag, misschien weet iemand het:

Ik ben op zoek naar een manier om te bepalen welke columns (en dus welke types ze hebben etc) worden geretourneerd door een stored procedure. Dit moet dus gebeuren zonder de stored procedure aan te roepen, dus dmv het raadplegen van system tables. Er zijn wel INFORMATION_SCHEMA. views, maar die geven o.a. alleen de definities van functies met een table return type (INFORMATION_SCHEMA.ROUTINE_COLUMNS).

Iemand een idee OF het uberhaupt wordt opgeslagen in Sqlserver? sp_sproc_columns gaf even hoop, maar die returnt alleen de parameter definities, niet de columns die worden geretourneerd. Ik kan de T-SQL code parsen, maar dat is a) een lastige hobbel omdat ik dan een SQL parser moet maken en b) niet altijd mogelijk want bij encrypted stored procedures is de source niet beschikbaar.

Via de OleDB driver van SqlServer kun je wel wat opvragen, maar die weigert (SQLOLEDB) weer de procedure_columns te rapporteren, kennelijk niet geimplementeerd.

Bedankt alvast

[Verwijderd]

Heb je "SET FMTONLY ON" al geprobeerd? Als je daarna de SP uitvoert (EXEC... )wordt er alleen metadata teruggestuurd. Niet voor alle scenarios geschikt maar misschien heb je er iets aan...

[Verwijderd]

Ik ben hier ook eens mee aan het stoeien geweest, ik moest toen een asp pagina maken die automatisch alle stored procedures en hun output/input parameters en types liet zien

Ik heb dit toen opgelost door automatisch alle stored procedures aan te roepen met parameters waarvan ik zeker wist dat ze geen resultset op zouden leveren(-9999 bijvoorbeeld), je kunt dan nog wel de column names en types uitlezen maar hebt dus geen results.

[ Voor 4% gewijzigd door Verwijderd op 22-03-2003 12:44 ]

[EfBe]

Ik heb het via OleDB gedaan mbv een OleDbDataAdapter en de FillSchema method. Die creeert een DataTable in een dataset met alle columns van alle stored procs. De procs worden niet uitgevoerd. De column types zijn wel al gemapped op System.Type types maar goed, dat boeit verder niet zo.

Ik heb OleDb gebruikt omdat de SqlServer driver SET FMTONLY ON meegeeft als enige bescherming en verder de proc wel aanroept. Heb je dan een select en een delete in 1 proc, dan rammelt die delete er wel degelijk doorheen, althans, dat risico loop je. Nu niet Ik geef voor alle parameters NULL mee.

c/p van de code:

Java:

spCurrent = (IDBStoredProcedure)StoredProcedures.GetByIndex(i);

// create command object
OleDbCommand cmdSelect = new OleDbCommand();
cmdSelect.CommandText = "[" + spCurrent.ContainingSchema.SchemaOwner + "].[" + spCurrent.StoredProcedureName + "]";
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Connection = ocnOpenConnection;

// Create the parameters. These will be filled with NULL. 
CreateEmptyParameters(ref spCurrent, ref cmdSelect);

OleDbDataAdapter daSchemaAdapter = new OleDbDataAdapter(cmdSelect);
DataSet dsSchema = new DataSet("ResultsetSchema");

DataTable[] arrdtReturned = daSchemaAdapter.FillSchema(dsSchema,SchemaType.Source);

// If no schema was returned, the stored procedure didn't contain a select procedure. 
if(arrdtReturned.Length<=0)
{
    // no schema
    continue;
}

// there is a schema returned. Analyze it and construct the resultcolumns from that schema.
// OleDb will only return 1 schema, no matter how many select statements are executed in 
// the stored procedure. just process the first one.
DataTable dtSchema = arrdtReturned[0];
SortedList slResultsetColumns = new SortedList(dtSchema.Columns.Count);

for(int j=0;j<dtSchema.Columns.Count;j++)
{
    DataColumn dcCurrent = (DataColumn)dtSchema.Columns[j];

    IDBResultsetColumn rcNewColumn = new DBResultsetColumn();
    rcNewColumn.ColumnName = dcCurrent.ColumnName;
    rcNewColumn.ColumnNetType = dcCurrent.DataType;
    rcNewColumn.MaxLength = dcCurrent.MaxLength;
    rcNewColumn.OrdinalPosition = dcCurrent.Ordinal;
    rcNewColumn.ReturnedByStoredProcedure = spCurrent;

    slResultsetColumns.Add(dcCurrent.Ordinal, rcNewColumn);
}

spCurrent.ResultsetColumns = slResultsetColumns;

Er zit wel ergens een bug in die FillSchema, want de documentatie zegt dat je multiple schema's terug moet krijgen, echter als je 2 select statements in je stored proc zet krijg je altijd het schema van de 1e, nooit van de 2e. Naja, da's dan jammer