Can't create new character. Something went wrong in stored procedure.

[jm_lapido]

[Fri Apr 24 2009 02:05:37.558674 3086026432]: open log file '/var/log/cabal/DBAgent_09.log'
[Fri Apr 24 2009 02:05:37.582184 3086026432]: LOG LEVEL: 3
[Fri Apr 24 2009 02:05:37.582328 3086026432]: MemPoolUnitSizeClass(2): 24576Bytes
[Fri Apr 24 2009 02:05:37.583786 3086026432]: Start DBAgent_09 Server
[Fri Apr 24 2009 02:05:37.584833 3086026432]: UseEcryptText (Off)
[Fri Apr 24 2009 02:05:37.585515 3086026432]: try listening 38181 port
[Fri Apr 24 2009 02:05:37.586401 3086023584]: CProcessLayer(0)::svc Start [3086023584]
[Fri Apr 24 2009 02:05:37.586618 3075533728]: CProcessLayer(0)::svc Start [3075533728]
[Fri Apr 24 2009 02:05:37.586755 3065043872]: CProcessLayer(0)::svc Start [3065043872]
[Fri Apr 24 2009 02:05:37.586882 3054554016]: CProcessLayer(0)::svc Start [3054554016]
[Fri Apr 24 2009 02:05:37.587235 3044064160]: CProcessLayer(0)::svc Start [3044064160]
[Fri Apr 24 2009 02:05:38.846762 3086026432]: accept success 8(127.0.0.1:32771)
[Fri Apr 24 2009 02:05:38.851864 3086026432]: 1.open user 8 [B4C004E8] (127.0.0.1)
[Fri Apr 24 2009 02:05:40.739084 3086026432]: accept success 10(127.0.0.1:32773)
[Fri Apr 24 2009 02:05:40.740096 3086026432]: 2.open user 10 [B4C08C18] (127.0.0.1)
[Fri Apr 24 2009 02:05:42.243744 3086026432]: accept success 11(127.0.0.1:32777)
[Fri Apr 24 2009 02:05:42.244278 3086026432]: 3.open user 11 [B4C0EE00] (127.0.0.1)
[Fri Apr 24 2009 02:05:43.440834 3086026432]: accept success 12(127.0.0.1:32781)
[Fri Apr 24 2009 02:05:43.441068 3086026432]: 4.open user 12 [B4C15010] (127.0.0.1)
[Fri Apr 24 2009 02:07:23.540630 3086023584]: Database Connect success!
[Fri Apr 24 2009 02:07:28.204345 3075533728]: Database Connect success!
[Fri Apr 24 2009 02:07:29.900396 3065043872]: Database Connect success!
[Fri Apr 24 2009 02:07:29.990144 3044064160]: Database Connect success!
[Fri Apr 24 2009 02:07:30.874011 3054554016]: Database Connect success!
[Fri Apr 24 2009 02:07:30.892918 3075533728]: sock 12=> sendBuf is growed (size:32768). len(657), 17000, 131072
[Fri Apr 24 2009 02:07:30.907208 3044064160]: sock 11=> sendBuf is growed (size:32768). len(657), 17000, 131072
[Fri Apr 24 2009 02:12:53.884576 3065043872]: [##ERROR##] OnIPCNewMyChartr fail (CharacterIdx: 20)
[Fri Apr 24 2009 02:12:53.884897 3065043872]: [##ERROR##] DB ERROR(MSG) : [FreeTDS][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
[Fri Apr 24 2009 02:12:53.884938 3065043872]: [##ERROR##] DB ERROR(CODE) : 217
[Fri Apr 24 2009 02:12:53.884965 3065043872]: [##ERROR##] DB ERROR(VAR_INFO) :
[Fri Apr 24 2009 02:12:53.884990 3065043872]: [##ERROR##] DB ERROR(SQLSTATE) : 42000
[Fri Apr 24 2009 02:12:53.885015 3065043872]: [##ERROR##] DB ERROR(STM_TEXT) : {call cabal_sp_newchar( ? , ? , ? , ? )}
[Fri Apr 24 2009 02:12:53.936167 3065043872]: Database Connect success!
[Fri Apr 24 2009 02:12:53.936392 3075533728]: [##ERROR##] OnIPCNewMyChartr fail (CharacterIdx: 20)
[Fri Apr 24 2009 02:12:53.936424 3075533728]: [##ERROR##] DB ERROR(MSG) : [FreeTDS][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
[Fri Apr 24 2009 02:12:53.936452 3075533728]: [##ERROR##] DB ERROR(CODE) : 217
[Fri Apr 24 2009 02:12:53.936478 3075533728]: [##ERROR##] DB ERROR(VAR_INFO) :
[Fri Apr 24 2009 02:12:53.936503 3075533728]: [##ERROR##] DB ERROR(SQLSTATE) : 42000
[Fri Apr 24 2009 02:12:53.936529 3075533728]: [##ERROR##] DB ERROR(STM_TEXT) : {call cabal_sp_newchar( ? , ? , ? , ? )}
[Fri Apr 24 2009 02:12:53.963181 3075533728]: Database Connect success!

This is my cabal_sp_newchar procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

/****** Object: Stored Procedure dbo.cabal_sp_newchar Script Date: 2008-4-14 5:52:47 ******/

ALTER PROCEDURE [dbo].[cabal_sp_newchar] (@charname varchar(16), @characteridx int, @style int, @stylemast int)
AS
begin
SET NOCOUNT ON
if( exists ( select * from cabal_badwoard where badword=@charname ) ) select convert(int,0x04)
else if( exists ( select * from cabal_character_table where Name=@charname ) ) select convert(int,0x03)
else if( exists ( select * from cabal_character_table where characteridx=@characteridx ) ) select convert(int,0x02)
else if( @stylemast < 0x01 or @stylemast > 0x06 ) select convert(int,0x02)
else
begin
-----------------------------------------------------------------------------------------------
declare @RegTime dateTime, @userNum int, @serverIdx int, @result int
set @userNum = @characterIdx/8
set @serverIdx = dbo.getServerIdx()
set @RegTime = getDate()
set @result = convert(int, 0xA0) + @stylemast
------------------------------------------------------------------------------------------------

select charTable.*, etcTable.equipmentData, etcTable.inventoryData, etcTable.skillData,
etcTable.quickSlotData, etcTable.questData, etcTable.questFlagsData
into #tempTable
from GAMESERVER.account.dbo.cabal_newCharData_table as charTable,
GAMESERVER.account.dbo.cabal_newEtcData_table as etcTable,
( select top 1 charDataIdx, etcDataIdx from GAMESERVER.account.dbo.cabal_newFlagData_table
where channel = @serverIdx) as flagTable
where flagTable.etcDataIdx = etcTable.dataIdx and flagTable.charDataIdx = charTable.dataIdx
and charTable.classType = etcTable.classType and charTable.classType = @stylemast

IF( NOT EXISTS ( select * from #tempTable))
BEGIN
select convert(int, 0x01)
drop table #tempTable
return
END
-----------------------------------------------------------------------------------------------
-- 캐릭터 카운트 증가
EXEC GAMESERVER.account.dbo.cabal_sp_update_character_count @userNum, @serverIdx, 1

--캐릭터 생성
insert into cabal_character_table( CharacterIdx, Name, LEV, [EXP], [STR], DEX, [INT], PNT, Rank, Alz,
WorldIdx, [Position], Style, HP, MP, SP, SwdPNT, MagPNT, RankEXP, Flags, WarpBField, MapsBField, Reputation, Reserved1 )
select @characteridx, @charname, LEV, [EXP], [STR], [DEX], [INT], [PNT], Rank, Alz,
WorldIdx, Position, @style, HP, MP, SP, SwdPNT, MagPNT, RankEXP, Flags, WarpBField, MapsBField, Reputation, Reserved1
from #TempTable

begin tran
insert into cabal_equipment_table (characterIdx, data) select @characteridx, equipmentData from #TempTable
insert into cabal_inventory_table (characterIdx, data) select @characteridx, inventoryData from #TempTable
insert into cabal_skilllist_table (characterIdx, data) select @characteridx, skillData from #TempTable
insert into cabal_quickslot_table (characterIdx, data) select @characteridx, quickSlotData from #TempTable
insert into cabal_questdata_table (characterIdx, data, flags) select @characteridx, questData, questFlagsData from #TempTable
commit tran
----------------------------------------------------------------------------------------------

-- 리턴값 0xA1 ~ 0xA6
drop table #tempTable
select convert(int, @result)
-----------------------------------------------------------------------------------------------
end

SET NOCOUNT OFF
end

Please help me chumpy or anybody, i spend my whole day try to figure whats wrong but i still can't find it. huhuhu. I believe its the stored procedure.

[MentaL]

[chumpywumpy]

Evrybody has the same stored procedure so it won't be that. It will be your linked server settings. Remove every "gameserver." from the SP (don't forget the dot!) and that will remove the need for the linked server. Do the same in the del char SP too.

[jm_lapido]

Evrybody has the same stored procedure so it won't be that. It will be your linked server settings. Remove every "gameserver." from the SP (don't forget the dot!) and that will remove the need for the linked server. Do the same in the del char SP too.

I dont get it chumpy, you mean that i will delete the GAMESERVER in the Linked Server?

Please tell be what exactly i should do when you say "Remove every "gameserver." from the SP (don't forget the dot!) and that will remove the need for the linked server. Do the same in the del char SP too.".

Sorry chumpy, im a bit spoonfeed here. :(

[chumpywumpy]

Ok, look at this query:

select charTable.*, etcTable.equipmentData, etcTable.inventoryData, etcTable.skillData,
etcTable.quickSlotData, etcTable.questData, etcTable.questFlagsData
into #tempTable
from GAMESERVER.account.dbo.cabal_newCharData_table as charTable,
GAMESERVER.account.dbo.cabal_newEtcData_table as etcTable,
( select top 1 charDataIdx, etcDataIdx from GAMESERVER.account.dbo.cabal_newFlagData_table
where channel = @serverIdx) as flagTable
where flagTable.etcDataIdx = etcTable.dataIdx and flagTable.charDataIdx = charTable.dataIdx
and charTable.classType = etcTable.classType and charTable.classType = @stylemast

That bit i have hilighted tells the server to go into the linked server, then into the account db and then into the cabal_newchardata_table to get the data.

We don't have multiple databases, our account db is on the same server as the gamedb so why bother going via the linked server? There is no reason at all.

select charTable.*, etcTable.equipmentData, etcTable.inventoryData, etcTable.skillData,
etcTable.quickSlotData, etcTable.questData, etcTable.questFlagsData
into #tempTable
from account.dbo.cabal_newCharData_table as charTable,
account.dbo.cabal_newEtcData_table as etcTable,
( select top 1 charDataIdx, etcDataIdx from GAMESERVER.account.dbo.cabal_newFlagData_table
where channel = @serverIdx) as flagTable
where flagTable.etcDataIdx = etcTable.dataIdx and flagTable.charDataIdx = charTable.dataIdx
and charTable.classType = etcTable.classType and charTable.classType = @stylemast

There i have removed the GAMESERVER. from both parts of the query which means the server doesn't need that linked server setting to find the data anymore, which means that query will work even if you have a broken linked server setting or even no linked server setting at all!

There is 1 other mention of GAMESERVER. in the newchar stored procedure so do the same, remove it and this SP will now work regardless of linked server settings.

Now go into the delchar SP and do the same in there to remove the GAMESERVE. entries from the queries. Personally i would suggest going through all of the stored procedures looking for GAMESERVR. entries as there are a few more and you will probably find some interesting looking stored procedures too ;)