The Hotkeys going missing coincide with a message in notice mode (Cannot Connect To Server) which coincides to an error throw both on the GameServer Console Log and in the GameLog.txt located in the server_bin (server folder)
This error is thrown by:
dbo.smp_check_purchased_item
Code:
USE [teal_sky_telecaster_test]
GO
/****** Object: StoredProcedure [dbo].[smp_check_purchased_item] Script Date: 6/24/2013 9:38:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[smp_check_purchased_item]
@IN_ACCOUNT_ID INT,
@IN_SID INT,
@OUT_TOTAL_ITEM_COUNT INT OUTPUT, /* Total Item Count */
@OUT_NEW_ITEM_COUNT INT OUTPUT, /* New Item Count (AKA New presents received)*/
@OUT_PREMIUM_TICKET INT OUTPUT, /* Premium Ticket Flag - 0 = No Pass*/
@OUT_PREMIUM_REST_TIME INT OUTPUT, /* HV Pass Time Remaining */
@OUT_STAMINA_REGEN_TIME INT OUTPUT /* Stam Regen Time (Stam Bonus) */
AS
SET NOCOUNT ON
SET @OUT_NEW_ITEM_COUNT = 10
SET @OUT_STAMINA_REGEN_TIME = 20
SET @OUT_PREMIUM_TICKET = 0
SET @OUT_PREMIUM_REST_TIME = 0
SELECT @OUT_PREMIUM_TICKET = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and item_code = 910000 and valid_time > GETDATE()
IF @OUT_PREMIUM_TICKET > 0
BEGIN
/* Extend the Pass on first login after it is purchased to grant the user to full time on the pass based on the first login */
UPDATE PaidItem set valid_time = DATEADD(s, DATEDIFF(s, bought_time, GETDATE()), valid_time), taken_time = GETDATE() where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND taken_time IS NULL
/** Combine passes by invalidating older passes and adding the combined extra time onto the most recently purchased pass **/
DECLARE @VALID_PASS_COUNT INT
SET @VALID_PASS_COUNT = 0
DECLARE @COMBINED_TIME INT
SET @COMBINED_TIME = 0
DECLARE @NEWEST_PASS INT
SET @NEWEST_PASS = -1
-- Check for more than one pass in Item Box. If there are combine them into one pass.
SELECT @VALID_PASS_COUNT = COUNT(*) FROM PaidItem WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()
IF @VALID_PASS_COUNT > 1
BEGIN
-- Get the combined time of all active passes
SELECT @COMBINED_TIME = SUM(DATEDIFF(s, GETDATE(), valid_time)) from PaidItem where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()
-- Select the most recent pass
SELECT TOP (1) @NEWEST_PASS = sid from PaidItem where taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE() ORDER BY bought_time DESC
-- Invalidate all but the most recent pass
SET ROWCOUNT 1
WHILE 1=1
BEGIN
UPDATE PaidItem set valid_time = GETDATE(), rest_item_count=0 WHERE sid IN ( SELECT TOP 1 sid FROM PaidItem WITH (NOLOCK) WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE() AND sid <> @NEWEST_PASS )
IF @@ROWCOUNT = 0 BREAK
END
SET ROWCOUNT 0
-- Extend the most recent pass by the Combined time - the current time
UPDATE PaidItem set valid_time = DATEADD(s, @COMBINED_TIME, GETDATE()), rest_item_count=0 WHERE taken_account_id = @IN_ACCOUNT_ID AND item_code = 910000 AND valid_time > GETDATE()AND sid = @NEWEST_PASS
END
-- end combine passes
SELECT @OUT_PREMIUM_REST_TIME = MAX(DATEDIFF(s, GETDATE(), valid_time)) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and item_code = 910000 and valid_time > GETDATE()
SELECT @OUT_TOTAL_ITEM_COUNT = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0;
SELECT @OUT_NEW_ITEM_COUNT = COUNT(*) from PaidItem WITH (NOLOCK) where taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0 and confirmed = 0;
END
RETURN @@ERROR
dbo.smp_read_purchased_item_list
Code:
USE [teal_sky_telecaster_test]
GO
/****** Object: StoredProcedure [dbo].[smp_read_purchased_item_list] Script Date: 6/24/2013 9:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[smp_read_purchased_item_list]
@IN_ACCOUNT_ID INT
AS
SET NOCOUNT ON
-- ¾ئہجإغ ب®ہخ ؟©؛خ¸¦ ¼¼ئأ
UPDATE dbo.PaidItem set confirmed = 1, confirmed_time = GETDATE() WHERE taken_account_id = @IN_ACCOUNT_ID and confirmed = 0;
SELECT sid, -- ID
account_id, -- ±¸¸إار °èء¤ ID
avatar_id, -- ±¸¸إار ؤ³¸¯ ID ( -1 ہد°و؟ىب¨اا؟،¼*±¸¸إار°حہس)
avatar_name, -- ±¸¸إار ؤ³¸¯ ہج¸§ ( -1 ہد°و؟ىب¨اا؟،¼*±¸¸إار°حہس)
server_name, -- ±¸¸إار ¼*¹ِ ہج¸§
item_code, -- ¾ئہجإغؤعµه
item_count, -- ±¸¸إار ¾ئہجإغ°¹¼ِ
rest_item_count, -- ³²ہ؛ ¾ئہجإغ °¹¼ِ
bought_time, -- ±¸¸إ½أ°£
valid_time -- ہ¯ب؟½أ°£( ء¦ار¾ّہ»°و؟ى-12-31 )
FROM dbo.PaidItem WITH (NOLOCK) WHERE taken_account_id = @IN_ACCOUNT_ID and rest_item_count > 0
-- The below shows items after they have been taken
--or sid in ( select sid from dbo.PaidItem WITH(NOLOCK) WHERE taken_account_id =@IN_ACCOUNT_ID and rest_item_count = 0)
RETURN @@ERROR
dbo.smp_takeout_commercial_item
Code:
USE [teal_sky_telecaster_test]
GO
/****** Object: StoredProcedure [dbo].[smp_takeout_commercial_item] Script Date: 6/24/2013 9:39:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[smp_takeout_commercial_item]
@OUT_ITEM_CODE INT OUTPUT,
@IN_SID INT,
@IN_COUNT INT,
@IN_ACCOUNT_ID INT,
@IN_AVATAR_ID INT,
@IN_AVATAR_NAME VARCHAR(61),
@IN_SERVER_NAME VARCHAR(30),
@IN_TRANSACTION_CODE INT,
@IN_IDX INT
AS
SET NOCOUNT ON
SET @OUT_ITEM_CODE = 0
DECLARE @RET INT
SET @RET = -1
DECLARE @REST_ITEM_COUNT INT
SET @REST_ITEM_COUNT = -1
-- °¹¼ِ ہج»َ
IF @IN_COUNT < 1
BEGIN
GOTO ON_END
END
BEGIN TRANSACTION
SELECT @REST_ITEM_COUNT = rest_item_count FROM dbo.PaidItem WHERE sid = @IN_SID;
IF @REST_ITEM_COUNT < @IN_COUNT
BEGIN
ROLLBACK TRANSACTION
GOTO ON_END
END
-- Remove item(s) from Item Box
UPDATE dbo.PaidItem SET
rest_item_count = ( @REST_ITEM_COUNT - @IN_COUNT ),
taken_avatar_id = @IN_AVATAR_ID,
taken_avatar_name = @IN_AVATAR_NAME,
taken_server_name = @IN_SERVER_NAME,
taken_time = GETDATE()
WHERE sid = @IN_SID and taken_account_id = @IN_ACCOUNT_ID;
SET @RET = 0
DECLARE @ACCOUNT_ID INT
SET @ACCOUNT_ID = -1
SELECT @OUT_ITEM_CODE = item_code, @ACCOUNT_ID = taken_account_id from dbo.PaidItem where sid = @IN_SID;
IF @ACCOUNT_ID <> @IN_ACCOUNT_ID
BEGIN
SET @OUT_ITEM_CODE = 0
ROLLBACK TRANSACTION
GOTO ON_END
END
-- Add the item(s) to the character
INSERT INTO dbo.Item (
sid, -- 0
owner_id, -- 1
account_id, -- 2
summon_id, -- 3
auction_id, -- 4
keeping_id, -- 5
idx, -- 6
code, -- 7
flag, -- 8
cnt, -- 9
[level], -- 10
enhance,
ethereal_durability, -- 11
endurance, -- 12
gcode, -- 13
create_time, -- 14
wear_info, -- 15
socket_0, -- 16
socket_1, -- 17
socket_2, -- 18
socket_3, -- 19
remain_time, -- 20
elemental_effect_type, -- 21
elemental_effect_expire_time, -- 22
elemental_effect_attack_point, -- 23
elemental_effect_magic_point, -- 24
update_time -- 25
)
VALUES (
@IN_TRANSACTION_CODE, -- 0
@IN_AVATAR_ID, -- 1
@IN_ACCOUNT_ID, -- 2
0, -- 3
0, -- 4
0, -- 5
@IN_IDX, -- 6
@OUT_ITEM_CODE, -- 7
0, -- 8
@IN_COUNT, -- 9
0, -- 10
0,
0, -- 11
0, -- 12
0, -- 13
GETDATE(), -- 14
-1, -- 15
0, -- 16
0, -- 17
0, -- 18
0, -- 19
0, -- 20
0, -- 21
0, -- 22
0, -- 23
0, -- 24
GETDATE() -- 25
);
SET @RET = @@ERROR
IF @RET <> 0
BEGIN
ROLLBACK TRANSACTION
GOTO ON_END
END
COMMIT TRANSACTION
ON_END:
RETURN @RET
The above setup was originally intended for 7.3/7.4 but is applicable in 8.1 as-well. (Be warned that is completely removes the whole "billing" schema as I found it to be useless. Hope this helps!
