[Discussion] Premium Service

[lollipop07]

Well after some stressfull times, I have found the root of a problem I have been having for the past week..


After installing a procedure to deduct the days of users premium by 1 every 24 hrs, When it came to people having 0 days premium, this is where things started to go wrong..

I noticed that users who were most online would eventually have 0 days, when this happenes it would seem that the server files do not like this, So when a user would try to login to the game, Aujard would give the below errors and Freeze the whole server, stopping all users from logging in, Freeze the KoPanel and eventually cause Ebenzer timeout errors and stop SQL access on the database tables.

2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 3, current count = 4., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 3, current count = 4., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 4, current count = 5., 187 ***
2008-6-24 0:36, *** 25000, 266, [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 4, current count = 5., 187 ***

So I removed that procedure, and Truncated the 'Premium_service' table, so the next time people would login they would have 3 days and hey presto, no more errors..

For the past week this has been driving me crazy, I was thinking someone may have been trying to hack through 'Aujard', I re-formatted and re-installed my whole box because of this error.

I would like to know if anyone else has found this out and are having the same problems..

[MentaL]

[lollipop07]

My conclusion for this is a Updated Procedure to check and remove the User that has 0 days Premium Service left from the Premium Service Table..

I have onyl basic Knowlege of Stored procs, So I am unabl;e to do the required edits my self..

I have included all the required Procedures for the Premium Service feature, So if anyone Knows how to Include this, Could they do it and share it with all please..


LOAD_PREMIUM_SERVICE_USER

[CODE]
USE [KA_Online]
GO
/****** Object: StoredProcedure [dbo].[LOAD_PREMIUM_SERVICE_USER] Script Date: 06/26/2008 13:11:53 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LOAD_PREMIUM_SERVICE_USER]
@AccountID varchar(27),
@nRet1 smallint OUTPUT,
@nRet2 smallint OUTPUT
AS
DECLARE @nRow smallint
SET @nRow = 0
DECLARE @Type smallint
SET @Type = null
DECLARE @Days smallint
SET @Days = null
SELECT @nRow = COUNT(*) FROM PREMIUM_SERVICE WHERE strAccountID = @AccountID
IF @nRow = 0
BEGIN
SET @nRet1 = 0 --Fail (Account don