|
|
Posted by Hugo Kornelis on 04/18/07 15:08
On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:
>I have pasted the code below (sorry if i offend anybody about
>formatting.
>I am not sure how to format code here... suggestions?). When I use
>Query Analyzer and step through the code in "debug" mode, the compiler
>jumps to the following line:
>
>----------------------------
>SET ROWCOUNT 0
>----------------------------
>
>and throws this error:
>
>---------------------------------------------------------------------------------------------
>
>CurrentPage TotalPages TotalRows
>----------- ----------- -----------
>0 0 0
>
>Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
>60
>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
>SET ROWCOUNT. Must be a non-null non-negative integer.
>
>@RETURN_VALUE = -6
>---------------------------------------------------------------------------------------------
>
>Could somebody tell me what about the logic in my code is causing this
>failure (I know that the value is null and that that is causing the
>error). I have a feeling it might have to do with the following lines
>of code that I have commented out in the WHERE clause (but, I am not
>sure what it is doing or how to add the logic to my own code):
Hi pbd22,
Since those commented lines are *AFTER* the place where the error occurs
(the debugger jumps to the incorrect line; the only line that can cause
this error is either this one:
> SET ROWCOUNT @lbound
or this one:
> SET ROWCOUNT @perPage
I assume that it's the first, and that it's cause by some unexpected
things going on in the calculations neede to compute @lbound.
You already have added a great statement to aid in debugging; I'm gonna
ask you to add a bit more to it - so please change this:
> SELECT
>
> CurrentPage = @pagenum,
> TotalPages = @pages,
> TotalRows = @rows
to this:
SELECT
CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound
And then rerun the query and post back the results. We'll then have to
take it from there.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
[Back to original message]
|