Dear Jochem, how are you? :)
> Are you running query analyser through ODBC?
I really don't know.. :(
> BTW, you should change the name of the variable you are passing. SQL is
> a reserved word.
I tried to change all variables, didn't work. I'm attaching the complete
stored procedure-
it is a GREAT sp by Tony Rogerson, you should check into it, it's for PAGING
result set.
maybe we can solve it like this...
CREATE PROCEDURE page_query
@sql varchar(4000),
@start_row int,
@rows_required int
AS
BEGIN
/***
Tony Rogerson [EMAIL PROTECTED]
Allows the paging of a SQL Server result set.
@sql is the sql statement to execute.
@start_row is the starting row number
@rows_required is the number of rows to return from @start_row
Note: This uses openquery, therefore, your local server should have
the 'data access' option set to true (sp_serveroption)
Example: page_query 'SELECT * FROM dbname.dbo.tablename ORDER BY
name', 10, 20
****/
DECLARE @execsql nvarchar(4000)
SET @execsql = N' SET NOCOUNT ON'
+ ' SELECT RowID = IDENTITY( int, 1, 1 ), *'
+ ' INTO #T'
+ ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE(
@sql, '''', '''''''' ) + ''' )'
+ ' SET NOCOUNT OFF'
+ ' SELECT *'
+ ' FROM #T'
+ ' WHERE RowID BETWEEN ' + CAST( @start_row AS
varchar(10) ) + ' AND ' + CAST( @start_row + (@rows_required-1) AS
varchar(10) )
EXEC sp_executesql @execsql
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
