Puzzling Guild Error

taurian83 · 09/07/2010, 20:06

So after months of running with no problems of any kind, we shut down for our weekly maintenance. Made a few changes to the item mall and mob drops, but nothing was done in regards to the guilds.

However, upon restart, all players had lost their guild tags. No guilds are showing at the guild manager either.

Interesting part is that ALL info is still intact within the database. Below is the only error I could find. As I said, the puzzling part is that I have gone months with no issues at all, and then out of the blue, we have this.

I have attempted several fixes of the past week or two, sadly inconveniencing my players, but to no avail. When attempting my own fixes, all new queries/procedures ran are all successful. Everything looks good. Until I bring the server back up, then we are right back where we started.

My work schedule has been in flux and I have lost a large amount of my time to sort this. Anyone with any suggestions would be greatly appreciated. And I don't believe in handouts. If anything is needed in return, feel free to ask.

This is only the second time I have ever needed to ask help of the community and is the first time I have been unable to either fix the issue or find the time to fix the issue own my own. This saddens me, but I'll do as I must.

That said, I am also looking for a a "pay per need" hand with a few things I simply do not have the time for and is a bit beyond the grasp of current staff. If anyone is interested, drop me a line.

Thank you in advance for any help that can offered.

-Syn

__________________________________________________ _______________
2010-09-07 12:45:44 PS_DBAGENT__system log start (DBAgent01)

2010-09-07 12:45:46 connect game, Name=

2010-09-07 12:45:46 connect game, Name=

2010-09-07 12:45:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rank'., SQL STATE: 42S22, NATIVE ERROR: 207 (0xCF)

2010-09-07 12:45:46 LoadGuildList failed qerr=-1, EXEC usp_Read_Guild2_R

2010-09-07 12:45:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rank'., SQL STATE: 42S22, NATIVE ERROR: 207 (0xCF)

2010-09-07 12:45:46 LoadGuildList failed qerr=-1, EXEC usp_Read_Guild2_R

2010-09-07 12:45:46 err=-1, [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Rank'., SQL STATE: 42S22, NATIVE ERROR: 207 (0xCF)

2010-09-07 12:45:46 LoadGuildList failed qerr=-1, EXEC usp_Read_Guild2_R

2010-09-07 12:45:48 connect game, Name=
__________________________________________________ _______________

ProfNerwosol · 09/07/2010, 20:12

Can you paste the procedure here so I can have a look?

taurian83 · 09/07/2010, 20:20

Quote:

Originally Posted by ProfNerwosol

Can you paste the procedure here so I can have a look?

Thank you for taking the time Prof. This is the current procedure being used. I had a few others that I had tried to make changes to myself but they all had the same result. In light of this, I simply went back to what had worked in the past, before the fail of amusingly, this same procedure.

"CREATE Proc usp_Read_Guild2_R

AS

SET NOCOUNT ON

SELECT G.GuildID, G.GuildName, G.MasterName, G.Country, G.GuildPoint,
ISNULL(H.Rank,31) Rank, ISNULL(H.Etin,0) Etin, ISNULL(H.UseHouse,0) UseHouse, ISNULL(H.Remark, '') Remark, ISNULL( H.BuyHouse,0) BuyHouse, ISNULL(H.EtinReturnCnt, 0 ) EtinReturnCnt, ISNULL(H.KeepEtin, 0) KeepEtin
FROM Guilds G
LEFT OUTER JOIN GuildDetails H ON G.GuildID = H.GuildID
WHERE G.Del=0
ORDER BY G.Rank DESC

SET NOCOUNT OFF"

ProfNerwosol · 09/07/2010, 22:59

I tested it on my database and it works fine ... It's improbable, but did you loose a column in Guilds or GuildDetails table or was it by any chance renamed? I can't think of any other reason to get such error.

taurian83 · 09/07/2010, 23:09

Yes, this is throwing me through a loop as well. I dont understand where the error is coming from. Unless there was to be a "rank" column in the guild table as well as the guild details table...no. all i can see is fully intact.

i think i am going to browse through my test server's procedures and see if anything doesnt match. it uses all the same setup and is still working fine.

thank you for the time, Prof. Tis very much appreciated.

Shaiya Xtreme · 09/07/2010, 23:12

hmm,

the guild works bevor fine?

i think too you loose any table or procedur.

Did you have test this again ? (i don´t know you loose all Guilds)

Oben the Guild Table copie all, delete the Guild table, refresh the Database use this Procedur, and put the copie files in there. I hope this helps.

CREATE TABLE [dbo].[Guilds] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[GuildID] [int] NOT NULL ,
[GuildName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[MasterUserID] [varchar] (18) COLLATE Latin1_General_CI_AS NOT NULL ,
[MasterCharID] [int] NOT NULL ,
[MasterName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[Country] [tinyint] NOT NULL ,
[TotalCount] [smallint] NOT NULL ,
[GuildPoint] [int] NOT NULL ,
[Del] [tinyint] DEFAULT (0) ,
[CreateDate] [datetime] DEFAULT (getdate()) ,
[DeleteDate] [datetime] DEFAULT (getdate())
) ON [PRIMARY]

and sry my very bad english

ProfNerwosol · 09/08/2010, 00:02

Sorry KillSteal but I don't recommend recreating the table considering it's a live database we are working on.

I did some research and I think I know what might be the issue Did you use QUOTED_IDENTIFIER ON option in any of the changed procedures? If so this might be the problem. When set to ON it forbids usage of reserved words as identifiers for columns and Rank is such a word. I'm not sure what is the range of this option and whether it affects any procedures executed afterwards, but it seems to be the case here. So, either put brackets around [Rank] or insert SET QUOTED_IDENTIFIER OFF at the very beginning when modifying the procedure.

Shaiya Xtreme · 09/08/2010, 00:10

Hi ProfNerwosol

that's clear why I wrote so that it can happen that the created guilds will be deleted. Therefore, he should copy the contents, I made the Guild also times and then I went back though my server was online already.

But I think you're right there have definitely missing something.

'll Also seek views on.

Hi ProfNerwosol

das ist klar deswegen schrieb ich ja das es passieren kann, das die erstellten guilden gelöscht werden. Deswegen sollte er die Guild inhalt kopieren ich machte das auch mal und es ging dann wieder obwohl mein server auch schon online war.

Aber ich denke das du recht hast es fehlt definitiv was.

Werd auch mal weiter suchen.

taurian83 · 09/08/2010, 00:46

Quote:

Originally Posted by ProfNerwosol

Sorry KillSteal but I don't recommend recreating the table considering it's a live database we are working on.

I did some research and I think I know what might be the issue Did you use QUOTED_IDENTIFIER ON option in any of the changed procedures? If so this might be the problem. When set to ON it forbids usage of reserved words as identifiers for columns and Rank is such a word. I'm not sure what is the range of this option and whether it affects any procedures executed afterwards, but it seems to be the case here. So, either put brackets around [Rank] or insert SET QUOTED_IDENTIFIER OFF at the very beginning when modifying the procedure.

Wow...I think this may have been where I went wrong, actually. Uber nub feeling at the moment. I will double check and try it out.

I will also be sure to reply back and let you guys know one way or the other. Thank you again for the effort guys.

-Syn

Bаne · 09/08/2010, 01:45

apex had that problem once. it was when the server was released and there were only 2 guilds at the time and the GLs just remade there guilds.

taurian83 · 09/08/2010, 03:06

Fixed...ready for this?

"CREATE Proc usp_Read_Guild2_R

AS

SET NOCOUNT ON

SELECT G.GuildID, G.GuildName, G.MasterName, G.Country, G.GuildPoint,
ISNULL(H.Rank,31) Rank, ISNULL(H.Etin,0) Etin, ISNULL(H.UseHouse,0) UseHouse, ISNULL(H.Remark, '') Remark, ISNULL( H.BuyHouse,0) BuyHouse, ISNULL(H.EtinReturnCnt, 0 ) EtinReturnCnt, ISNULL(H.KeepEtin, 0) KeepEtin
FROM Guilds G
LEFT OUTER JOIN GuildDetails H ON G.GuildID = H.GuildID
WHERE G.Del=0
ORDER BY G.Rank DESC

SET NOCOUNT OFF"

Line 14..."G.Rank"...I changed the "G" to an "H" as it is put in line 8

I have since restarted the server on 5 attempts and it has worked each time.

-Syn

Bаne · 09/08/2010, 03:09

TY ^.- rember to thank syn and everyone that helped figure this out. # request lock before this gets spam filled

ProfNerwosol · 09/09/2010, 00:25

Quote:

Originally Posted by taurian83

Fixed...ready for this?

"CREATE Proc usp_Read_Guild2_R

AS

SET NOCOUNT ON

SELECT G.GuildID, G.GuildName, G.MasterName, G.Country, G.GuildPoint,
ISNULL(H.Rank,31) Rank, ISNULL(H.Etin,0) Etin, ISNULL(H.UseHouse,0) UseHouse, ISNULL(H.Remark, '') Remark, ISNULL( H.BuyHouse,0) BuyHouse, ISNULL(H.EtinReturnCnt, 0 ) EtinReturnCnt, ISNULL(H.KeepEtin, 0) KeepEtin
FROM Guilds G
LEFT OUTER JOIN GuildDetails H ON G.GuildID = H.GuildID
WHERE G.Del=0
ORDER BY G.Rank DESC

SET NOCOUNT OFF"

Line 14..."G.Rank"...I changed the "G" to an "H" as it is put in line 8

I have since restarted the server on 5 attempts and it has worked each time.

-Syn

Now I feel like a noob >.< Never underestimate the power of a hunch. It was telling me "go for the G, change to H" ... I went, nah, go check google.