Pangya Season 4.9 Server Files and Database

**Tsukasa** · 04-12-10

Originally Posted by tulang3587

@Tsukasa
About your bootstrapper query... Everytime I run the query, I always got "Incorrect syntax near 'LOGIN'."
I used MSSQL 2005 btw.

I'm using MSSQL 2008, so maybe there have been some subtle changes to what's acceptable. I'll look into it.

Originally Posted by ragezonersonexa

@Tsukasa

If not bothered, could you please post a copy of its databases, for which I am not able to login here.

If you registered and activated the account through ini3_py_account's stored procedures SP_PANGYA_REGIST and SP_PANGYA_ACTIVATE and it shows up in the database you might simply have to type the command "alluseropen" into the login server window.

--

The query hidden in the spoiler will update your vanilla database to the latest version in terms of tables and procedures. It's a collection of everything posted so far... If any of the procedures or tables already exist in your setup the query will simply notify you, skip the item in question and go on.

Attention: For some reason the procedure [dbo].[USP_CHAR_EQUIP_LOAD_S4] won't be created. Simply copy the [dbo].[USP_CHAR_EQUIP_LOAD] procedure, that should do the trick.

Spoiler:

Code:

USE [Pangya_S4_TH]
GO

/***************************************************
 * Pangya Server 4.9 Database Fixes                *
 *                                                 *
 * Last Update: 2010-12-04                         *
 *                                                 *
 * Run this after bootstrapping the database and   *
 * creating the tables through the .sql files from *
 * the server-package.                             *
 *                                                 *
 * The following procedures were added/modified:   *
 * =============================================== *
 * USP_NICKNAME_UPDATE by darkknight/advance       *
 * USP_TUTORIAL_GET_STATUS by bubbastic            *
 * USP_CARD_LIST by bubbastic                      *
 * USP_CHAR_EQUIP_LOAD_S4 by bubbastic             *
 * USP_CARD_GET_LIST by bubbastic                  *
 * Create table TD_CHAR_EQUIP_S4 by bubbastic      *
 * USP_CHAR_EQUIP_SAVE_S4 by bubbastic             *
 * USP_CHAR_EQUIP_UCC_S4 by tsukasa                *
 ***************************************************/

/****** Object:  StoredProcedure [dbo].[USP_NICKNAME_UPDATE]    by darkknight/advance ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_NICKNAME_UPDATE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_NICKNAME_UPDATE_SQL VARCHAR(8000)
	
	SET @USP_NICKNAME_UPDATE_SQL = N'	
	CREATE PROCEDURE [dbo].[USP_NICKNAME_UPDATE]
	@IDX int,
	@NICKNAME varchar(20)
	AS
	BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
		SET NOCOUNT ON;

		SELECT 0
	END'
	
	EXEC(@USP_NICKNAME_UPDATE_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] already exists and will not be modified.'

/****** Object:  StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS]    by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_TUTORIAL_GET_STATUS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_TUTORIAL_GET_STATUS_SQL VARCHAR(8000)
	
	SET @USP_TUTORIAL_GET_STATUS_SQL = N'
	CREATE PROCEDURE [dbo].[USP_TUTORIAL_GET_STATUS] (  
	@UID	INT
    )
    AS  
    BEGIN
		SET NOCOUNT ON  

		SELECT	 A.USER_LEVEL  
				,[QUEST] = SUM(A.STEP)  
		FROM	DBO.TA_TUTORIAL_ACC A  WITH (NOLOCK)
				INNER JOIN  
				(
					SELECT	SEQ  
					FROM	DBO.TD_TUTORIAL WITH (NOLOCK)   
					WHERE	UID = @UID  
					GROUP BY SEQ 
				) B  
		ON		A.SEQ = B.SEQ  
		GROUP BY A.USER_LEVEL
	END'
	
	EXEC(@USP_TUTORIAL_GET_STATUS_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] already exists and will not be modified.'

/****** Object:  StoredProcedure [dbo].[USP_CARD_LIST]    by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CARD_LIST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_CARD_LIST_SQL VARCHAR(8000)
	
	SET @USP_CARD_LIST_SQL = N'
	CREATE PROC [dbo].[USP_CARD_LIST] (  
        @UID    INT  
	)
	AS
	BEGIN  
		SET NOCOUNT ON  
  
		SELECT    UID  
			      , CARD_TYPEID  
				  , CARD_ITEMID  
				  , CARD_TYPE  
				  , QTY
				  , IS_USE_DT = 0
		FROM    DBO.TD_CARD_USRS WITH (NOLOCK)  
		WHERE   UID =  @UID   
		AND     USE_YN = ''Y''
	END'
	
	EXEC(@USP_CARD_LIST_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_LIST] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_LIST] already exists and will not be modified.'

/****** Object:  StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4]    by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_LOAD_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_CHAR_EQUIP_LOAD_S4_SQL VARCHAR(8000)
	
	SET @USP_CARD_LIST_SQL = N'
	CREATE PROC [dbo].[USP_CHAR_EQUIP_LOAD_S4] (     
	@UID INT    
	, @TYPEID INT = 0    
	)     
	AS    
  
	BEGIN     
	SET NOCOUNT ON     
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT    
		X.*    
		, UCCIDX = Y.UCCIDX    
		, SEQ = Y.SEQ    
		, ITEMID = Y.ITEMID  
	FROM     
		(SELECT    
			*  
		FROM     
			(    
			SELECT    
				A.UID    
				, A.ITEM_ID    
				, A.TYPEID    
				, EQUIP_NUM = CASE    
								WHEN B.SEQ = 1 THEN 1    
								WHEN B.SEQ = 2 THEN 2    
								WHEN B.SEQ = 3 THEN 3    
								WHEN B.SEQ = 4 THEN 4    
								WHEN B.SEQ = 5 THEN 5    
								WHEN B.SEQ = 6 THEN 6    
								WHEN B.SEQ = 7 THEN 7    
								WHEN B.SEQ = 8 THEN 8    
								WHEN B.SEQ = 9 THEN 9    
								WHEN B.SEQ = 10 THEN 10    
								WHEN B.SEQ = 11 THEN 11    
								WHEN B.SEQ = 12 THEN 12    
								WHEN B.SEQ = 13 THEN 13    
								WHEN B.SEQ = 14 THEN 14    
								WHEN B.SEQ = 15 THEN 15    
								WHEN B.SEQ = 16 THEN 16    
								WHEN B.SEQ = 17 THEN 17    
								WHEN B.SEQ = 18 THEN 18    
								WHEN B.SEQ = 19 THEN 19    
								WHEN B.SEQ = 20 THEN 20    
								WHEN B.SEQ = 21 THEN 21    
								WHEN B.SEQ = 22 THEN 22    
								WHEN B.SEQ = 23 THEN 23    
								WHEN B.SEQ = 24 THEN 24    
								WHEN B.SEQ = 25 THEN 25    
								WHEN B.SEQ = 26 THEN 26    
								WHEN B.SEQ = 27 THEN 27    
								WHEN B.SEQ = 28 THEN 28    
								WHEN B.SEQ = 29 THEN 29    
								WHEN B.SEQ = 30 THEN 30    
								WHEN B.SEQ = 31 THEN 31    
								WHEN B.SEQ = 32 THEN 32    
								WHEN B.SEQ = 33 THEN 33    
								WHEN B.SEQ = 34 THEN 34    
								WHEN B.SEQ = 35 THEN 35    
								WHEN B.SEQ = 36 THEN 36    
								WHEN B.SEQ = 37 THEN 37    
								WHEN B.SEQ = 38 THEN 38          
								ELSE NULL END    
				, VALUE = CASE    
								WHEN B.SEQ = 1 THEN A.PARTS_1    
								WHEN B.SEQ = 2 THEN A.PARTS_2    
								WHEN B.SEQ = 3 THEN A.PARTS_3    
								WHEN B.SEQ = 4 THEN A.PARTS_4    
								WHEN B.SEQ = 5 THEN A.PARTS_5    
								WHEN B.SEQ = 6 THEN A.PARTS_6    
								WHEN B.SEQ = 7 THEN A.PARTS_7    
								WHEN B.SEQ = 8 THEN A.PARTS_8    
								WHEN B.SEQ = 9 THEN A.PARTS_9    
								WHEN B.SEQ = 10 THEN A.PARTS_10    
								WHEN B.SEQ = 11 THEN A.PARTS_11    
								WHEN B.SEQ = 12 THEN A.PARTS_12    
								WHEN B.SEQ = 13 THEN A.PARTS_13    
								WHEN B.SEQ = 14 THEN A.PARTS_14    
								WHEN B.SEQ = 15 THEN A.PARTS_15    
								WHEN B.SEQ = 16 THEN A.PARTS_16    
								WHEN B.SEQ = 17 THEN A.PARTS_17    
								WHEN B.SEQ = 18 THEN A.PARTS_18    
								WHEN B.SEQ = 19 THEN A.PARTS_19    
								WHEN B.SEQ = 20 THEN A.PARTS_20    
								WHEN B.SEQ = 21 THEN A.PARTS_21    
								WHEN B.SEQ = 22 THEN A.PARTS_22    
								WHEN B.SEQ = 23 THEN A.PARTS_23    
								WHEN B.SEQ = 24 THEN A.PARTS_24    
								WHEN B.SEQ = 25 THEN A.AUXPARTS_1    
								WHEN B.SEQ = 26 THEN A.AUXPARTS_2    
								WHEN B.SEQ = 27 THEN A.AUXPARTS_3    
								WHEN B.SEQ = 28 THEN A.AUXPARTS_4    
								WHEN B.SEQ = 29 THEN A.AUXPARTS_5    
								WHEN B.SEQ = 30 THEN A.DEFAULT_HAIR    
								WHEN B.SEQ = 31 THEN A.DEFAULT_SHIRTS    
								WHEN B.SEQ = 32 THEN A.GIFT_FLAG    
								WHEN B.SEQ = 33 THEN A.PCL0    
								WHEN B.SEQ = 34 THEN A.PCL1    
								WHEN B.SEQ = 35 THEN A.PCL2    
								WHEN B.SEQ = 36 THEN A.PCL3    
								WHEN B.SEQ = 37 THEN A.PCL4    
								WHEN B.SEQ = 38 THEN A.PURCHASE    
								ELSE NULL END    
			FROM     
				DBO.PANGYA_CHARACTER_INFORMATION A (NOLOCK)        
			CROSS JOIN    
				(SELECT SEQ FROM TZ_DUMMY WHERE SEQ BETWEEN 1 AND 50) B    
			WHERE     
				A.UID = @UID    
				AND A.TYPEID = CASE WHEN (@TYPEID = 0) THEN A.TYPEID ELSE @TYPEID END    
			) C    
		WHERE    
			EQUIP_NUM IS NOT NULL    
		)X    
	LEFT OUTER JOIN    
		(  
		SELECT   
			B.UID  
			, A.TYPEID  
			, A.UCCIDX  
			, A.SEQ    
			, B.CHARACTER_ITEMID  
			, B.EQUIP_NUM  
			, B.EQUIP_TYPE  
			, B.ITEMID  

		FROM   
			(  
			SELECT UID, TYPEID, UCCIDX, SEQ, ITEM_ID--, CHARACTER_ITEM_ID, EQUIP_NUM, EQUIP_TYPE  
			FROM DBO.TU_UCC
			WHERE UID = @UID-- AND USE_YN = ''Y''  
			)  A  
		RIGHT OUTER  JOIN   
			(  
			SELECT UID, CHARACTER_ITEMID, EQUIP_NUM, EQUIP_TYPE, ITEMID  
			FROM DBO.TD_CHAR_EQUIP   
			WHERE UID = @UID AND USE_YN = ''Y''  
			) B   
		ON A.UID = B.UID   
			AND A.ITEM_ID = B.ITEMID   
			--AND A.EQUIP_NUM = B.EQUIP_NUM   
		 
		) Y         
	ON X.ITEM_ID = Y.CHARACTER_ITEMID    
		AND X.EQUIP_NUM = Y.EQUIP_NUM    
	ORDER BY X.ITEM_ID, X.EQUIP_NUM      
	 
	SET NOCOUNT OFF    
END'
	
	EXEC(@USP_CHAR_EQUIP_LOAD_S4_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4] created.'
END
ELSE
	PRINT 'NOTE: [dbo].[USP_CHAR_EQUIP_LOAD_S4] already exists and will not be modified.'

/****** Object:  StoredProcedure [dbo].[USP_CARD_GET_LIST]    by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CARD_GET_LIST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_CARD_GET_LIST_SQL VARCHAR(8000)
	
	SET @USP_CARD_GET_LIST_SQL = N'
	CREATE PROC [dbo].[USP_CARD_GET_LIST] (  
        @UID    INT  
	)
	AS  

	BEGIN  

    SET NOCOUNT ON  
  
    SELECT  CARD_TYPEID  
              , CARD_ITEMID  
              , QTY
              , IS_USE_DT = 0
    FROM    DBO.TD_CARD_USRS WITH (NOLOCK)  
    WHERE   UID =  @UID   
    AND     USE_YN = ''Y''
    
	END'
	
	EXEC(@USP_CARD_GET_LIST_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_GET_LIST] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_GET_LIST] already exists and will not be modified.'
	
/****** Object:  Table [dbo].[TD_CHAR_EQUIP_S4]    by bubbastic ******/
IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = N'TD_CHAR_EQUIP_S4')
BEGIN
	DECLARE @ADD_TD_CHAR_EQUIP_S4_SQL VARCHAR(8000)
	
	SET @ADD_TD_CHAR_EQUIP_S4_SQL = N'
    SET ANSI_PADDING ON;
    
    CREATE TABLE [dbo].[TD_CHAR_EQUIP_S4](
		[SEQ] [int] IDENTITY(1,1) NOT NULL,
		[UID] [int] NOT NULL,
		[CHAR_ITEMID] [int] NULL,
		[ITEMID] [bigint] NULL,
		[TYPEID] [int] NULL,
		[EQUIP_NUM] [tinyint] NULL,
		[UCC_IDX] [char](8) NULL,
		[USE_YN] [char](1) NULL
	) ON [PRIMARY];

	SET ANSI_PADDING OFF;'
	
	EXEC(@ADD_TD_CHAR_EQUIP_S4_SQL)
	PRINT 'NOTE: Table [dbo].[TD_CHAR_EQUIP_S4] created.'
END
ELSE
	PRINT 'NOTE: Table [dbo].[TD_CHAR_EQUIP_S4] already exists and will not be modified.'
		
/****** Object:  StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4]    by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_SAVE_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_CHAR_EQUIP_SAVE_S4_PROC_SQL VARCHAR(8000)
	
	SET @USP_CHAR_EQUIP_SAVE_S4_PROC_SQL = N'
	CREATE PROC [dbo].[USP_CHAR_EQUIP_SAVE_S4] (     
		 @UID INT    
		 , @CHAR_ITEM_ID	INT    
		 , @STR				VARCHAR(8000)    
		 , @DEBUG			TINYINT = 0
	)   
	AS    
  
	BEGIN     

-- ''1^123456|2|3^1243^2214^1''
-- @EQUIP_NUM^@TYPEID^@ITEMID^@UCCIDX^@SEQ|
-- @EQUIP_NUM^@TYPEID^@ITEMID^@UCCIDX^@SEQ    


	SET NOCOUNT ON     
  
	DECLARE @RET INT
	SET	@RET = 0
    
  
	--INPUT 분석(1차 파싱 결과)    
	IF OBJECT_ID(''TEMPDB..#TD_SRC'') IS NOT NULL     
	DROP TABLE #TD_SRC    
  
	CREATE TABLE #TD_SRC (
		IDX		SMALLINT	IDENTITY(1,1)     
		, DT	VARCHAR(1000)
	)     
  
  
	--INPUT 분석(2차 파싱) 및 RESULT를 저장     
	IF OBJECT_ID(''TEMPDB..#TD_CHAR'') IS NOT NULL     
	DROP TABLE #TD_CHAR     
  
	CREATE TABLE #TD_CHAR (    
			  IDX			SMALLINT IDENTITY(1,1) 
		    , UID			INT
			, CHAR_ITEMID	INT
			, EQUIP_NUM		INT				-- 캐릭터 파트 번호    
			, TYPEID		INT				-- 아이템    
			, ITEMID		INT				-- ITEMID   
			, UCCIDX		VARCHAR(8)		-- UCC인덱스    
			, SEQ			SMALLINT		-- UCC 시퀀스      
		 )    
  

	DECLARE	@SQL VARCHAR(8000)    
			, @STR_SEP CHAR(1)     
			, @INTLOOP INT     
	    
	SET @STR_SEP = ''|''    
	SET @SQL = ''''
	SET @INTLOOP = 1      
  

	IF CHARINDEX(''|'', @STR) > 0 BEGIN    
		SELECT @SQL= ''SELECT LTRIM(C) C FROM (SELECT ''''''    
					+ REPLACE(CONT,@STR_SEP,'''''' C UNION ALL '' + CHAR(13) + ''SELECT '''''') + '''''') T''    
		FROM (	SELECT CONT = @STR	) X    
	END ELSE BEGIN     
		SET @SQL = ''SELECT LTRIM(C) C FROM (SELECT '''''' + @STR  + '''''' C) T''    
	END     
  
	INSERT INTO #TD_SRC (DT)    
	EXEC(@SQL)     
 
 
	DECLARE @EQUIP_NUM	VARCHAR(20)    
			, @TYPEID	VARCHAR(20)    
			, @ITEMID	VARCHAR(20)  
			, @UCCIDX	VARCHAR(20)
			, @SEQ		VARCHAR(20)
	    
	  
	WHILE EXISTS( SELECT 1 FROM #TD_SRC WHERE IDX = @INTLOOP ) BEGIN     
		SELECT @SQL = REPLACE(DT, ''^'', '' ^'') FROM #TD_SRC WHERE IDX = @INTLOOP     

		EXEC	MASTER..XP_SSCANF @SQL, ''%s ^%s ^%s ^%s ^%s'',     
				@EQUIP_NUM OUTPUT, @TYPEID OUTPUT, @ITEMID OUTPUT, @UCCIDX OUTPUT, @SEQ OUTPUT    
	  
		INSERT INTO #TD_CHAR	(UID, CHAR_ITEMID, EQUIP_NUM, TYPEID, ITEMID, UCCIDX, SEQ)     
		SELECT  @UID
				, @CHAR_ITEM_ID   
				, EQUIP_NUM = CAST(@EQUIP_NUM AS INT)    
				, TYPEID	= CAST(@TYPEID AS INT)    
				, ITEMID	= CAST(@ITEMID AS INT)    
				, UCCIDX	= @UCCIDX    
				, SEQ		= CAST(@SEQ AS INT)    
	       
		SET @INTLOOP = @INTLOOP + 1     

	END    
	  
	CREATE CLUSTERED INDEX CIDX_TD_CHAR     
	ON #TD_CHAR (EQUIP_NUM)    



  

-- 캐릭터 장착 정보 UPDATE문 생성 => !!! AD-HOC 하나라도 더 줄이기!!! 

	BEGIN TRY

		BEGIN TRAN

		UPDATE	B
		SET
				  parts_1  = CASE WHEN	A.parts_1 > 0	THEN  A.parts_1 ELSE 0  END  
				, parts_2  = CASE WHEN	A.parts_2 > 0	THEN  A.parts_2 ELSE 0  END
				, parts_3  = CASE WHEN	A.parts_3 > 0	THEN  A.parts_3 ELSE 0  END
				, parts_4  = CASE WHEN	A.parts_4 > 0	THEN  A.parts_4 ELSE 0  END
				, parts_5  = CASE WHEN	A.parts_5 > 0	THEN  A.parts_5 ELSE 0  END
				, parts_6  = CASE WHEN	A.parts_6 > 0	THEN  A.parts_6 ELSE 0  END
				, parts_7  = CASE WHEN	A.parts_7 > 0	THEN  A.parts_7 ELSE 0  END 
				, parts_8  = CASE WHEN	A.parts_8 > 0	THEN  A.parts_8 ELSE 0  END  
				, parts_9  = CASE WHEN	A.parts_9 > 0	THEN  A.parts_9 ELSE 0  END
				, parts_10 = CASE WHEN	A.parts_10 > 0	THEN  A.parts_10 ELSE 0  END
				, parts_11 = CASE WHEN	A.parts_11 > 0	THEN  A.parts_11 ELSE 0  END
				, parts_12 = CASE WHEN	A.parts_12 > 0	THEN  A.parts_12 ELSE 0  END
				, parts_13 = CASE WHEN	A.parts_13 > 0	THEN  A.parts_13 ELSE 0  END 
				, parts_14 = CASE WHEN	A.parts_14 > 0	THEN  A.parts_14 ELSE 0  END
				, parts_15 = CASE WHEN	A.parts_15 > 0	THEN  A.parts_15 ELSE 0  END
				, parts_16 = CASE WHEN	A.parts_16 > 0	THEN  A.parts_16 ELSE 0  END
				, parts_17 = CASE WHEN	A.parts_17 > 0	THEN  A.parts_17 ELSE 0  END
				, parts_18 = CASE WHEN	A.parts_18 > 0	THEN  A.parts_18 ELSE 0  END 
				, parts_19 = CASE WHEN	A.parts_19 > 0	THEN  A.parts_19 ELSE 0  END  
				, parts_20 = CASE WHEN	A.parts_20 > 0	THEN  A.parts_20 ELSE 0  END  
				, parts_21 = CASE WHEN	A.parts_21 > 0	THEN  A.parts_21 ELSE 0  END
				, parts_22 = CASE WHEN	A.parts_22 > 0	THEN  A.parts_22 ELSE 0  END 
				, parts_23 = CASE WHEN	A.parts_23 > 0	THEN  A.parts_23 ELSE 0  END
				, parts_24 = CASE WHEN	A.parts_24 > 0	THEN  A.parts_24 ELSE 0  END

		FROM	
				( 
					SELECT	UID
							, CHAR_ITEMID = MAX(CHAR_ITEMID)	
							, parts_1  = MAX(CASE WHEN EQUIP_NUM =  1 THEN  TYPEID ELSE 0  END)  
							, parts_2  = MAX(CASE WHEN EQUIP_NUM =  2 THEN  TYPEID ELSE 0  END)  
							, parts_3  = MAX(CASE WHEN EQUIP_NUM =  3 THEN  TYPEID ELSE 0  END)  
							, parts_4  = MAX(CASE WHEN EQUIP_NUM =  4 THEN  TYPEID ELSE 0  END)  
							, parts_5  = MAX(CASE WHEN EQUIP_NUM =  5 THEN  TYPEID ELSE 0  END)  
							, parts_6  = MAX(CASE WHEN EQUIP_NUM =  6 THEN  TYPEID ELSE 0  END)  
							, parts_7  = MAX(CASE WHEN EQUIP_NUM =  7 THEN  TYPEID ELSE 0  END)  
							, parts_8  = MAX(CASE WHEN EQUIP_NUM =  8 THEN  TYPEID ELSE 0  END)  
							, parts_9  = MAX(CASE WHEN EQUIP_NUM =  9 THEN  TYPEID ELSE 0  END)  
							, parts_10 = MAX(CASE WHEN EQUIP_NUM = 10 THEN  TYPEID ELSE 0  END)  
							, parts_11 = MAX(CASE WHEN EQUIP_NUM = 11 THEN  TYPEID ELSE 0  END)  
							, parts_12 = MAX(CASE WHEN EQUIP_NUM = 12 THEN  TYPEID ELSE 0  END)  
							, parts_13 = MAX(CASE WHEN EQUIP_NUM = 13 THEN  TYPEID ELSE 0  END)    
							, parts_14 = MAX(CASE WHEN EQUIP_NUM = 14 THEN  TYPEID ELSE 0  END)   
							, parts_15 = MAX(CASE WHEN EQUIP_NUM = 15 THEN  TYPEID ELSE 0  END)   
							, parts_16 = MAX(CASE WHEN EQUIP_NUM = 16 THEN  TYPEID ELSE 0  END)    
							, parts_17 = MAX(CASE WHEN EQUIP_NUM = 17 THEN  TYPEID ELSE 0  END)   
							, parts_18 = MAX(CASE WHEN EQUIP_NUM = 18 THEN  TYPEID ELSE 0  END)    
							, parts_19 = MAX(CASE WHEN EQUIP_NUM = 19 THEN  TYPEID ELSE 0  END)   
							, parts_20 = MAX(CASE WHEN EQUIP_NUM = 20 THEN  TYPEID ELSE 0  END)   
							, parts_21 = MAX(CASE WHEN EQUIP_NUM = 21 THEN  TYPEID ELSE 0  END)  
							, parts_22 = MAX(CASE WHEN EQUIP_NUM = 22 THEN  TYPEID ELSE 0  END)   
							, parts_23 = MAX(CASE WHEN EQUIP_NUM = 23 THEN  TYPEID ELSE 0  END)   
							, parts_24 = MAX(CASE WHEN EQUIP_NUM = 24 THEN  TYPEID ELSE 0  END)  
					FROM	#TD_CHAR
					GROUP BY UID 
				) A
				INNER JOIN
				( 
					SELECT	*
					FROM	pangya_character_information WITH (NOLOCK) 
					WHERE	UID = @UID 
					AND		ITEM_ID = @CHAR_ITEM_ID
				) B
		ON		A.UID = B.UID
		AND		A.CHAR_ITEMID = B.ITEM_ID


		   

		  
		/*****************************************  

			카드 장착정보 ITEM 추가: SELECT * FROM TD_CARD_EQUIP  
			했다고 주석에 나와있지만 무슨소린지 아무도 모른다..
			뭘 어쨌다는건지....

			알게 되면 주석을 추가해 주세요 ㅡ.ㅡ

		******************************************/       

		UPDATE	DBO.TD_CHAR_EQUIP_S4
		SET		USE_YN = ''N''
		WHERE	UID = @UID	

		 
		INSERT INTO DBO.TD_CHAR_EQUIP_S4 ( UID, CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, UCC_IDX, USE_YN )    
			SELECT    
				@UID    
				, @CHAR_ITEM_ID  
				, CASE ITEMID WHEN 0 THEN 0 ELSE ITEMID END
				, TYPEID  
				, B.EQUIP_NUM
				, UCCIDX
				, ''Y''    
			FROM	(
						SELECT	*    
						FROM	#TD_CHAR    
						WHERE	ITEMID IS NOT NULL    
					) B    

	   
		 

		COMMIT TRAN
		SELECT [RET] = @@ERROR


		IF ( @DEBUG = 1 ) BEGIN
			SELECT	*    
			FROM	#TD_CHAR  
		END
    

	END TRY
	BEGIN CATCH

		ROLLBACK TRAN
		SELECT [RET] = 1

	END CATCH
 

END'
	
	EXEC(@USP_CHAR_EQUIP_SAVE_S4_PROC_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] already exists and will not be modified.'

/****** Object:  StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4]    by tsukasa ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_UCC_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DECLARE @USP_CHAR_EQUIP_UCC_S4_SQL VARCHAR(8000)
	
	SET @USP_CHAR_EQUIP_UCC_S4_SQL = N'
	CREATE PROC [dbo].[USP_CHAR_EQUIP_UCC_S4] (   
 @UID INT  
) -- WITH ENCRYPTION   
AS  
/***********************  
DESCRIPTION : GET CHARINFO OF USER EQUIP CHARACTER  
   유저가 장착한 캐릭터의 정보를 가져온다.  
   한국. 시즌4. 친구초대  
OBJECT NAME : USP_CHAR_USER_EQUIP  
CREATED BY : JAE WOONG LEE  
LAST UPDATE : 2008-02-04  
RETURN VALUES : SEQ(TINYINT 0:NONE)  
EXCUTE : EXEC DBO.USP_EVENT_SEQ_S 123, 1  
***********************/  
BEGIN   

	SET NOCOUNT ON 
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
   
 DECLARE @CHAR_TYPEID INT  
   
 SELECT  
  @CHAR_TYPEID = TYPEID  
 FROM  
  DBO.PANGYA_USER_EQUIP A (NOLOCK)  
 INNER JOIN  
  DBO.PANGYA_CHARACTER_INFORMATION B (NOLOCK)  
  ON A.UID = B.UID  
  AND A.CHARACTER_ID = B.ITEM_ID   
 WHERE  
  A.UID = @UID  
    
 IF (@CHAR_TYPEID IS NOT NULL) BEGIN   
  EXEC DBO.USP_CHAR_EQUIP_LOAD @UID, @CHAR_TYPEID  
 END  
   
 SET NOCOUNT OFF  
END'

	EXEC(@USP_CHAR_EQUIP_UCC_S4_SQL)
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4] created.'
END
ELSE
	PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4] already exists and will not be modified.'
	
GO

**ragezonersonexa** · 04-12-10

@Tsukasa

No problem, I have the 2008 too

**chiosin2** · 04-12-10

@Tsukasa
aww man you beat mo to it, yours is more organized than mine. LoL. why not just release it in .sql format?

Is anyone using Monk's DBs? are you getting Log in errors?

**darkknight** · 04-12-10

Oh man It missing

dbo.USP_CHAR_EQUIP_UCC_S4

2010-12-05 01:50:14 107 Pang-CB Server 1 [E_ODBC] ErrCode(42000) ErrMsg([Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.USP_CHAR_EQUIP_UCC_S4'.)
2010-12-05 01:50:14 107 Pang-CB Server 1 [F_QUERY] LastSuccQuery({ call dbo.USP_CHAR_EQUIP_LOAD_S4 (1) }), CurrQuery({ call dbo.USP_CHAR_EQUIP_UCC_S4 (1) })

**Tsukasa** · 04-12-10

Originally Posted by chiosin2

LoL. why not just release it in .sql format?

I think queries are easier to update and a bit more flexible. Wouldn't want to post the entire database every time someone gets something done.

Originally Posted by chiosin2

Is anyone using Monk's DBs? are you getting Log in errors?

Monks database works just as fine as the vanilla one from the server-package. The only differences are in the usernames and some minor edits methinks.

When running the INI3BILL_DB.SQL you will encounter a few problems but it's safe to ignore them.

I've updated my previous post, it now contains all methods (see the attention note) to log into a channel. There isn't much to see or do there in the current state, though.

**darkknight** · 04-12-10

Tsukasa you has missing dbo.USP_CHAR_EQUIP_UCC_S4 ?
I search in Pangya_S4_TH.sql not found .

**Tsukasa** · 04-12-10

Originally Posted by darkknight

Tsukasa you has missing dbo.USP_CHAR_EQUIP_UCC_S4 ?
I search in Pangya_S4_TH.sql not found .

Code:

USE [Pangya_S4_TH]
GO
/****** Object:  StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4]    Script Date: 12/04/2010 20:10:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROC [dbo].[USP_CHAR_EQUIP_UCC_S4] (   
 @UID INT  
) -- WITH ENCRYPTION   
AS  
/***********************  
DESCRIPTION : GET CHARINFO OF USER EQUIP CHARACTER  
   ??? ??? ???? ??? ????.  
   ??. ??4. ????  
OBJECT NAME : USP_CHAR_USER_EQUIP  
CREATED BY : JAE WOONG LEE  
LAST UPDATE : 2008-02-04  
RETURN VALUES : SEQ(TINYINT 0:NONE)  
EXCUTE : EXEC DBO.USP_EVENT_SEQ_S 123, 1  
***********************/  
BEGIN   

	SET NOCOUNT ON 
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
   
 DECLARE @CHAR_TYPEID INT  
   
 SELECT  
  @CHAR_TYPEID = TYPEID  
 FROM  
  DBO.PANGYA_USER_EQUIP A (NOLOCK)  
 INNER JOIN  
  DBO.PANGYA_CHARACTER_INFORMATION B (NOLOCK)  
  ON A.UID = B.UID  
  AND A.CHARACTER_ID = B.ITEM_ID   
 WHERE  
  A.UID = @UID  
    
 IF (@CHAR_TYPEID IS NOT NULL) BEGIN   
  EXEC DBO.USP_CHAR_EQUIP_LOAD @UID, @CHAR_TYPEID  
 END  
   
 SET NOCOUNT OFF  
END

Here you go, mate.

**darkknight** · 04-12-10

oh thankyou so much I can play in family mode
can't make room or quick mach
game has not lasted version

**chiosin2** · 04-12-10

@Tsukasa mind i ask did you make this?

**Tsukasa** · 04-12-10

Originally Posted by chiosin2

@Tsukasa mind i ask did you make this?

It seems that a lot of the procedures in S4 are just copies of "previous" methods already present in the database (just take a look at many other procedures previously posted in this thread, they can often be found all over the place in the database). So no, it's just a rebrand of another procedure that does the same but is called different.

Since we have no insight into the inner workings (i.e. there's a procedure missing that's calling another missing procedure...) we better hope that we won't be running out of spares anytime soon

**chiosin2** · 04-12-10

@Yup, exactly my thought. I'm not good at SQL but i do understand how it works, somewhat. but it will take some time to finish.

**TheLost** · 04-12-10

Hello, does anyone know which command does this:

http://i52.tinypic.com/2ytuqo3.jpg

The /notice does not do that

**darkknight** · 04-12-10

chiosin2 you can restore database
if this work for you - -
Pangya_S4_TH.rar

**ragezonersonexa** · 04-12-10

I managed to log in, most could not complete the tutorial.

For those not able to login, just open the server.ini and change the number of channels to 1, the GameServer will stay as it should.

Does anyone have a solution to the tutorial?

**chiosin2** · 04-12-10

@darkknight thanks!

Edit: nope it doesn't restore properly i get Error: 3241

Pangya Season 4.9 Server Files and Database

Thread Tools

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Re: Pangya Season 4.9 Server Files and Database

Advertisement