October 29, 2002 at 7:17 am
I have the following SP which when run from Query Analyser (with the correct input parameters) returns the result I am expecting. However when I run it from an ASP - I get :-
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
This is the SP :-
CREATE PROCEDURE web_test
@where char(150),
@key char(20),
@type char(1),
@list char(15)
AS
/* ****************************************************************************** */
/* */
/* This procedure is used by the SEARCH facility - do NOT delete */
/* */
/* ****************************************************************************** */
DECLARE @sql varchar(1000)
SELECT @sql = 'SELECT part INTO ##t1 FROM epar WHERE part IN '
SELECT @sql = @sql + '(SELECT part FROM epar WHERE '
SELECT @sql = @sql + RTRIM(@where)
IF NOT RTRIM(@key) = ''
BEGIN
SELECT @sql = @sql + ' UNION SELECT part FROM epar_keys WHERE keywords LIKE '
SELECT @sql = @sql + RTRIM(@key)
END
IF @type = '5'
SELECT @sql = @sql + ' AND (status=1) AND (searchable=''y'' or searchable=''T'') AND part_type=5 ORDER BY part'
ELSE
SELECT @sql = @sql + ' AND (status=1) AND (searchable=''y'' or searchable=''T'') ORDER BY part'
exec (@sql)
IF @list = 'Default'
BEGIN
SELECT @sql = 'SELECT part, descr, summary, full_descr FROM epar WHERE part IN (SELECT part FROM ##t1 AND price>0)'
exec (@sql)
DROP TABLE ##t1
END
ELSE
BEGIN
SELECT @sql = 'SELECT part INTO ##t2 FROM ##t1 WHERE part IN (SELECT part FROM eluciddb_nds..plis_brks WHERE price_list='''+RTRIM(@list)+''' AND price>0)'
exec (@sql)
SELECT @sql = 'SELECT part, descr, summary, full_descr FROM epar WHERE part IN (SELECT part FROM ##t2)'
exec (@sql)
DROP TABLE ##t1
DROP TABLE ##t2
END
GO
I have another SP which does a similar thing and it works perfectly. This is that SP :-
CREATE PROCEDURE web_search_epar
@where char(150),
@key char(20),
@type char(1)
AS
/* ****************************************************************************** */
/* */
/* This procedure is used by the SEARCH facility - do NOT delete */
/* */
/* ****************************************************************************** */
DECLARE @sql varchar(1000)
SELECT @sql = 'SELECT part, descr, summary, full_descr FROM epar WHERE part IN '
SELECT @sql = @sql + '(SELECT part FROM epar WHERE '
SELECT @sql = @sql + RTRIM(@where)
IF NOT RTRIM(@key) = ''
BEGIN
SELECT @sql = @sql + ' UNION SELECT part FROM epar_keys WHERE keywords LIKE '
SELECT @sql = @sql + RTRIM(@key)
END
IF @type = '5'
SELECT @sql = @sql + ' AND (status=1) AND (searchable=''y'' or searchable=''T'') AND part_type=5 ORDER BY part'
ELSE
SELECT @sql = @sql + ' AND (status=1) AND (searchable=''y'' or searchable=''T'') ORDER BY part'
exec (@sql)
GO
The difference between the 2 is that the first has some extra filtering which uses temporary tables.
I imagine that the temporary tables could be eliminated if one is good at SQL - unfortunately my SQL is not good enough.
Any help would be much appreciated.
October 29, 2002 at 10:09 am
Problem solved - found a reference to SET NOCOUNT ON in another post although it did not match my query.
Itried it and the SP now workd from ASP as well.
October 29, 2002 at 10:17 am
Thanks for the update.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply