3

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

bkwdesign
  • 1,953
  • 2
  • 28
  • 50
  • Are you certain that the MVC app is running the same stored procedure? Not a different instance, database? Is the user resolving to the same schema? – Shannon Severance Nov 17 '14 at 21:09
  • Thanks for asking @ShannonSeverance, I just verified: My MVC/WCF app can run both DEV and PROD versions of the stored proc w/o errors. My other MVC app crashes on DEV and PROD databases. There is a difference between how the apps call the stored proc which I'll add to my question – bkwdesign Nov 17 '14 at 21:51

3 Answers3

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?

Community
  • 1
  • 1
Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Yeah, this is a massive query with numerous 'resource' tables that I load up before building out the main output table. I used to have all the resource tables coded up as @TableVariables but recently switched to #TempTables because I heard the performance is better (as my resource tables are rather large data sets themselves). Hmmm.. as I thihk it over, I switched over to #TempTables a few weeks ago and have been able to develop my web app against the data just fine until today. Hmmm. – bkwdesign Nov 17 '14 at 20:40
  • I've not used them myself, but another potential option could be user defined table types: http://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx - though this is for SQL 2008 and greater – Kritner Nov 17 '14 at 20:49
  • thanks for your answer - it is useful and is indeed an accurate way to solve the problem. It's not the way I went for my scenario for the reasons stated in my prior comment. I've posted the answer that I eventually employed to solve my dilemma. – bkwdesign Nov 18 '14 at 18:00
2

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!!!

bkwdesign
  • 1,953
  • 2
  • 28
  • 50
0

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.

Simon Elms
  • 17,832
  • 21
  • 87
  • 103