Character Database Bug Fix (SQL Script)

[wolfulus]

teroare asked me to make this script, for fix bug when trying to create character into a wrong filled account character row

-- Script by WoLf (02/02/2008)
-- Keep credits if you will repost this

DECLARE @account NVARCHAR(10)
DECLARE @char1 NVARCHAR(10)
DECLARE @char2 NVARCHAR(10)
DECLARE @char3 NVARCHAR(10)
DECLARE @char4 NVARCHAR(10)
DECLARE @char5 NVARCHAR(10)

DECLARE @fixed INT
SET @fixed = 0

DECLARE fixcr CURSOR FOR 
SELECT Id, ISNULL(GameID1, ''), ISNULL(GameID2, ''), ISNULL(GameID3, ''), ISNULL(GameID4, ''), ISNULL(GameID5, '') FROM AccountCharacter

OPEN fixcr

	FETCH NEXT FROM fixcr INTO @account, @char1, @char2, @char3, @char4, @char5

	WHILE @@FETCH_STATUS = 0
	BEGIN

		IF(@char1 <> '')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM Character WHERE AccountID = @account AND Name = @char1))
			BEGIN
				UPDATE AccountCharacter SET GameID1 = NULL WHERE Id = @account
				SET @fixed = @fixed + 1
			END
		END
		IF(@char2 <> '')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM Character WHERE AccountID = @account AND Name = @char2))
			BEGIN
				UPDATE AccountCharacter SET GameID2 = NULL WHERE Id = @account
				SET @fixed = @fixed + 1
			END
		END
		IF(@char3 <> '')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM Character WHERE AccountID = @account AND Name = @char3))
			BEGIN
				UPDATE AccountCharacter SET GameID3 = NULL WHERE Id = @account
				SET @fixed = @fixed + 1
			END
		END
		IF(@char4 <> '')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM Character WHERE AccountID = @account AND Name = @char4))
			BEGIN
				UPDATE AccountCharacter SET GameID4 = NULL WHERE Id = @account
				SET @fixed = @fixed + 1
			END
		END
		IF(@char5 <> '')
		BEGIN
			IF(NOT EXISTS(SELECT * FROM Character WHERE AccountID = @account AND Name = @char5))
			BEGIN
				UPDATE AccountCharacter SET GameID5 = NULL WHERE Id = @account
				SET @fixed = @fixed + 1
			END
		END

		FETCH NEXT FROM fixcr INTO @account, @char1, @char2, @char3, @char4, @char5

	END

CLOSE fixcr
DEALLOCATE fixcr

Credits: WoLf (me)

[MentaL]

[teroare]

Thanks m8 , ill be forever thankful for this !!

[53rJ]

What is this for? i don't understend

[noobies]

nice release, will help lots & lots of noobies :P

[wolfulus]

when you can't create characters, in a "empty" account, this solves the problem if the cause was wrong filled rows in AccountCharacter table

[kapeika]

senks wolfulus for the release

[53rJ]

Thx, nice

[Maurioski]

mmmMM!!.. nice , and cheking the old scripts from webzen

[hamiltonsl]

it better explains for what it serves this and so that version?

[wolfulus]

it checks if all the names on "AccountCharacter" exists on Character table, if don't, it will remove it

[BlackEye]

Hm...Interesting,need to try!

[ScanX]

Thanks man

[DraGoN-GR]

BlackEye your Web site is the MMPORG TOP GAME LIST?

[jlroco33]

Thx Wolf, great !!!