June 22, 2009 at 2:46 am
I have got a stored procedure with WHILE loop in it. Each pass through loop selects one row from some table and inserts the same row into temporary table. Once WHILE loop exits I SELECT all rows from the temporary table. When this procedure is invoked from the SQL server it returns expected set of rows. When the same procedure is invoked remotely, from web application server, it returns only the first row of data.
I do not now how to debug SQL remotely, but I managed to confirm that procedure actually inserted only one row of data into temporary table when invoked remotely from web application.
So, my conclusion is that for some, to me unknown reason, WHILE loop exits execution after first pass when invoked remotely.
This is happening in SQL 2005 and .NET 2.0 environment.
Any thoughts?
June 22, 2009 at 3:52 am
Please post the full code, but at a guess it sounds like that the connections are using different ANSI_NULLS settings which could account for the difference.
June 22, 2009 at 5:30 am
Here is the code used during testing
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[grhr_Article_GetObjectTypeObjects]
@ObjectTypeint,
@PublishedOnlybit = 'false',
@MaxTextFieldLengthint,
@SortExpressionnvarchar(256) = ' AddedDate DESC ',
@PageIndexint = 0,
@PageSizeint = 100
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex +1
SET @PageUpperBound = @PageLowerBound + @PageSize - 1
DECLARE @SQLStatement nvarchar(4000)
SET @SQLStatement = ''
DECLARE @WhereClose nvarchar(4000)
SET @WhereClose = ''
DECLARE @ObjectId int -- For cursor
IF (@ObjectType = 10001)
BEGIN
CREATE TABLE #PageIndexForImages
(
IndexId int IDENTITY (1, 1) NOT NULL,
ObjectIdintNOT NULL
)
CREATE TABLE #PageOfImages
(
ObjectIdintNOT NULL,
ObjectTypeintNOT NULL,
OriginalUrlnvarchar(256)NOT NULL,
BigUrlnvarchar(256)NOT NULL,
MediumUrlnvarchar(256)NOT NULL,
SmallUrlnvarchar(256)NOT NULL,
SequenceNointNOT NULL,
AddedDatedatetimeNOT NULL,
AddedBynvarchar(256)NOT NULL,
ChangeDatedatetimeNOT NULL,
ChangedBynvarchar(256)NOT NULL
)
SET @SQLStatement =' SELECT ObjectId FROM grhr_Images ORDER BY ' + @SortExpression
INSERT INTO #PageIndexForImages EXEC(@SQLStatement)
SELECT COUNT(*)AS CountBefore FROM #PageIndexForImages -- testing purpose
DELETE FROM #PageIndexForImages
WHERE IndexId @PageUpperBound
DECLARE @WhileIndex int
SET @WhileIndex = 1
WHILE @WhileIndex <= @PageSize
BEGIN
SELECT @ObjectId = ObjectId FROM #PageIndexForImages WHERE IndexId = @WhileIndex
INSERT INTO #PageOfImages EXEC grhr_Article_GetImageById @ObjectId
SET @WhileIndex = @WhileIndex + 1
END
SELECT COUNT(*)AS CountBefore FROM #PageIndexForImages -- testing purpose
SELECT * FROM #PageOfImages
DROP TABLE #PageOfImages
DROP TABLE #PageIndexForImages
END
END
June 22, 2009 at 5:49 am
And what parameters is it being called with,
Confirm with sql trace the exact parameters (including NULLS) that the webserver is passing.
June 22, 2009 at 6:19 am
I did confirm equality of all parameters. Anyway, if there was mismatch I would not expect to get the first row of proper result set.
June 22, 2009 at 7:16 am
Well, there has to be a difference some where.
Its either environmental (SET options) these can be seen in from sys.dm_exec_sessions or the parameters that are being passed. Dont overlook silly things either , the amount of posts there have been in the past where the poster swears blind there is a bug in SQL because of this that or the other , only to find that yes he was using different databases you would not believe 🙂
June 22, 2009 at 8:01 am
Dave,
Thank you so much for your support.
I have found the culprit!
It was line in C# code where input parameter was cast into SQL equivalent:
cmd.Parameters.Add("@PageSize", SqlDbType.Bit).Value = pageSize;
instead of:
cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
As you can see pageSize size was converted into "bit" type. No wander that WHILE loop was finishing after first pass. It was correct decision!!
I apologize for wasting your time with this silly typo!
All the best!
June 22, 2009 at 8:53 am
No Problems , but the the way to REALLY confirm what parameters are being passed is with SQLProfiler, it would of highlighted this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply