[APORTE] Scripts SQL

ATENCION! Con esto sigo el post de Wiz Khalifa - VER si el me lo permite, voy a citar su post.

Gente, les dejo este pequeño pack de scripts que les va a servir siempre cuando quieran montan un servidor de Mu Online. Lo que deben hacer es abrir el analizador de consultas usando la base "MuOnline" o en su defecto crear una nueva tarea si creen que el script debe ser automatico en distintos horarios. Dejo explicacion de Wiz Khalifa de como entrar al analizador.

...ir a Inicio/Programas/Microsoft Sql Server/Analizador de Consultas. Entraremos al Analizardor de Consultas y ingresaremos con nuestra ACC (GENERALMENTE EL USUARIO ES "sa" YA QUE ES UN USUARIO QUE VIENE CON SQL Y ES UN USUARIO PREDETERMINADO) de la cuenta de SQL y la contraseña de esa cuenta con la que ingresamos. Luego de haber ingresado, les aparecera un recuadro en blanco, donde ahi pegaran los CODIGOS,SENTENCIAS,SCRIPTS.
Luego de haber pegado el codigo, deben presionar F5 y lo que hace eso es que el codigo se carge automaticamente. Luego de haber pegado una sentencia "x" por ejemplo: Borrar escores a todas las guilds, para no tener que salir pueden apretar Ctrl N, lo que eso hara sera abrir otra ventana BLANCA como la que nos aparecio al princio y pegan otro codigo y presionan F5 nuevamente para activar automaticamente el codigo. Luego de eso,
cierran en la cruz de la ventana blanca que se les abrio y guardan el archivo donde a ustedes les quede mas comodo.

Ojo, estos Scripts son globales, afectan a todos los personajes de la tabla seleccionada, al final les explicare como hacer para seleccionar un personaje en particular.


Zen FIX (Cuando queda el zen negativo).

Código:

UPDATE Character
SET Money = 2000000000
WHERE Money < 1 OR Money > 2000000

Stats FIX (Puntos bugeados). Si tienen personajes con otros stats, los agregan de la misma forma, como el DL por ejemplo.

Código:

UPDATE Character
SET Strength = 32767
WHERE Strength < 0 OR Strength > 32767
SET Dexterity = 32767
WHERE Dexterity < 0 OR Dexterity > 32767
SET Vitality = 32767
WHERE Vitality < 0 OR Vitality > 32767
SET Energy = 32767
WHERE Energy < 0 OR Energy > 32767

Guild FIX (Score negativo).

Código:

UPDATE Guild
SET G_Score=('0')
WHERE G_Score<0

Users ON/OFF FIX (Cuando tenemos algun inconveniente y el servidor se apaga, las cuentas quedan "pegadas" y los usuarios parecen online, sin estarlo)

Código:

UPDATE MEMB_STAT
SET ConnectStat='0'
WHERE ConnectStat>0

PK FIX (Ojo, limpia el estado de asesino, no soluciona el PK BUG, eso lo veremos en otro post).

Código:

UPDATE Character
SET PkLevel = ('0'), PkTime = ('0'), PkCount = ('0')
Where PkLevel>2

Dos cosas a tener encuenta, es importante saber cuando una sentencia busca un personaje, o busca una cuenta. Por ejemplo.

Esta sentencia busca un personaje.

Código:

UPDATE Character
SET Inventory=('')

Esta sentencia busca una cuenta.

Código:

UPDATE warehouse
SET Items=('')

Para buscar en particular, quedaria de la siguiente manera.

Código:

UPDATE Character
SET Inventory=('')
WHERE Name='Netbios'

UPDATE warehouse
SET Items=('')
WHERE AccountID='Netbios'

Eliminar por TRUNCATE. (Importante para eliminar una tabla entera)

Ejemplo

Código:

TRUNCATE TABLE Character

Con esto estariamos eliminando TODO el contenido de esa tabla, para eliminar otras tablas o agregamos una nueva fila para truncar tablas simultaneamente.

Ejemplo

Código:

TRUNCATE TABLE Character
TRUNCATE TABLE MEMB_INFO

Eliminar Resets.

Código:

UPDATE Character
SET Resets=('0')
WHERE Resets>0

Eliminar Inventarios.

Código:

UPDATE Character
SET Inventory=('')

Eliminar Baules.

Código:

UPDATE warehouse
SET Items=('')

Eliminar Baneo.

Código:

UPDATE Character
SET CtlCode=('0')
WHERE CtlCode=('1')

Elimina Puntos (A agregar, no ya asignados).

Código:

UPDATE Character
SET LevelUpPoints=('0')
WHERE LevelUpPoints>0

Eliminar cuentas vacias (Mantenimiento)

Paso 1 (Correr esta sentencia primero, solo una vez)

Código:

ALTER TABLE memb_info
ADD ACTIVE int
ALTER TABLE VI_CURR_INFO
ADD ACTIVE int

Paso 2 (Esto eliminara las cuentas inactivas)

Código:

UPDATE dbo.memb_info
SET active = 0
UPDATE dbo.memb_info
SET active = 1 FROM memb_info join Character on memb_info.memb___id=Character.AccountID
COLLATE Latin1_general_CI_AS
UPDATE dbo.VI_CURR_INFO
SET active = 0
UPDATE dbo.VI_CURR_INFO
SET active = 1 FROM VI_CURR_INFO join Character on VI_CURR_INFO.memb___id=Character.AccountID
COLLATE Latin1_general_CI_AS
SELECT * FROM VI_CURR_INFO join MEMB_INFO on VI_CURR_INFO.memb___id=MEMB_INFO.memb___id
COLLATE Latin1_general_CI_AS WHERE memb_info.active = 0 AND VI_CURR_INFO.active=0
DELETE FROM VI_CURR_INFO WHERE VI_CURR_INFO.active=0
DELETE FROM MEMB_INFO WHERE memb_info.active = 0

ATENCION! No es de mi autoria.

Código:


USE MuOnline
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_CC_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_CC_OFFLINE_GIFT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_DL_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_DL_OFFLINE_GIFT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_FRIEND_STONE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_FRIEND_STONE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_RINGGIFT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_RINGGIFT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_RINGGIFT_TEMP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_RINGGIFT_TEMP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_REG_SERIAL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_REG_SERIAL]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShowResourceInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShowResourceInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_EMP6]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_EMP6]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Admin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Admin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_BLOOD_CASTLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[T_BLOOD_CASTLE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_BLOOD_PLAYCOUNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_BLOOD_PLAYCOUNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CC_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_CC_OFFLINE_GIFT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CC_OFFLINE_GIFTNAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_CC_OFFLINE_GIFTNAME]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DL_OFFLINE_GIFT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_DL_OFFLINE_GIFT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DL_OFFLINE_GIFTNAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_DL_OFFLINE_GIFTNAME]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FRIENDSHIP_SERVERRANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_FRIENDSHIP_SERVERRANK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FRIENDSHIP_STONE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_FRIENDSHIP_STONE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MU2003_EVENT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_MU2003_EVENT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RegCount_Check]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RegCount_Check]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Register_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Register_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingAttackEvent_Gift]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingAttackEvent_Gift]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingEvent_GiftName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingEvent_GiftName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RingEvent_OfflineGift]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_RingEvent_OfflineGift]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Serial_Bank]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Serial_Bank]
GO

CREATE TABLE [dbo].[Admin] (
   [Id] [char] (10) NOT NULL ,
   [Pass] [char] (10) NULL ,
   [AdminLevel] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_BLOOD_CASTLE] (
   [AccountID] [varchar] (10) NOT NULL ,
   [StoneCount] [int] NOT NULL ,
   [Check_Code] [int] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_BLOOD_PLAYCOUNT] (
   [AccountId] [varchar] (50) NOT NULL ,
   [PlayCount] [int] NOT NULL ,
   [RecoverCheck] [bit] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_CC_OFFLINE_GIFT] (
   [Guid] [int] IDENTITY (1, 1) NOT NULL ,
   [Server] [int] NULL ,
   [AccountID] [varchar] (10) NULL ,
   [CharName] [varchar] (10) NULL ,
   [GiftKind] [int] NOT NULL ,
   [Date_Give] [smalldatetime] NOT NULL ,
   [Date_Reg] [smalldatetime] NULL ,
   [RegCheck] [tinyint] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_CC_OFFLINE_GIFTNAME] (
   [GiftKind] [int] NOT NULL ,
   [GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_DL_OFFLINE_GIFT] (
   [Guid] [int] IDENTITY (1, 1) NOT NULL ,
   [Server] [int] NULL ,
   [AccountID] [varchar] (10) NULL ,
   [CharName] [varchar] (10) NULL ,
   [GiftKind] [int] NOT NULL ,
   [Date_Give] [smalldatetime] NOT NULL ,
   [Date_Reg] [smalldatetime] NULL ,
   [RegCheck] [tinyint] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_DL_OFFLINE_GIFTNAME] (
   [GiftKind] [int] NOT NULL ,
   [GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] (
   [ServerCode] [smallint] NOT NULL ,
   [FriendShipStoneCount] [int] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_FRIENDSHIP_STONE] (
   [AccountID] [varchar] (10) NOT NULL ,
   [ServerCode] [smallint] NOT NULL ,
   [CharName] [varchar] (10) NOT NULL ,
   [FriendShipStoneCount] [int] NOT NULL ,
   [Check_Code] [tinyint] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_MU2003_EVENT] (
   [AccountID] [varchar] (50) NOT NULL ,
   [EventChips] [smallint] NOT NULL ,
   [MuttoIndex] [int] NOT NULL ,
   [MuttoNumber] [int] NOT NULL ,
   [Check_Code] [char] (1) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_RegCount_Check] (
   [AccountID] [varchar] (10) NOT NULL ,
   [RegCount] [int] NOT NULL ,
   [RegAlready] [bit] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_Register_Info] (
   [F_Register_Section] [smallint] NOT NULL ,
   [F_Register_Name] [varchar] (50) NOT NULL ,
   [F_Register_TotalCount] [int] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_RingAttackEvent_Gift] (
   [GUID] [int] IDENTITY (1, 1) NOT NULL ,
   [AccountID] [varchar] (50) NOT NULL ,
   [GiftKind] [tinyint] NOT NULL ,
   [RegisterDate] [datetime] NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_RingEvent_GiftName] (
   [GiftKind] [tinyint] NOT NULL ,
   [GiftName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_RingEvent_OfflineGift] (
   [GUID] [smallint] IDENTITY (1, 1) NOT NULL ,
   [AccountID] [varchar] (10) NULL ,
   [GiftSection] [tinyint] NOT NULL ,
   [GiftKind] [tinyint] NOT NULL ,
   [RegisterDate] [datetime] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[T_Serial_Bank] (
   [F_Serial_Guid] [int] IDENTITY (1, 1) NOT NULL ,
   [P_Serial_1] [char] (4) NOT NULL ,
   [P_Serial_2] [char] (4) NOT NULL ,
   [P_Serial_3] [char] (4) NOT NULL ,
   [F_Serial_Section] [smallint] NOT NULL ,
   [F_Member_Guid] [int] NULL ,
   [F_Member_Id] [char] (10) NULL ,
   [F_Register_Section] [smallint] NULL ,
   [F_Register_Date] [smalldatetime] NULL ,
   [F_Create_Date] [smalldatetime] NOT NULL ,
   [F_RegisterCheck] [bit] NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_BLOOD_CASTLE] WITH NOCHECK ADD
   CONSTRAINT [PK_T_BLOOD_CASTLE] PRIMARY KEY  CLUSTERED
   (
      [AccountID]
   )  ON [PRIMARY]
GO



ALTER TABLE [dbo].[T_BLOOD_PLAYCOUNT] WITH NOCHECK ADD
   CONSTRAINT [PK_T_BLOOD_PLAYCOUNT] PRIMARY KEY  CLUSTERED
   (
      [AccountId]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_CC_OFFLINE_GIFTNAME] WITH NOCHECK ADD
   CONSTRAINT [PK_T_CC_OFFLINE_GIFTNAME] PRIMARY KEY  CLUSTERED
   (
      [GiftKind]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_DL_OFFLINE_GIFTNAME] WITH NOCHECK ADD
   CONSTRAINT [PK_T_DL_OFFLINE_GIFTNAME] PRIMARY KEY  CLUSTERED
   (
      [GiftKind]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] WITH NOCHECK ADD
   CONSTRAINT [PK_T_FRIENDSHIP_SERVERRANK] PRIMARY KEY  CLUSTERED
   (
      [ServerCode]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_FRIENDSHIP_STONE] WITH NOCHECK ADD
   CONSTRAINT [PK_T_T] PRIMARY KEY  CLUSTERED
   (
      [AccountID],
      [ServerCode],
      [CharName]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_MU2003_EVENT] WITH NOCHECK ADD
   CONSTRAINT [PK_T_MU2003_EVENT_1] PRIMARY KEY  CLUSTERED
   (
      [AccountID]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_RegCount_Check] WITH NOCHECK ADD
   CONSTRAINT [PK_T_RegCount_Check] PRIMARY KEY  CLUSTERED
   (
      [AccountID]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_Register_Info] WITH NOCHECK ADD
   CONSTRAINT [PK_T_Register_Info] PRIMARY KEY  CLUSTERED
   (
      [F_Register_Section]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_RingAttackEvent_Gift] WITH NOCHECK ADD
   CONSTRAINT [PK_T_RingAttackEvent_Gift] PRIMARY KEY  CLUSTERED
   (
      [GUID],
      [GiftKind],
      [RegisterDate]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_RingEvent_OfflineGift] WITH NOCHECK ADD
   CONSTRAINT [PK_T_RingEvent_OfflineGift] PRIMARY KEY  CLUSTERED
   (
      [GUID],
      [GiftSection]
   )  ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_Serial_Bank] WITH NOCHECK ADD
   CONSTRAINT [PK_T_Serial_Bank] PRIMARY KEY  CLUSTERED
   (
      [P_Serial_1],
      [P_Serial_2],
      [P_Serial_3]
   )  ON [PRIMARY]
GO


 CREATE  CLUSTERED  INDEX [IX_T_CC_OFFLINE_GIFT] ON [dbo].[T_CC_OFFLINE_GIFT]([Date_Give]) ON [PRIMARY]
GO


 CREATE  CLUSTERED  INDEX [IX_T_DL_OFFLINE_GIFT] ON [dbo].[T_DL_OFFLINE_GIFT]([Date_Give]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_BLOOD_CASTLE] ADD
   CONSTRAINT [DF_T_BLOOD_CASTLE_StoneCount] DEFAULT (0) FOR [StoneCount],
   CONSTRAINT [DF_T_BLOOD_CASTLE_Check_Code] DEFAULT (0) FOR [Check_Code]
GO


ALTER TABLE [dbo].[T_CC_OFFLINE_GIFT] ADD
   CONSTRAINT [DF_T_CC_OFFLINE_GIFT_Date_Reg] DEFAULT (getdate()) FOR [Date_Reg],

   CONSTRAINT [DF_T_CC_OFFLINE_GIFT_RegCheck] DEFAULT (0) FOR [RegCheck],
   CONSTRAINT [PK_T_CC_OFFLINE_GIFT] PRIMARY KEY  NONCLUSTERED
   (
      [Guid]
   )  ON [PRIMARY]
GO


 CREATE  INDEX [IX_T_CC_OFFLINE_GIFT_1] ON [dbo].[T_CC_OFFLINE_GIFT]([AccountID]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_DL_OFFLINE_GIFT] ADD
   CONSTRAINT [DF_T_DL_OFFLINE_GIFT_Date_Reg] DEFAULT (getdate()) FOR [Date_Reg],
   CONSTRAINT [DF_T_DL_OFFLINE_GIFT_RegCheck] DEFAULT (0) FOR [RegCheck],
   CONSTRAINT [PK_T_DL_OFFLINE_GIFT] PRIMARY KEY  NONCLUSTERED
   (
      [Guid]
   )  ON [PRIMARY]
GO


 CREATE  INDEX [IX_T_DL_OFFLINE_GIFT_1] ON [dbo].[T_DL_OFFLINE_GIFT]([AccountID]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_FRIENDSHIP_SERVERRANK] ADD
   CONSTRAINT [DF_T_FRIENDSHIP_SERVERRANK_FriendShipStoneCount] DEFAULT (0) FOR [FriendShipStoneCount]
GO


ALTER TABLE [dbo].[T_FRIENDSHIP_STONE] ADD
   CONSTRAINT [DF_T_T_FriendShipStoneCount] DEFAULT (0) FOR [FriendShipStoneCount],

   CONSTRAINT [DF_T_T_Check_Code] DEFAULT (0) FOR [Check_Code]
GO


ALTER TABLE [dbo].[T_MU2003_EVENT] ADD
   CONSTRAINT [DF_T_MU2003_EVENT_EventChips_1] DEFAULT (0) FOR [EventChips],
   CONSTRAINT [DF_T_MU2003_EVENT_MuttoIndex_1] DEFAULT ((-1)) FOR [MuttoIndex],
   CONSTRAINT [DF_T_MU2003_EVENT_MuttoNumber_1] DEFAULT (0) FOR [MuttoNumber],
   CONSTRAINT [DF_T_MU2003_EVENT_Check_Code] DEFAULT (0) FOR [Check_Code]
GO


ALTER TABLE [dbo].[T_RegCount_Check] ADD
   CONSTRAINT [DF_T_RegCount_Check_RegCount] DEFAULT (1) FOR [RegCount],
   CONSTRAINT [DF_T_RegCount_Check_RegAlready] DEFAULT (0) FOR [RegAlready]
GO


ALTER TABLE [dbo].[T_RingAttackEvent_Gift] ADD

   CONSTRAINT [DF_T_RingAttackEvent_Gift_RegisterDate] DEFAULT (getdate()) FOR [RegisterDate]
GO


 CREATE  INDEX [IX_T_RingAttackEvent_Gift] ON [dbo].[T_RingAttackEvent_Gift]([AccountID]) ON [PRIMARY]
GO


 CREATE  INDEX [IX_T_RingEvent_OfflineGift] ON [dbo].[T_RingEvent_OfflineGift]([AccountID]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[T_Serial_Bank] ADD

   CONSTRAINT [DF_T_Serial_Bank_F_Create_Date] DEFAULT (getdate()) FOR [F_Create_Date],
   CONSTRAINT [DF_T_Serial_Bank_F_RegisterCheck] DEFAULT (0) FOR [F_RegisterCheck]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




--//************************************************************************
--// ³»  ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.05.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--// ¸®ÅÏ°ª ¼³¸í
--//
--//    ¼º°øÄÚµå :   0 : »óÇ°µî·Ï ½ÇÆÐ (¾øÀ½)
--//         1 : »óÇ°µî·Ï ¼º°ø
--//    ¼±¹°À̸§ :   T_CC_OFFLINE_GIFTNAME °ú GiftKind ¸¦ JOIN ÇÑ °á°ú
--//
--//************************************************************************


CREATE PROCEDURE   SP_REG_CC_OFFLINE_GIFT
   @AccountID      varchar(10),   
   @CharName      varchar(10),   
   @ServerCode      int
As
Begin
   BEGIN TRANSACTION


   DECLARE @iGIFT_GUID   INT      -- ¹ÞÀ» »óÇ°ÀÇ GUID
   DECLARE @iGIFT_KIND   INT      -- ¹ÞÀ» »óÇ°ÀÇ Á¾·ù
   DECLARE @iGIFT_NAME   VARCHAR(50)   -- ¹ÞÀ» »óÇ°ÀÇ À̸§

   SET NOCOUNT ON


   IF EXISTS (SELECT AccountID Guid FROM T_CC_OFFLINE_GIFT WHERE AccountID = @AccountID)
   BEGIN
      -- ÀÌ »ç¶÷Àº ÀÌÀü¿¡ Çѹø ´ç÷µÈ »ç¶÷À̹ǷΠ¶Ç °æÇ°À» ÁÙ ¼ö ¾ø´Ù.
      SELECT 0 As ResultCode, '' As GiftName
   END
   ELSE
   BEGIN
      IF EXISTS (SELECT TOP 1 Guid FROM T_CC_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = 0 ORDER BY Guid ASC)
      BEGIN
         -- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ ³²¾ÆÀÖ´Ù.
         SELECT
 TOP 1 @iGIFT_GUID = Guid, @iGIFT_KIND = GiftKind  FROM
T_CC_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = 0 ORDER
BY Guid ASC

         -- ¿©±â¼­ ÇØ´ç »ç¿ëÀÚÀÇ °æÇ°À¸·Î ÁöÁ¤ÇØ ÁØ´Ù.
         UPDATE
 T_CC_OFFLINE_GIFT SET Server = @ServerCode, AccountID = @AccountID,
CharName = @CharName, Date_Reg = GetDate(), RegCheck = 1 WHERE Guid =
@iGIFT_GUID

         SELECT @iGIFT_NAME = GiftName FROM T_CC_OFFLINE_GIFTNAME WHERE GiftKind = @iGIFT_KIND

         SELECT 1 As ResultCode, @iGIFT_NAME As GiftName
      END
      ELSE
      BEGIN
         -- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ Çϳªµµ ¾ø´Ù.
         SELECT 0 As ResultCode, '' As GiftName
      END
   END




   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION
   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






--//************************************************************************
--// ³»  ¿ë : ´ÙÅ©·ÎµåÀÇ ¸¶À½ À̺¥Æ® °æÇ°µî·Ï
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.08.11
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--// ¸®ÅÏ°ª ¼³¸í
--//

--//    ¼º°øÄÚµå :   0 : »óÇ°µî·Ï ½ÇÆÐ (¾øÀ½)
--//         1 : »óÇ°µî·Ï ¼º°ø

--//    ¼±¹°À̸§ :   T_DL_OFFLINE_GIFTNAME °ú GiftKind ¸¦ JOIN ÇÑ °á°ú
--//
--//************************************************************************


CREATE PROCEDURE   SP_REG_DL_OFFLINE_GIFT
   @AccountID      varchar(10),   
   @CharName      varchar(10),   
   @ServerCode      int
As
Begin
   BEGIN TRANSACTION


   DECLARE @iGIFT_GUID   INT      -- ¹ÞÀ» »óÇ°ÀÇ GUID
   DECLARE @iGIFT_KIND   INT      -- ¹ÞÀ» »óÇ°ÀÇ Á¾·ù
   DECLARE @iGIFT_NAME   VARCHAR(50)   -- ¹ÞÀ» »óÇ°ÀÇ À̸§

   SET NOCOUNT ON


   IF EXISTS (SELECT AccountID Guid FROM T_DL_OFFLINE_GIFT WHERE AccountID = @AccountID)
   BEGIN
      -- ÀÌ »ç¶÷Àº ÀÌÀü¿¡ Çѹø ´ç÷µÈ »ç¶÷À̹ǷΠ¶Ç °æÇ°À» ÁÙ ¼ö ¾ø´Ù.
      SELECT 0 As ResultCode, '' As GiftName
   END
   ELSE
   BEGIN
      IF
 EXISTS (SELECT TOP 1 Guid FROM T_DL_OFFLINE_GIFT WHERE Date_Give <
GetDate() and RegCheck = CAST(0 AS BIT) ORDER BY Guid ASC)
      BEGIN
         -- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ ³²¾ÆÀÖ´Ù.
         SELECT
 TOP 1 @iGIFT_GUID = Guid, @iGIFT_KIND = GiftKind  FROM
T_DL_OFFLINE_GIFT WHERE Date_Give < GetDate() and RegCheck = CAST(0
AS BIT) ORDER BY Guid ASC

         -- ¿©±â¼­ ÇØ´ç »ç¿ëÀÚÀÇ °æÇ°À¸·Î ÁöÁ¤ÇØ ÁØ´Ù.
         UPDATE
 T_DL_OFFLINE_GIFT SET Server = @ServerCode, AccountID = @AccountID,
CharName = @CharName, Date_Reg = GetDate(), RegCheck = CAST(1 AS BIT)
WHERE Guid = @iGIFT_GUID

         SELECT @iGIFT_NAME = GiftName FROM T_DL_OFFLINE_GIFTNAME WHERE GiftKind = @iGIFT_KIND

         SELECT 1 As ResultCode, @iGIFT_NAME As GiftName
      END
      ELSE
      BEGIN
         -- ¿À´Ã ÁÙ ¼ö ÀÖ´Â °æÇ°ÀÌ Çϳªµµ ¾ø´Ù.
         SELECT 0 As ResultCode, '' As GiftName
      END
   END




   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION
   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF
End


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO








--//************************************************************************
--// ³»  ¿ë : ¹Â Ä£±¸À̺¥Æ®
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2004.02.11
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************


CREATE PROCEDURE   SP_REG_FRIEND_STONE
   @AccountID      varchar(10),   -- °èÁ¤¸í
   @ServerCode      smallint,   -- ¼­¹öÄÚµå
   @CharName      varchar(10)   -- Äɸ¯¸í
As
Begin
   BEGIN TRANSACTION

   SET NOCOUNT ON


   -- 1 . ÇØ´ç °èÁ¤ÀÇ ½ºÅæ°³¼ö¸¦ Áõ°¡½ÃÅ´
   IF
 NOT EXISTS (SELECT FriendShipStoneCount FROM T_FRIENDSHIP_STONE WHERE
AccountID = @AccountID and ServerCode = @ServerCode and CharName =
@CharName)
   BEGIN
      INSERT T_FRIENDSHIP_STONE (AccountID, ServerCode, CharName, FriendShipStoneCount) VALUES (@AccountID, @ServerCode, @CharName, 1)
   END
   ELSE
   BEGIN
      UPDATE T_FRIENDSHIP_STONE
      SET FriendShipStoneCount = FriendShipStoneCount  1
      WHERE AccountID = @AccountID and ServerCode = @ServerCode and CharName = @CharName
   END


   -- 2 . ¼­¹öº° ½ºÅæ°³¼ö Áõ°¡
   IF NOT EXISTS (SELECT FriendShipStoneCount FROM T_FRIENDSHIP_SERVERRANK WHERE ServerCode = @ServerCode)
   BEGIN
      INSERT T_FRIENDSHIP_SERVERRANK VALUES (@ServerCode, 1)
   END
   ELSE
   BEGIN
      UPDATE T_FRIENDSHIP_SERVERRANK
      SET FriendShipStoneCount = FriendShipStoneCount  1
      WHERE ServerCode = @ServerCode
   END


   SELECT
 FriendShipStoneCount FROM T_FRIENDSHIP_STONE WHERE  AccountID =
@AccountID and ServerCode = @ServerCode and CharName = @CharName

   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION

   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF
End


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


--//************************************************************************
--// ³»  ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.12.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************



CREATE PROCEDURE   SP_REG_RINGGIFT
   @AccountID      varchar(10),   -- °èÁ¤¸í
   @GiftSection      tinyint      -- »óÇ°ÀÇ Á¾·ù (0:¿ë»çÀǹÝÁöµå·Ó, 1:¿ÀÅ©Á×ÀÓ)

As
Begin
   BEGIN TRANSACTION

   SET NOCOUNT ON

   DECLARE   @ToDay      DATETIME
   DECLARE   @strToDayStart      VARCHAR(32)
   DECLARE   @strToDayEnd      VARCHAR(32)
   DECLARE   @iToDayRegCount   INT
   DECLARE   @iMaxAllGiftNum   INT      -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¸ðµç °³¼ö
   DECLARE   @iMaxTodayGiftNum   INT      -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¿À´ÃÀÇ °³¼ö


   DECLARE   @iLeftGifts      INT      -- ³²Àº »óÇ° °³¼ö
   DECLARE   @iGUID      INT      -- GUID
   DECLARE   @iGiftKind      INT      -- ´ç÷µÈ »óÇ°¹øÈ£


   IF @GiftSection = 0
   BEGIN
      SET @iMaxAllGiftNum = 80      
      SET @iMaxTodayGiftNum = 2      
   END   
   ELSE
   BEGIN
      SET @iMaxAllGiftNum = 160      
      SET @iMaxTodayGiftNum = 5      
   END


   -- 1> . ÀÌÁ¦ ÇØ´ç °æÇ°Á¾·ùÀÇ ³²Àº °æÇ°ÀÌ ÀÖ´ÂÁö È®ÀÎÇÑ´Ù.
   SELECT @iLeftGifts = COUNT(*) FROM T_RingEvent_OfflineGift WHERE RegisterDate IS NULL AND GiftSection = @GiftSection


   IF @iLeftGifts = 0
   BEGIN
      SELECT 3 As RegResult, @AccountID, 0 As GiftKind   -- ÀÌÁ¦ ´õ ÀÌ»ó µî·ÏÇÒ ¼ö ¾ø´Ù¸é ³ª°£´Ù.
   END
   ELSE
   BEGIN
      -- 2> . ¿À´Ã µî·ÏÇÒ ¼ö Àִ ȸ¼ö ÀÌ»óÀ¸·Î µî·Ï µÇ¾ú´ÂÁö üũ
      SET @ToDay = GetDate()

      SET
 @strToDayStart = CONVERT(char(4), YEAR(@ToDay))  '-' 
CONVERT(char(2), MONTH(@ToDay))  '-'  CONVERT(char(2), DAY(@ToDay))  '
 00:00:00'
      SET @strToDayEnd =
CONVERT(char(4), YEAR(@ToDay))  '-'  CONVERT(char(2), MONTH(@ToDay)) 
 '-'  CONVERT(char(2), DAY(@ToDay))  ' 23:59:59'

      SELECT @iToDayRegCount = COUNT(*)
      FROM T_RingEvent_OfflineGift
      WHERE RegisterDate between @strToDayStart and @strToDayEnd
         and @GiftSection = GiftSection

      IF @iToDayRegCount >= @iMaxTodayGiftNum      -- ¿À´ÃÀÇ ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
      BEGIN
         SELECT 3 As RegResult, @AccountID, 0 As GiftKind
      END
      ELSE
      BEGIN
         -- 3> . ÀÌ¹Ì µî·ÏµÈ °èÁ¤ÀÎÁö È®ÀÎÇÑ´Ù.
         IF EXISTS ( SELECT * FROM T_RingEvent_OfflineGift  WITH (READUNCOMMITTED)
                  WHERE AccountID = @AccountID)
         BEGIN   
            -- ÀÌ¹Ì µî·ÏµÈ »ç¶÷À̹ǷΠ±×»ç¶÷ÀÇ °èÁ¤¸í Á¤µµ¸¸ º¸³½´Ù.
            SELECT 2 As RegResult, @AccountID, 0 As GiftKind
         END
         ELSE
         BEGIN
            SELECT TOP 1 @iGUID = GUID, @iGiftKind = GiftKind
            FROM T_RingEvent_OfflineGift
            WHERE @GiftSection = GiftSection and RegisterDate IS NULL
            ORDER BY GUID


            UPDATE T_RingEvent_OfflineGift
            SET AccountID=@AccountID, RegisterDate=GetDate()
            WHERE @iGUID = GUID

            -- »õ·Î µî·ÏÇÏ´Â »ç¶÷ÀÌ¸ç µî·Ï ¼º°ø
            SELECT 1 As RegResult, @AccountID, @iGiftKind As GiftKind
         END

      END
   END


   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION

   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO






-- ÀúÀå ÇÁ·Î½ÃÀú »ý¼º


--//************************************************************************
--// ³»  ¿ë : ¹Â ¹ÝÁöÀ̺¥Æ® °æÇ°µî·Ï
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.12.05
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************


CREATE PROCEDURE   SP_REG_RINGGIFT_TEMP
   @AccountID      varchar(10),   -- °èÁ¤¸í
   @GiftKind      tinyint      -- »óÇ°ÀÇ Á¾·ù (0:¿ë»çÀǹÝÁöµå·Ó, 1:¿ÀÅ©Á×ÀÓ)
As
Begin
   BEGIN TRANSACTION

   SET NOCOUNT ON


   DECLARE   @ToDay      DATETIME
   DECLARE   @strToDayStart      VARCHAR(32)
   DECLARE   @strToDayEnd      VARCHAR(32)
   DECLARE   @iToDayRegCount   INT
   DECLARE   @iMaxAllGiftNum   INT      -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¸ðµç °³¼ö
   DECLARE   @iMaxTodayGiftNum   INT      -- ÇØ´ç ¹øÈ£ »óÇ°ÀÇ ¿À´ÃÀÇ °³¼ö


   IF @GiftKind = 0
   BEGIN
      SET @iMaxAllGiftNum = 80      
      SET @iMaxTodayGiftNum = 2      
   END   
   ELSE
   BEGIN
      SET @iMaxAllGiftNum = 160      
      SET @iMaxTodayGiftNum = 3      
   END


   SELECT @iToDayRegCount = COUNT(*)
   FROM T_RingAttackEvent_Gift
   WHERE @GiftKind = GiftKind


   IF @iToDayRegCount >= @iMaxAllGiftNum      -- Àüü ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
   BEGIN
      SELECT 3 As RegResult, @AccountID
   END

   ELSE
   BEGIN
      SET @ToDay = GetDate()

      SET @strToDayStart =
CONVERT(char(4), YEAR(@ToDay))  '-'  CONVERT(char(2), MONTH(@ToDay)) 
 '-'  CONVERT(char(2), DAY(@ToDay))  ' 00:00:00'
      SET @strToDayEnd =
CONVERT(char(4), YEAR(@ToDay))  '-'  CONVERT(char(2), MONTH(@ToDay)) 
 '-'  CONVERT(char(2), DAY(@ToDay))  ' 23:59:59'

      SELECT @iToDayRegCount = COUNT(*)
      FROM T_RingAttackEvent_Gift
      WHERE RegisterDate between @strToDayStart and @strToDayEnd
         and @GiftKind = GiftKind

      IF @iToDayRegCount >= @iMaxTodayGiftNum   -- ¿À´ÃÀÇ ÃÖ´ë »óÇ° µî·Ï ¼ö¸¦ ÃÊ°úÇÏ¸é ³ª°£´Ù.
      BEGIN
         SELECT 3 As RegResult, @AccountID
      END
      ELSE

      BEGIN
         IF EXISTS ( SELECT * FROM T_RingAttackEvent_Gift  WITH (READUNCOMMITTED)
                  WHERE AccountID = @AccountID)
         BEGIN   
            -- ÀÌ¹Ì µî·ÏµÈ »ç¶÷À̹ǷΠ±×»ç¶÷ÀÇ °èÁ¤¸í Á¤µµ¸¸ º¸³½´Ù.
            SELECT 2 As RegResult, @AccountID
         END
         ELSE
         BEGIN
            INSERT INTO T_RingAttackEvent_Gift
            VALUES (@AccountID, @GiftKind, default)

            -- »õ·Î µî·ÏÇÏ´Â »ç¶÷ÀÌ¸ç µî·Ï ¼º°ø
            SELECT 1 As RegResult, @AccountID
         END
      END
   END



   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION
   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO






--//************************************************************************
--// ³»  ¿ë : ¹Â »ó¿ëÈ­ 2Áֳ⠱â³ä º¹±Ç À̺¥Æ® °ü·Ã - ½Ã¸®¾ó µî·Ï
--// ºÎ  ¼­ : °ÔÀÓ°³¹ßÆÀ
--// ¸¸µéÀÏ : 2003.10.20
--// ¸¸µéÀÌ : ÁØÀÏ
--//
--//************************************************************************


CREATE PROCEDURE   SP_REG_SERIAL
   @AccountID      varchar(10),   -- °èÁ¤¸í
   @MembGUID      int,      -- GUID
   @SERIAL1      varchar(4),   -- ½Ã¸®¾ó1
   @SERIAL2      varchar(4),   -- ½Ã¸®¾ó2
   @SERIAL3      varchar(4)   -- ½Ã¸®¾ó3
As
Begin
   BEGIN TRANSACTION

   SET NOCOUNT ON   


   -- 1 . T_RegCount_Check Å×ÀÌºí¿¡¼­ ÇØ´ç °èÁ¤ÀÌ ÀÌ¹Ì µî·ÏÇß´ÂÁö, Ä«¿îÆ®¸¦ ³Ñ¾ú´ÂÁö üũÇÑ´Ù.
   DECLARE @MAX_REGCOUNT   INT
   DECLARE @iREG_COUNT   INT
   DECLARE @iREG_ALREADY   BIT


   SET @MAX_REGCOUNT    = 20
   SET @iREG_ALREADY      = 0

   IF EXISTS ( SELECT * FROM T_RegCount_Check  WITH (READUNCOMMITTED)
            WHERE AccountID = @AccountID)
   BEGIN   
      -- T_RegCount_Check ¿¡´Â ÀÌ¹Ì °èÁ¤¸íÀÌ ÀÖÀ» °ÍÀ̹ǷΠº°µµÀÇ °ËÁõ¾øÀÌ µî·Ï¿©ºÎ¸¦ º¯°æÇÑ´Ù.
      SELECT @iREG_ALREADY = RegAlready FROM T_RegCount_Check WHERE AccountID = @AccountID

/*      IF (@iREG_ALREADY = 1 )
      BEGIN
         SELECT 5 As RegResult, 0 As F_Register_Section
      END
      ELSE
      BEGIN
*/         SELECT @iREG_COUNT = RegCount

         FROM T_RegCount_Check
         WHERE AccountID = @AccountID

         IF (@iREG_COUNT >= @MAX_REGCOUNT)
         BEGIN
            SET @iREG_ALREADY = 1


            SELECT 2 As RegResult, 0 As F_Register_Section
         END
         ELSE
         BEGIN
            UPDATE T_RegCount_Check
            SET RegCount = RegCount  1
            WHERE AccountID = @AccountID
         END
--      END

   END
   ELSE
   BEGIN
      INSERT INTO T_RegCount_Check
      VALUES (@AccountID, default, default)
   END




/*   IF (@iREG_ALREADY =1)
   BEGIN
      IF(@@Error <> 0 )
         ROLLBACK TRANSACTION
      ELSE   
         COMMIT TRANSACTION

      SET NOCOUNT OFF   


      RETURN
   END
*/   


   -- 2 . T_Serial_Bank Å×ÀÌºí¿¡ ½Ã¸®¾óÀ» µî·ÏÇÑ´Ù.
   DECLARE @REG_CHECK   BIT

   IF EXISTS ( SELECT F_RegisterCheck FROM T_Serial_Bank  WITH (READUNCOMMITTED)
      WHERE  P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3)
   BEGIN   
      SELECT @REG_CHECK = F_RegisterCheck FROM T_Serial_Bank  WITH (READUNCOMMITTED)
      WHERE  P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3


      IF (@REG_CHECK = 0)
      BEGIN
         UPDATE T_Serial_Bank
         SET F_Member_Guid = @MembGUID, F_Member_Id = @AccountID, F_RegisterCheck = 1, F_Register_Date = GetDate()
         WHERE  P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3


         -- T_RegCount_Check ¿¡´Â ÀÌ¹Ì °èÁ¤¸íÀÌ ÀÖÀ» °ÍÀ̹ǷΠº°µµÀÇ °ËÁõ¾øÀÌ µî·Ï¿©ºÎ¸¦ º¯°æÇÑ´Ù.
         UPDATE T_RegCount_Check
         SET RegAlready = 1
         WHERE AccountID = @AccountID

         SELECT 0 As RegResult, F_Register_Section
         FROM T_Serial_Bank
         WHERE  P_Serial_1 = @SERIAL1 and P_Serial_2 = @SERIAL2 and P_Serial_3 = @SERIAL3
      END
      ELSE
      BEGIN   -- ÀÌ¹Ì µî·ÏµÇ¾ú´Ù.
         SELECT 1 As RegResult, 0 As F_Register_Section
      END


   END
   ELSE
   BEGIN   -- ÇØ´ç ½Ã¸®¾óÀº Á¸ÀçÇÏÁö ¾Ê´Â´Ù.
      SELECT 3 As RegResult, 0  As F_Register_Section
   END



   IF(@@Error <> 0 )
      ROLLBACK TRANSACTION
   ELSE   
      COMMIT TRANSACTION

   SET NOCOUNT OFF   
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROCEDURE ShowResourceInfo
    AS
    SET NOCOUNT ON
    print '*********************************'
    print ' 5ÃÊ°£ »ç¿ëµÈ ½Ã½ºÅÛ ÀÚ¿ø·®ÀÔ´Ï´Ù.'
    print '*********************************'
    SELECT spid, cpu, physical_io
    INTO #Temp1 FROM master..sysprocesses
    WAITFOR DELAY '0:00:05' 
    SELECT P.spid, P.cpu-T.cpu AS 'cpu »ç¿ë·®(ms)',
    P.physical_io-T.physical_io AS 'Disk R/W·®(Page)',
    P.nt_username ' NT »ç¿ëÀÚ¸í', P.program_name 'ÀÀ¿ëÇÁ·Î±×·¥',
    P.hostname, P.cmd
    FROM master..sysprocesses P, #Temp1 T
    WHERE P.spid=T.spid
    AND P.cpu-T.cpu P.physical_io-T.physical_io P.memusage > 0
    DROP TABLE #Temp1
    Return





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROC UP_EMP6 @JOB VARCHAR(4), @YEAR CHAR(4), @SAL BIGINT
AS
   SELECT * FROM EMP
      WHERE JOB = @JOB AND DATEPART(YY,EDATE) = @YEAR
         AND SAL > @SAL




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ATENCION! No es de mi autoria.

Código:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DupesDetected]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DupesDetected]
GO
CREATE TABLE [dbo].[DupesDetected] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ITEM_TYPE] [binary] (1) NOT NULL ,
[ITEM_SERIAL] [binary] (4) NOT NULL ,
[INV_POSITION] [smallint] NOT NULL ,
[WH_POSITION] [smallint] NOT NULL ,
[TIMESTAMP] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DupesDetected] WITH NOCHECK ADD
CONSTRAINT [PK_DupesDetected] PRIMARY KEY  CLUSTERED
(
[ID]
)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[DupesDetected] ADD
CONSTRAINT [DF_Table1_TIMESTAMP] DEFAULT (getdate()) FOR [TIMESTAMP]
GO
CREATE TRIGGER [dbo].[DUPEFINDER] ON [dbo].[character]
FOR UPDATE
AS
IF UPDATE(inventory)
BEGIN
SET NOCOUNT ON
DECLARE
@CHARNAME varchar(10),
@ACCOUNT_ID varchar(10),
@INV binary(760),
@ITEM_TYPE binary(1),
@ITEM_SERIAL binary(4),
@WAREHOUSE binary(1200),
@j smallint,
@k smallint,
@PWOLD smallint,
@PWNEW smallint
--TOMAMOS EL INVENTARIO Y LOS DATOS DEL USUARIO
SELECT @ACCOUNT_ID=accountid,@CHARNAME=Name,@INV=inventory FROM inserted
SET @j=0
--ITEM POR ITEM
WHILE (@j<76) AND (@INV IS NOT NULL)
BEGIN
--TOMAMOS EL TIPO Y EL SERIAL DEL ITEM
SET @ITEM_TYPE=SUBSTRING(@INV,@j*10 1,1)
SET @ITEM_SERIAL=SUBSTRING(@INV,@j*10 4,4)
--SI EL TIPO ES VÁLIDO Y EL SERIAL NO ES "0"
IF @ITEM_TYPE<>0xFF AND @ITEM_SERIAL<>0x00000000
BEGIN
--SI NO TENEMOS EL WAREHOUSE DATA LO OBTENEMOS (ESTO SUCEDERÁ UNA SOLA VEZ)
IF (@WAREHOUSE IS NULL)
BEGIN
SELECT @WAREHOUSE=items, @PWOLD=pw FROM warehouse WHERE accountid=@ACCOUNT_ID
END
--POR CADA SLOT EN EL BAULT
SET @k=0
WHILE (@k<120) AND (@WAREHOUSE IS NOT NULL)
BEGIN
--SI EL TIPO Y SERIAL COINCIDEN ENTONCES
IF (SUBSTRING(@WAREHOUSE,@k*10 1,1)=@ITEM_TYPE) AND (SUBSTRING(@WAREHOUSE,@k*10 4,4)=@ITEM_SERIAL)
BEGIN
--GUARDAMOS EL ITEM EN EL LOG
INSERT INTO DupesDetected (AccountID,Name,ITEM_TYPE,ITEM_SERIAL,INV_POSITION,WH_POSITION)
VALUES (@ACCOUNT_ID,@CHARNAME,@ITEM_TYPE,@ITEM_SERIAL,@j,@k)
--DESCONECTAMOS AL USUARIO
UPDATE MEMB_STAT SET CONNECTSTAT=0 WHERE memb___id=@CHARNAME
--BLOQUEAMOS EL BAULT
--OBTENEMOS EL NUEVO LOCK CODE
SET @PWNEW = DATEPART(YYYY,GETDATE())  DATEPART(MM,GETDATE())  DATEPART(D,GETDATE())
--BLOQUEAMOS EL BAULT
UPDATE warehouse SET pw=3244 WHERE ACCOUNTID=@ACCOUNT_ID
--BANEAMOS TODOS LOS PJS DE LA CUENTA
UPDATE Character SET CtlCode=1 WHERE AccountID=@ACCOUNT_ID

END
--AVANZAMOS UNO EN EL CONTADOR
SET @k=@k 1
END
END
SET @j=@j 1
END
SET NOCOUNT ON
END

CREDITOS: Recopilacion de otros foros, post propio.

Casi todas estan testeadas por mi, las uso en mi servidor, igualmente varia según las versiones de db, pero viendo el codigo de error te das cuenta que es lo que no esta funcionando. Igualmente, cualquier inconveniente estoy a disposición.

Saludos.