Urgent problem - Stored Procedures using Created. Tables

[Mike Hutton]

I have a rather odd problem.

I have a SP which uses temp. tables along the way, and then returns a
table of results:

CREATE PROCEDURE dbo.usp_myproc(
@pNameList VARCHAR(6000)
)
AS

....
CREATE TABLE #MyTable (
Id INT
, Nm VARCHAR(255)
)
....
SELECT * FROM #MyTable;

GO

I am calling this using a "text" style SqlCommand, as follows:

SqlCommand pCommand = new SqlCommand("usp_myproc '" + namelist.Text +
"'");
pCommand.CommandType = CommandType.Text;
SqlConnection oConn = new SqlConnection(myConnectionString);
oConn.Open();
pCommand.Connection = oConn;
SqlDataAdapter oDtAd = new SqlDataAdapter(pCommand);
oDtAd.SelectCommand = pCommand; //new SqlDataAdapter(pCommand);
MyDataSet.Tables.Clear();
oDtAd.Fill(MyDataSet,"Results");
oConn.Close();

The oddity is that I'm getting back an Invalid Object exception -
"Invalid object name '#MyTable'"

If I take out the SELECT * FROM #MyTable and replace it with a select
from another table, it works fine.

If I change the table to a permanent table, the problem returns.

So it seems that the issue is one of using tables which have been
created in the same procedure.

It seems that the run of the stored proc is ignoring any DDL commands.
I have a large number of SPs which use temporary tables et al, and
these need to be accessed via ASP.NET

HELP!

From a security POV I am using a named user with db_owner access onto
both the target database and tempdb.

Any suggestions, anyone?

Mike.

 

[Sebastian]

add parameters to command object. Don't pass them like that.

SqlCommand pCommand = new SqlCommand("usp_myproc", oConn);
pCommand.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@pNameList ", SqlDbType.Varchar, 6000).Value =
namelist.Text ;

Hope that help's.

 

[Mike Hutton]

add parameters to command object. Don't pass them like that.

I know it's not "best practice", but it was easier for me to do. I
still fail to see why this method shouldn't work. .NET only interfaces
to SQL - the operation of a stored proc should be independent of how I
pass parameters to it.

Has anyone else out there managed to successfully run stored procs
from .NET which create and drop tables?

As it is I've changed the proc so it parses the string and adds the
parameters dynamically (there are reasons for me doing it this way).
But I still get the same error. Any object created or altered in the
SP is not behaving - I still get the "Invalid object name
'#temptablename'." message.

I am running .NET 1.1, and using this to populate a custom datagrid by
adding a custom method to pass in the stored proc.

The proc call is MyCustomDataGrid.ExecuteStoredProc(storedprocstring,connectionstring);

In my custom control the code works along these lines:

public void ExecuteStoredProc(string pSPStr,string pCnStr)
{
string sSP;
// parse SP name
sSP = ...

SqlConnection oConn;
oConn = new SqlConnection(pCnStr);
oConn.Open();

SqlCommand oSqlCmd;
oSqlCmd = new SqlCommand(sSP,oConn);
oSqlCmd.CommmandType = CommandType.StoredProcedure;
AddParameters(ref oSqlCmd,pSPStr);

SqlDataAdapter oDA;
oDA = new SqlDataAdapter(oSqlCmd);

oDA.Fill(this.DataSet,"Results");
oConn.Close();
}

AddParameters(ref SqlCommand pSql,string pParams)
{
while(more params to process)
{
string ParamName=<parse from pParams>
string ParamValue=<parse from pParams>
SqlDbType ParamType=<parse from pParams/ParamValue>

pSql.Parameters.Add(ParamName,ParamType);
pSql.Parameters[ParamName].Direction = ParameterDirection.Input;
switch (ParamType) {
case SqlDbType.VarChar:
pSql.Parameters[ParamName].Value = sParamValue;
break;
case SqlDbType.Float:
pSql.Parameters[ParamName].Value =
Convert.ToDouble(sParamValue);
break;
...
etc
...
}
}
}

Irrespective of the clunkiness of this approach, the SP still works
fine provided I avoid creating/dropping tables, and fails when I
include them.

ARRGGHHH!!

Is this a .NET Bug, or am I doing something blatantly wrong?

Mike.