Stored Procedure works fine from SQL Mgt Studio but throws Invalid Object name #AllActiveOrders from MVC app

Question

I can run the 'guts' of my stored procedure as a giant query.. just fine from SQL Management Studio. Furthermore, I can even right click and 'execute' the stored procedure - .. y'know.. run it as a stored procedure - from SQL Management Studio.

When my ASP.NET MVC app goes to run this stored procedure, I get issues..

System.Data.SqlClient.SqlException: Invalid object name '#AllActiveOrders'.

Does the impersonation account that ASP.NET runs under need special permissions? That can't be it.. even when I run it locally from my Visual Studio (under my login account) I also get the temp table error message.

---

EDIT: Furthermore, it seems to work fine when called from one ASP.NET app (which is using a WCF service / ADO.NET to call the stored procedure) but does not work from a different ASP.NET app (which calls the stored proc directly using ADO.NET)

---

FURTHERMORE: The MVC app that doesn't crash, does pass in some parameters to the stored procedure, while the crashing app runs the Stored Proc with default parameters (doesn't pass any in). FWIW - when I run the stored procedure in SQL Mgt. Studio, it's with default parameters (and it doesn't crash).

---

If it's of any worth, I did have to fix a 'String or Binary data would be truncated' issue just prior to this situation. I went into this massive query and fixed the temptable definition (a different one) that I knew to be the problem (since I had just edited it a day or so ago). I was able to see the 'String/Binary truncation' issue in SQL Mgt. Studio / as well as resolve the issue in SQL Mgt Studio.. but, I'm really stumped as to why I cannot see the 'Invalid Object name' issue in SQL Mgt. Studio

Answer 1

Ok. Figured it out with the help of my colleague who did some better Google-fu than I had done prior..

First, we CAN indeed make SQL Management Studio puke on my stored procedure by adding the FMTONLY option:

SET FMTONLY ON;

EXEC    [dbo].[My_MassiveStackOfSubQueriesToProduceADigestDataSet]

GO

Now, on to my two competing ASP.NET applications... why one of them worked and one of them didn't? Under the covers, both essentially used an ADO.NET System.Data.SqlClient.SqlDataAdapter to go get the data and each performed a .Fill(DataSet1)

However, the one that was crashing was trying to get the schema in advanced of the data, instead of just deriving the schema after the fact.. so, it was this line of code that was killing it:

da.FillSchema(DataSet1, SchemaType.Mapped)

If you're struggling with this same issue that I've had, you may have come across forums like this from MSDN which are all over the internets - which explain the details of what's going on quite adequately. It had just never occurred to me that when I called "FillSchema" that I was essentially tripping over this same issue.

Now I know!!!

Answer 2

Stored procedures and temp tables generally don't mix well with strongly typed implementations of database objects (ado, datasets, I'm sure there's others).

If you change your #temp table to a @variable table that should fix your issue.

(Apparently) this works in some cases:

IF 1=0 BEGIN
    SET FMTONLY OFF
END

Although according to http://msdn.microsoft.com/en-us/library/ms173839.aspx, the functionality is considered deprecated.

An example on how to change from temp table to var table would be like:

create table #tempTable (id int, someVal varchar(50))

to:

declare @tempTable table (id int, someval varchar(50))

There are a few differences between temp and var tables you should consider:

What's the difference between a temp table and table variable in SQL Server?

When should I use a table variable vs temporary table in sql server?

Answer 3

Following on from bkwdesign's answer about finding the problem was due to ADO.NET DataAdapter.FillSchema using SET FMTONLY ON, I had a similar problem. This is how I dealt with it:

I found the simplest solution was to short-circuit the stored proc, returning a dummy recordset FillSchema could use. So at the top of the stored proc I added something like:

IF 1 = 0 
BEGIN;
    SELECT CAST(0 as INT) AS ID, 
        CAST(NULL AS VARCHAR(10)) AS SomTextCol, 
        ...; 

    RETURN 0;
END;

The columns of the select statement are identical in name, data type and order to the schema of the recordset that will be returned from the stored proc when it executes normally.

The RETURN ensures that FillSchema doesn't look at the rest of the stored proc, and so avoids problems with temp tables.