How to remove the need for a linked server

[chumpywumpy]

Since so many people utterly fail to follow the guides on how to properly create the linked server i finally give up trying to teach people how to do it. Instead i will try removing the linked server requirement altogether.

I thought we actually had one of these topics already but i can't find it now (which means somebody is probably gonna post it below).

Open up Query Analyser (or management studo for 2005 users). Find cabal_sp_newchar in the gamedb. Right-click and Edit.

Delete everything in that window and paste in everything below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS 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 account.dbo.cabal_newCharData_table as charTable,
account.dbo.cabal_newEtcData_table as etcTable,
( select top 1 charDataIdx, etcDataIdx from 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 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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Now press F5 or Execute.

Next find cabal_sp_delchar and do the same.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

ALTER PROCEDURE [dbo].[cabal_sp_delchar] (@characteridx int)
AS

begin
SET NOCOUNT ON
DECLARE @ROWCOUNT int

BEGIN
--If GuildMaster, return 0xB2
SELECT @ROWCOUNT = COUNT(A.GroupIndex) FROM GUILDGROUP A
INNER JOIN
(
SELECT GroupIndex FROM GUILDMEMBER WHERE CharacterIndex = @characteridx
) B
ON A.GroupIndex = B.GroupIndex
WHERE A.GroupPermission = 0

IF( @ROWCOUNT > 0 )
BEGIN
SELECT CONVERT(INT, 0xB2)
SET NOCOUNT OFF
RETURN
END
END

begin tran
INSERT INTO DELETED_CABAL_CHARACTER_TABLE(
CHARACTERIDX, NAME, LEV, EXP, STR, DEX, INT, PNT, RANK, ALZ, WORLDIDX, POSITION, STYLE, HP, MP, SWDPNT, MAGPNT, RANKEXP, FLAGS, WARPBFIELD, MAPSBFIELD, SP, PENALTYEXP, LOGOUTTIME, RP, REPUTATION, LOGINTIME, PLAYTIME, CHANNELIDX, CreateDate, EQUIPDATA, INVENDATA, SKILLDATA, QSLOTDATA, QDFLAGS, QSTDATA, QSTFLAGS, Level0, Level1, Level2, Level3, Level4, Exp0, Exp1, Exp2, Exp3, Exp4, CraftFlags, PKPenalty, Nation, BBeadData)
SELECT
A.CHARACTERIDX, NAME, LEV, EXP, STR, DEX, INT, PNT, RANK, ALZ, WORLDIDX, POSITION, STYLE, HP, MP, SWDPNT, MAGPNT, RANKEXP, A.FLAGS, WARPBFIELD, MAPSBFIELD, SP, PENALTYEXP, LOGOUTTIME, RP, REPUTATION, LOGINTIME, PLAYTIME, CHANNELIDX, CreateDate, B.DATA, C.DATA, D.DATA, E.DATA, F.FLAGS, G.DATA, G.FLAGS, H.Level0, H.Level1, H.Level2, H.Level3, H.Level4, H.Exp0, H.Exp1, H.Exp2, H.Exp3, H.Exp4, H.Flags, A.PKPenalty, A.Nation, I.DATA
FROM CABAL_CHARACTER_TABLE A
LEFT OUTER JOIN CABAL_EQUIPMENT_TABLE B ON A.CHARACTERIDX = B.CHARACTERIDX
LEFT OUTER JOIN CABAL_INVENTORY_TABLE C ON A.CHARACTERIDX = C.CHARACTERIDX
LEFT OUTER JOIN CABAL_SKILLLIST_TABLE D ON A.CHARACTERIDX = D.CHARACTERIDX
LEFT OUTER JOIN CABAL_QUICKSLOT_TABLE E ON A.CHARACTERIDX = E.CHARACTERIDX
LEFT OUTER JOIN CABAL_QDDATA_TABLE F ON A.CHARACTERIDX = F.CHARACTERIDX
LEFT OUTER JOIN CABAL_QUESTDATA_TABLE G ON A.CHARACTERIDX = G.CHARACTERIDX
LEFT OUTER JOIN CABAL_CRAFT_TABLE H ON A.CHARACTERIDX = H.CHARACTERIDX
LEFT OUTER JOIN CABAL_BBEAD_TABLE I ON A.CHARACTERIDX = I.CHARACTERIDX
WHERE A.CHARACTERIDX = @characteridx

delete from chat_buddy_table where (RegisterCharIdx = @characteridx) OR (RegisteeCharIdx = @characteridx)
delete from chat_buddygroup_table where CharIdx = @characteridx
delete from guildmember where characterindex = @characteridx
delete from cabal_bbead_table where CharacterIdx=@characteridx
delete from cabal_craft_table where Characteridx=@characteridx
delete from cabal_equipment_table where CharacterIdx=@characteridx
delete from cabal_inventory_table where CharacterIdx=@characteridx
delete from cabal_skilllist_table where CharacterIdx=@characteridx
delete from cabal_quickslot_table where CharacterIdx=@characteridx
delete from cabal_questdata_table where CharacterIdx=@characteridx
delete from cabal_qddata_table where CharacterIdx=@characteridx
delete from cabal_character_table where CharacterIdx=@characteridx

set @ROWCOUNT = @@ROWCOUNT
commit tran
IF( @ROWCOUNT > 0 )
BEGIN
-- ???? ??? ???
DECLARE @USERNUM INT, @SERVERIDX INT
SET @USERNUM = @CHARACTERIDX/8
SET @SERVERIDX = DBO.GETSERVERIDX()
EXEC ACCOUNT.dbo.CABAL_SP_UPDATE_CHARACTER_COUNT @USERNUM, @SERVERIDX, -1
END

SELECT CONVERT(INT, 0xA1)

SET NOCOUNT OFF
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Hopefully that will stop the endless stream of failed-to-pay-attention.

 

[zputt]

no succes man promlem on going

 

[cypher]

it`s fine lol

 

[zaugola]

for those who have trouble with the linked server... just paste this into ur query analyser n change ur sa passwd then hit F5 (excute query)

/****** Object:  LinkedServer [GAMESERVER]    Script Date: 05/27/2009 17:46:55 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'GAMESERVER', @srvproduct=N'GameServer', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=LOCALHOST;UID=[COLOR="Red"][SIZE="4"]SA[/SIZE][/COLOR];PWD=[COLOR="Red"][SIZE="4"]123[/SIZE][/COLOR];'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=N'NT AUTHORITY\SYSTEM',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'use remote collation', @optvalue=N'true'

 

[Bzzz]

what advantages it would give?

 

[cypher]

less headache for us when we have to figure out what`s the problem in some noob server.

 

[MaximumSatan]

So this do so I dont need a linked server?

 

[cypher]

yeah, but i still use linked servers... i like em xD

 

[Tommy1]

looks like Stored Production is important for all Online Game's thanks for this TUT.








~ TommY

 

[Xc0deRed93]

Chump What is the Exact Advantage of removing this Linked Server? Do it lessen the Lags? and What if i remove this Linked Server, What Happen to the Existing or Stored Accounts and Character in my database Would it be deleted or what?

Sorry for my English :

 

[TanGzkie]

prime23

all the existing account in database is not be affected even if you remove the "LS" and why??..
hmn... have you ever encounter an error ink [unix]blabla or doesnt exist..if you check you var/log/cabal??
That is the LS <---- This is just your Test Account or Let me say Full test account that's why it has a LS created...


correct me if im wrong cause that's my point of view about this LS thingy..

 

[Alphakilo23]

Chump What is the Exact Advantage of removing this Linked Server? Do it lessen the Lags? and What if i remove this Linked Server

AFAIK it removes the delay when creating / deleting an character. I couldn't measure any changes in the perfomance on any other task.
But in theorie, it should increase the perfomance (removing the need for distributed transactions). Most likely you won't notice it.

What Happen to the Existing or Stored Accounts and Character in my database Would it be deleted or what?

Removing the linked server won't impact your database (under normal circumstances, when you're working with replication, what you most likely don't, it's another story).

 

[Xc0deRed93]

Thank you very much Mr. Tangzkie and Mr. Alphakilo23
That was the info i need to know, well clear now :

 

[TheMaker99]

i ficed it guys.

here is the code ! write it in the query and u will get the linked server , change pw !


/****** Object: LinkedServer [GAMESERVER] Script Date: 00/00/0000 00:00:00 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'GAMESERVER', @srvproduct=N'GameServer', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=LOCALHOST;UID=SA;PWD=(YourPwHere);'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=N'NT AUTHORITY\SYSTEM',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMESERVER',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GAMESERVER', @optname=N'use remote collation', @optvalue=N'true'






thanks zaugola ! i just added it little and its worked

 

[xXxAxXx]

i have mssql 2005 but i dont have the stored procedure folder.
how can i aply this modification?

 

[Amaymon]

Because u didin`t restore correct the database,thats why.

 

[emi]

ofcourse you have
herenot this is in sql 2005)

 

[xXxAxXx]

thanks emi.Its correct

 

[StonyPony]

Still having this problem.
Had it working fine at one time, but then went to make a character about a week later, and it acted up again? I did do the patch for failure to make guild between there (added IDX to database),
so maybe something got messed up?

I checked the logs, found nothing odd related to it.

If somebody can tell me a way to reinstall all the correct functions without losing my players' data, I'm willing to do that.
I just switched to Serenity Cabal's repack, because of the problem, and it's still happening, so I guess it's not the files.

Thanks for the help!

PS: Tried it on 2 different accounts, as well. Still can't make a new char.

Note: This may be a necropost, but it's directly related to this, and didn't want somebody posting me TO this, even if I linked it saying I did it (stranger things have happened).
If this is still against the rules, please split this to a new topic. Is there another thing that could be causing this?Is there another thing that could be causing this?

I checked my logs, and saw nothing that resembles this problem. checked my logs, and saw nothing that resembles this problem.

 

[thonght]

2013-07-19 @ 03:43:32 : com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function up_AddMyCashItem has too many arguments specified.

2013-07-19 @ 03:43:42 : com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function up_AddMyCashItem has too many arguments specified.

every one can help me to fix it ?