rowcount = -12856 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

rowcount = -12856

Author

Topic

lau_gu
Starting Member

7 Posts

Posted - 2002-07-26 : 06:49:11

I'm getting an infrequent error where I update the database and the row is actually updated sucessfully, however it returns a rowcount = -12856
And since I check that rowcount was > 0 it triggers my error routine.

Has anyone come accross this? SQL BOL totally omits the error range 12000 to 13000!

In case it helps here is my sql: (unfortunately all the formatting gets screwed up)

CREATE PROCEDURE [UpdateEziPage]
@ProviderID int,
@EzipageID int,
@AuditTrailMemberID1 int,
@Description nvarchar(2000),
@HtmlText ntext,
@Name nvarchar(50),
@MemberID int,
@AllowUserRequestFlag char(1),
@LanguageID smallint,
@RowsUpdated smallint OUTPUT
AS
Set NOCOUNT ON

DECLARE @UserAccess smallint,
@Error int
DECLARE @EziObjectTypeID smallint
SELECT @EziObjectTypeID = 2

EXEC GetEziobjectAccessLevel @ProviderID = @ProviderID, @EziobjectID= @EzipageID, @EziObjectTypeID=@EziObjectTypeID, @MemberID = @MemberID, @AccessLevel = @UserAccess OUTPUT

IF @UserAccess > 3
BEGIN
BEGIN TRANSACTION
UPDATE EziPageText SET
HtmlText = @HtmlText
WHERE ProviderID = @ProviderID
AND EzipageID = @EzipageID
AND LanguageID = @LanguageID
SELECT @RowsUpdated = @@ROWCOUNT,
@Error = @@ERROR
IF @RowsUpdated < 1 OR @Error > 0
ROLLBACK TRANSACTION
ELSE
begin
UPDATE EziobjectText SET
Description = @Description,
Name = @Name
WHERE ProviderID = @ProviderID
AND EziobjectID = @EzipageID
AND EziobjectTypeID = 2
AND LanguageID = @LanguageID

IF @@ROWCOUNT < 1 OR @@ERROR > 0
ROLLBACK TRANSACTION
ELSE
begin
UPDATE EziPage SET
AuditTrailMemberID3 = AuditTrailMemberID2,
AuditTrailMemberID2 = AuditTrailMemberID1,
AuditTrailMemberID1 = @AuditTrailMemberID1,
AuditTrailTime3 = AuditTrailTime2,
AuditTrailTime2 = AuditTrailTime1,
AuditTrailTime1 = getdate(),
AllowUserRequestFlag = @AllowUserRequestFlag
WHERE ProviderID = @ProviderID
AND EzipageID = @EzipageID

IF @@ROWCOUNT < 1 OR @@ERROR > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
end
end
END

Peace
Lau

Edited by - lau_gu on 07/26/2002 06:54:25

Edited by - lau_gu on 07/26/2002 06:56:00

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-26 : 08:49:13

as an aside....Why are you mixing the following 'similiar' tests....they seem to serve the same purpose but why the different code???....

SELECT @RowsUpdated = @@ROWCOUNT, @Error = @@ERROR
IF @RowsUpdated < 1 OR @Error > 0

and
IF @@ROWCOUNT < 1 OR @@ERROR > 0

also you say the SP is failing on an @rowcount problem, but you have 3 potential @rowcounts...which one is giving the problem?

seperately....is it the SP that is failing, or some VB code calling the SP that is failing....in which case the error may be to do with with either the way you are calling the SP....or some ADO/MDAC DLL....through which the SP is executed...that is the problem.

I know I had a problem recently with MDAC 2.0 giving different error codes for duplicate records (on inserts) from MDAC 2.1....a common MS feature I believe!!

just wonder if a variation of same is happening here?

maybe you could put a 'log a message' section to trap the problem when it happens.....if the problem doesn't happen 100% of the time....
and then log as much info as you can to help spot a pattern...in the time of day, user, pc, data?

HTH.

lau_gu
Starting Member

7 Posts

Posted - 2002-07-28 : 23:28:49

Your're right, I should be using the SELECT one in all cases.
SELECT @RowsUpdated = @@ROWCOUNT, @Error = @@ERROR
IF @RowsUpdated < 1 OR @Error > 0

>also you say the SP is failing on an @rowcount problem, but you have >3 potential @rowcounts...which one is giving the problem?

The first one becuase that was the only one that was being stored in a variable and returned to the program...But now that I think about it you must be right about it being a ADO/MDAC DLL problem becuase if @Rowcount was < 0 in the SQL then it would have done a ROLLBACK!!

Nothing strange about my calling code, though, I've used it in a zillion other places. Maybe the fact one of the parameters is ntext is causing it occasional grief.

Here's my calling code in case you can spot anything weird.

Err.Clear
With Cmd
For l_OldParm = 0 to (.Parameters.Count - 1)
.Parameters.Delete Cmd(0).name
next
.Parameters.Append .CreateParameter("ProviderID",K_int,1,4, ProviderID)
.Parameters.Append .CreateParameter("EzipageID",K_int,1,4, EzipageID)
.Parameters.Append .CreateParameter("AuditTrailMemberID1",K_int,1,4, User_Session.MemberID)
.Parameters.Append .CreateParameter("Description",K_nvarchar,1,4000, Description)
Call AppendNText(HTMLText, "HTMLText")
.Parameters.Append .CreateParameter("Name",K_nvarchar,1,100, Name)
.Parameters.Append .CreateParameter("MemberID",K_int,1,4, User_Session.MemberID)
.Parameters.Append .CreateParameter("AllowUserRequestFlag",K_char,1,1, AllowUserRequestFlag)
.Parameters.Append .CreateParameter("LanguageID",K_smallint,1,2, Language)
.Parameters.Append .CreateParameter("RowsUpdated", 2, 2)
.CommandText = "UpdateEziPage "
on Error Resume Next
.Execute
On Error GoTo 0

if Connect.Errors.Count = 0 _
AND Cmd(.Parameters.Count - 1) > 0 then
Popup_Message = UpdateSuccessMsg
Instruction_Msg = Server.HTMLEncode(Popup_Message)
Action = K_Edit_Complete
else
call HandleError(ObjectName & " has not been updated", _
Err, K_Program_Filename, "UpdateRow", 0, Connect.Errors, _
.CommandText & PrintCmdParameters())
end if

I think I'll have to change:

if Connect.Errors.Count = 0 _
AND Cmd(.Parameters.Count - 1) > 0 then

to

if Connect.Errors.Count = 0 _
AND Cmd(.Parameters.Count - 1) <> 0 then

under the assumption that any time @@ROWCOUNT is less than zero it is a glitch in ADO/MDAC rather than a real problem.

Peace
Lau

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-29 : 09:18:57

couple of things.....

1. CreateParameter("RowsUpdated", 2, 2)....where the data type?..compared to the other parameters....
2. As this is an OUTPUT variable....do you not have to specify same in the calling code?
3. text....the text field is not the last field in the list of parameters....i think it might have to be?
4. you define the ntext/nvchar fields as 2000 + 50 in the SP....yet you define them as 4000 + 100 in the calling code....is it possible this is where your problems is?

I know N'field's take double the storage/memory space....but surely the VB type declarations should sort that out and not need you to do anything special for it.....

by the sounds of your problem, an occasional error means 'data is the problem'.....some data item you are passing, causes a mismatch....
rather than trap and ignore it....i'd trap and log/report it....

lau_gu
Starting Member

7 Posts

Posted - 2002-07-30 : 21:49:48

>>1. CreateParameter("RowsUpdated", 2, 2)....where the data type?..compared to the other parameters.... <<

The 2nd parameter is the datatype, I just haven't used the constant. (spank me... I'm a bad boy)

>>2. As this is an OUTPUT variable....do you not have to specify same in the calling code? <<

The 3rd parameter is the direction 1 is input 2 is output

>>3. text....the text field is not the last field in the list of parameters....i think it might have to be? <<

It seems to work with it elsewhere - this isn't the first time I've passed ntext as parameters. But maybe it is doing something weird. The ntext definitely has to be the last field in a SELECT.

>>4. you define the ntext/nvchar fields as 2000 + 50 in the SP....yet you define them as 4000 + 100 in the calling code....is it possible this is where your problems is?
I know N'field's take double the storage/memory space....but surely the VB type declarations should sort that out and not need you to do anything special for it..... <<

No, vb doesn't handle it. in the parameter call the size has to be specified in bytes.

by the sounds of your problem, an occasional error means 'data is the problem'.....some data item you are passing, causes a mismatch....
rather than trap and ignore it....i'd trap and log/report it....

Yeah, that's the way to go ... thanks for your help.

Peace
Lau

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources