30 03 2009

Finally sorted something out which had been pissing me off. This applies to ASP.NET 2.0 and SQL Server 2005.

Say you have an SqlDataSource whose SELECT query calls a Stored Procedure, and when you’re at the final stages of the wizard whereby it prepares the schema, you may get an error which reads:

Unable to refresh schema.
Invalid object name #temp

The #temp part may vary, but it will resemble something in your stored procedure. After hunting around on Google, I found a few forums, most saying it isn’t possible, and one giving a solution which worked.

The first result I found was here:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22011091.html

The “Accepted Solution” for this post was this:
“there is no immediate solution. VS isn’t smart enough to deal with temporary tables 🙂
unless you are willing to change your database schema or create another temporary/auxiliary database for this, I don’t think you will be able to use that feature.”

Wrong. I found this which worked perfectly, after much tinkering around:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/70af8b07-8e1a-44c7-9f27-6ec6dc0900f6/

This post explains why the error occurs. It appears that when the schema is retrieved, it changes the mode of execution to FMTONLY ON which runs the stored procedure in a way which predicts all possible resultant datasets, ignoring conditional statements.

If your stored procedure includes the creation of a temporary table, then this causes SQL to keel over, giving an error message which is displayed in the Visual Studio IDE.

You can test this operation within SQL Server Manager by running
SET FMTONLY ON;
EXEC YourStoredProc ‘param1’ ‘param2’

If you’re getting the errors in visual studio you should also get the error in the output. You can use this mode to test your trial and error methods when fixing it. When it finally works, you’ll recieve and empty result set with no errors.

To solve this, you need to recreate your entire stored procedure in a local variable, create a table variable with the exact schema of the output you request, then execute a INSERT statement into the table variable, executing your local variable at the same time, writing the output into the table variable.

When you wrap your procedure code into the local variable, ensure you drop any temporary tables within that local variable, as it will be executed outside the domain of your stored procedure, and a DROP TABLE statement will fail outside the variable.

Here’s an example:

CREATE PROCEDURE MyProc
@param int
AS
DECLARE @tempSQL varchar(4000)
SET @tempSQL = ‘
DECLARE ##tempTable TABLE (idKey int, DisplayName varchar(48))

INSERT INTO ##tempTable (idKey, DisplayName)
VALUES (SELECT idNames, firstName + CHAR(32) + lastName FROM ContactList
WHERE Age > 18
AND Requirement = ”’ + @param + ”’)
SELECT * FROM ##tempTable
DROP ##tempTable’

DELCARE @outputTable TABLE (idKey int, DisplayName varchar(48))
INSERT INTO @outputTable EXEC sp_executesql @tempSQL
SELECT * FROM @outputTable
GO

Bare in mind that I have not actually tested the above code, it’s just recited from memory, and simplified/obfuscated from the production code I wrote. The complex bit to watch out for the is the @param within the variable string. You have the escape a quote within a literal with another quote, and as I’m ending the string before I concatenated the “+ @param” bit, therefore I needed 3 single quote characters.

CSS Opacity in IE