Calling Stored Procs from ASP code

Hi,

I have a Stored Procedure on a Windows 2000 server / MS SQL 2000 that selects from a temporary table. When I call this from MS SQL Query Manager it works fine, and returns the correct results. However, when I call the same Stored Proc with exactly the same parameters from an ASP page (running on IIS from Windows 2000) it fails.

The Stored Proc being called basically calls another stored proc as outlined below, and it is the lower level one that seems to be causing the problems. I've been doing a lot of debugging to see what is causing the problem, and it looks like the ODBC that the ASP is using doesn't like more than one query in the lower level Stored Proc. Does this make sense to anyone, does anyone know a way around it? I've tried putting it all in one string and doing an exec on that, but makes no difference.

Thanks in advance.


If it is relevent, here is the code for the Stored Procs - starting with the one being called from the ASP.

CREATE PROCEDURE [sp_AllContactsWithOrder]
@sOrderBy as nvarchar(255), @iStartPos as nvarchar(255), @iEndPos as nvarchar(255)
AS

declare @sSQL as nvarchar(1000)

Set @sSQL = 'Exec sp_AllContactsByPage ''' + @sOrderBy + ''', '''', ' + @iStartPos + ', ' + @iEndPos
exec(@sSQL)
GO


then 'sp_AllContactsByPage' (which is a generic Stored Proc) is defined as:

CREATE PROCEDURE [sp_AllContactsByPage]
@sOrderBy as nvarchar(255), @sWhere as nvarchar(255), @iStartPos as nvarchar(255), @iEndPos as nvarchar(255)
AS

-- Purpose
-- Need a unique ordered id on each row - so create a temporary table with an identity col for unique id
-- Insert required data into it
-- Add total row count into dummy column
-- Select from temp table between start and end rows on the unique ordered id

-- Create temp table
CREATE TABLE #temp (row [int] IDENTITY (1, 1) NOT NULL,
row_count [int] default 0,
county nvarchar(255),
district nvarchar(255),
council nvarchar(255),
jobtitle nvarchar(255),
firstname nvarchar(255),
surname nvarchar(255)
)

-- Insert into the temporary table
declare @sSQL as nvarchar(1000)

Set @sSQL = 'INSERT INTO #temp (county, district, council, jobtitle, firstname, surname) '
Set @sSQL = @sSQL + 'SELECT county, district, council, jobtitle, firstname, surname FROM Vw_AllContacts '
if @sWhere <> ''
Set @sSQL = @sSQL + 'WHERE ' + @sWhere + ' '

Set @sSQL = @sSQL + 'ORDER BY ' + @sOrderBy
--print(@sSQL)
exec(@sSQL)


-- Work out the number of rows, and update temp table
declare @iRowCount as int
Set @iRowCount = (select count(surname) from #temp)
--update #temp Set row_count=@iRowCount


-- Select from temp table bewteen start and end rows
Set @sSQL = 'select * from #temp WHERE row >= ' + @iStartPos + ' and row < ' + @iEndPos + ' ORDER BY row'
exec(@sSQL)
GO