During code generation using the .netTiers templates the stored procedures will be called to determine their outputs. If the stored procedures don't return the correct results netTiers will only provide IDataReader as a return type rather than a strongly typed object.
To resolve this use the SQL user_name() function to determine if netTiers is calling the stored procedure and return data that will generate the correct structure.
-- Check if netTiers is trying to discover how this stored proc works and return a dummy record with the expected result set. IF user_name() IS null BEGIN -- Return dummy data to netTiers with the expected data types SELECT TOP 1 * FROM [dbo].[SampleView] SELECT 1 as TotalRowCount RETURN END
It will pay to check your custom stored procedure works with FMTONLY. NetTiers will call the stored procedure with FMTONLY on and null for all of the parameters.
SET FMTONLY ON; EXEC dbo.custom_sp_MyTestSproc @param1 = NULL, @param2 = NULL, @param3 = NULL SET FMTONLY OFF;
I found this caused issues when using temporary tables. I.e.:
Msg 208, Level 16, State 0, Procedure custom_sp_StoredProcedureName, Line 160 Invalid object name '#TheTempTable'.If this is the case you can turn FMTONLY OFF in the in stored procedure before creating the temporary table and then back on again afterward. E.g.
-- ... DECLARE @fmtonlyon bit SELECT @fmtonlyon = 0 IF 1 = 0 BEGIN --This block will only be reached when FMTONLY is ON SELECT @fmtonlyon = 1 SET FMTONLY OFF END CREATE TABLE #TheTempTable ( -- ... ) --USE test here as FMTONLY will be disabled IF @fmtonlyon = 1 SET FMTONLY ON