Help! Error when running SP from App, but not from QA.

[sndkick]

This problem has been baffling me for days and i just cannot figure out why it is not working. I have the following stored procedure which runs perfect and returns the perfect information when running it from the SQL Query Analyzer (QA) or from the Enterprise Manager (EM), but when I try to run the EXACT same stored procedure call from my ASP application I get an error while attempting to execute. Any idea why?

Here's the exact code i'm running from QA:

sp_GetTestDataCrosstabP_New 606

From my ASP app I'm executing the same thing with this code on my line #29:

...
set rs = cm.execute "sp_GetTestDataCrosstabP_New 606"
...

And the SQL Error i get while attempting to execute this statement from my ASP page is the following:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'END'.
/data/testdataview.asp, line 29

Below is the entire stored procedure (it's very long, beware). I pretty much copied it from an example online and it just so happens to work beautifully except when called from my ASP application for some reason. Might it have something to do with the creation of a temporary table? In removing or commenting out either of the "END"s from the code i either get phony data, or another error. Help!!!


CREATE PROCEDURE dbo.sp_GetTestDataCrosstabP_NEW

@test_id int

AS

DECLARE @select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(200),
@table varchar(100)

SET @select = 'SELECT DISTINCT trr.test_run_id FROM tbl_test_run tr LEFT OUTER JOIN tbl_test_run_result trr ON trr.test_run_id = tr.test_run_id WHERE tr.test_id = ' + convert(varchar(100),@test_id) + ' GROUP BY trr.test_run_id ORDER BY trr.test_run_id'
SET @sumfunc = 'min(trr.result)'
SET @pivot = 'convert(varchar(100),trr.test_parameter_id)+''|''+convert(varchar(100),trr.test_parameter_condition_id)'
SET @table = 'tbl_test_run_result trr'


DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null ORDER BY ' + @pivot )

--SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' )')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO

 

[sndkick]

Note that there's one commented out line in there with a few @sumfunc variables on that line.

Also, this query is pretty much a crosstab/pivot query much like MSAccess had built in. Most of the query code shouldn't matter much in your reviewing it, but rather maybe an explanation as to why an "END" might trigger an error when called from my APP, but not when run from the QA or SQL EM.

thanks for any help,
scott

 

[SonOfEmidec1100]

I notice you have a SET NOCOUNT ON part way through, but no SET NOCOUNT OFF before it.

If I have a stored proc with more than one SELECT but only want access to the data from the final SELECT (Usually the case)I put SET NOCOUNT OFF at the start of the stored proc and SET NOCOUNT ON before the final SELECT statement.

Maybe this is your problem?