Click here to Skip to main content
15,936,802 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm writing a method to retrieve the dataset structure as returned by a specified stored procedure.

The code in C# (under development so a little dirty) is as follows:

public static TableInfoList DeriveTableInfo(string linkedServer, string query)
            string temporaryTable = string.Format("generatorTemp{0}", new Random(DateTime.Now.Millisecond).Next(0, 1000).ToString());

            string sql = string.Format("SELECT * INTO {0} FROM OPENQUERY ({1}, \'{2}\')", temporaryTable, linkedServer, query);

            new DataAccess().ExecuteCommandText(sql);

            TableInfoList derived = new TableInfoList("dbo", temporaryTable);

            sql = string.Format("DROP TABLE {0}", temporaryTable);

            new DataAccess().ExecuteCommandText(sql);

            return derived;

With the basic approach being summarisable as:

1) Create a temp table
2) Use OPENQUERY to run the sproc into the table
3) Derive all the necessary info from the temporary table (done by the TableInfoList constructor with a call to sys.sp_columns) and drop it

This works perfectly until I attempt to invoke a sproc which employs a temporary table. The table created by the sproc is not recognised as it is out of scope and I get an error message to the effect of "#WhateverTheTempTableIsCalled is not a valid object."

Given that the method is intended for use in various analysis and code generating tasks based on a vast number of pre-existing sprocs, altering the target sproc is not an option.

Does anyone know of a workround with OPENQUERY or perhaps an alternative to using it?

1 solution

I just use ExecuteReader to execute the statement and then use GetSchemaTable to access the details of the result.
Share this answer
PeejayAdams 21-Jul-14 5:34am    
Thanks, Piebald but that runs into the same issue - I also get the same "invalid object" error if I run the commands via SMS. The problem really stems from going via a linked server. For my current purposes, I can work around it by making a direct connection to the server/database that contains the sproc in question and creating the temporary table there but that's a slightly untidy solution as I'll have to maintain multiple connection strings etc.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900