| HISORI |
27-02-2011 09:58 AM |
[Download] Procedures Inexistentes / FIX
Olá a todos !
Bom para um melhor entendimento e facil procura dos procedimentos que estão faltando em algumas tables, segue abaixo todas que estão disponiveis no momento:
Para quem não tem altere onde tiver ALTER para CREATE e execute.
USP_CARD_LIST
Código:
USE [Pangya_S4_TH]
GO
/****** StoredProcedure [dbo].[USP_CARD_LIST] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
CHAR_EQUIP_LOAD_S4 <<< |Query que resolve o Problema de não aparecer os canais do Servidor/Salas
Código:
USE [Pangya_S4_TH]
GO
/****** Objet : StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[USP_CHAR_EQUIP_LOAD_S4] (
@UID INT
)
AS
BEGIN
SET NOCOUNT ON
IF ( @UID > 0 ) BEGIN
SELECT X.ITEM_ID
, TYPEID
/* new */
, ITEMID_PARTS_1
, ITEMID_PARTS_2
, ITEMID_PARTS_3
, ITEMID_PARTS_4
, ITEMID_PARTS_5
, ITEMID_PARTS_6
, ITEMID_PARTS_7
, ITEMID_PARTS_8
, ITEMID_PARTS_9
, ITEMID_PARTS_10
, ITEMID_PARTS_11
, ITEMID_PARTS_12
, ITEMID_PARTS_13
, ITEMID_PARTS_14
, ITEMID_PARTS_15
, ITEMID_PARTS_16
, ITEMID_PARTS_17
, ITEMID_PARTS_18
, ITEMID_PARTS_19
, ITEMID_PARTS_20
, ITEMID_PARTS_21
, ITEMID_PARTS_22
, ITEMID_PARTS_23
, ITEMID_PARTS_24
/* new */
, PARTS_1
, PARTS_2
, PARTS_3
, PARTS_4
, PARTS_5
, PARTS_6
, PARTS_7
, PARTS_8
, PARTS_9
, PARTS_10
, PARTS_11
, PARTS_12
, PARTS_13
, PARTS_14
, PARTS_15
, PARTS_16
, PARTS_17
, PARTS_18
, PARTS_19
, PARTS_20
, PARTS_21
, PARTS_22
, PARTS_23
, PARTS_24
, AUXPARTS_1
, AUXPARTS_2
, AUXPARTS_3
, AUXPARTS_4
, AUXPARTS_5
, DEFAULT_HAIR
, DEFAULT_SHIRTS
, GIFT_FLAG
, PCL0
, PCL1
, PCL2
, PCL3
, PCL4
, PURCHASE
FROM (
SELECT
ITEM_ID
, TYPEID
, PARTS_1
, PARTS_2
, PARTS_3
, PARTS_4
, PARTS_5
, PARTS_6
, PARTS_7
, PARTS_8
, PARTS_9
, PARTS_10
, PARTS_11
, PARTS_12
, PARTS_13
, PARTS_14
, PARTS_15
, PARTS_16
, PARTS_17
, PARTS_18
, PARTS_19
, PARTS_20
, PARTS_21
, PARTS_22
, PARTS_23
, PARTS_24
, AUXPARTS_1
, AUXPARTS_2
, AUXPARTS_3
, AUXPARTS_4
, AUXPARTS_5
, DEFAULT_HAIR
, DEFAULT_SHIRTS
, GIFT_FLAG
, PCL0
, PCL1
, PCL2
, PCL3
, PCL4
, PURCHASE
FROM DBO.PANGYA_CHARACTER_INFORMATION WITH (NOLOCK)
WHERE UID = @UID
) X
INNER JOIN
(
SELECT
ITEM_ID
, ITEMID_PARTS_1 = MAX(CASE WHEN EQUIP_NUM = 1 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_2 = MAX(CASE WHEN EQUIP_NUM = 2 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_3 = MAX(CASE WHEN EQUIP_NUM = 3 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_4 = MAX(CASE WHEN EQUIP_NUM = 4 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_5 = MAX(CASE WHEN EQUIP_NUM = 5 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_6 = MAX(CASE WHEN EQUIP_NUM = 6 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_7 = MAX(CASE WHEN EQUIP_NUM = 7 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_8 = MAX(CASE WHEN EQUIP_NUM = 8 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_9 = MAX(CASE WHEN EQUIP_NUM = 9 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_10 = MAX(CASE WHEN EQUIP_NUM = 10 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_11 = MAX(CASE WHEN EQUIP_NUM = 11 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_12 = MAX(CASE WHEN EQUIP_NUM = 12 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_13 = MAX(CASE WHEN EQUIP_NUM = 13 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_14 = MAX(CASE WHEN EQUIP_NUM = 14 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_15 = MAX(CASE WHEN EQUIP_NUM = 15 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_16 = MAX(CASE WHEN EQUIP_NUM = 16 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_17 = MAX(CASE WHEN EQUIP_NUM = 17 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_18 = MAX(CASE WHEN EQUIP_NUM = 18 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_19 = MAX(CASE WHEN EQUIP_NUM = 19 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_20 = MAX(CASE WHEN EQUIP_NUM = 20 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_21 = MAX(CASE WHEN EQUIP_NUM = 21 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_22 = MAX(CASE WHEN EQUIP_NUM = 22 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_23 = MAX(CASE WHEN EQUIP_NUM = 23 THEN ITEMID ELSE 0 END)
, ITEMID_PARTS_24 = MAX(CASE WHEN EQUIP_NUM = 24 THEN ITEMID ELSE 0 END)
FROM (
SELECT
ITEM_ID
, TYPEID
, PARTS_1
, PARTS_2
, PARTS_3
, PARTS_4
, PARTS_5
, PARTS_6
, PARTS_7
, PARTS_8
, PARTS_9
, PARTS_10
, PARTS_11
, PARTS_12
, PARTS_13
, PARTS_14
, PARTS_15
, PARTS_16
, PARTS_17
, PARTS_18
, PARTS_19
, PARTS_20
, PARTS_21
, PARTS_22
, PARTS_23
, PARTS_24
FROM DBO.PANGYA_CHARACTER_INFORMATION WITH (NOLOCK)
WHERE UID = @UID
) A
LEFT OUTER JOIN
(
SELECT CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM
FROM TD_CHAR_EQUIP_S4 WITH (NOLOCK)
WHERE UID = @UID
--AND ITEMID > 0
AND USE_YN = 'Y'
) B
ON A.ITEM_ID = B.CHAR_ITEMID
GROUP BY ITEM_ID
) Y
ON X.ITEM_ID = Y.ITEM_ID
END
END
USP_TUTORIAL_GET_STATUS
Código:
USE [Pangya_S4_TH]
GO
/****** StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
USP_CARD_GET_LIST
Código:
USE [Pangya_S4_TH]
GO
/****** Objet : StoredProcedure [dbo].[USP_CARD_GET_LIST] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
USP_CHAR_EQUIP_UCC_S4
Código:
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[/QUOTE]
USP_CHAR_EQUIP_SAVE_S4
[QUOTE]USE [Pangya_S4_TH]
GO
/****** Objet : StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] Date de génération du script : 12/04/2010 00:48:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
USP_NICKNAME_UPDATE
Código:
USE [Pangya_S4_TH]
GO
/****** Object: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] Script Date: 11/30/2010 19:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_NICKNAME_UPDATE]
@x int,
@y varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select 0
END
GO
Query que Cria todos os FileGroups e Users Necessarios e as DataBases
Código:
USE [master]
GO
/*******************************************
* Pangya Server 4.9 Database Bootstrapper *
* *
* Use at your own risk! *
* *
* Please edit the @DBUSER_PASSWORD for *
* your server. Leaving the default simply *
* isn't cool. *
* *
* USAGE: *
* 1. Run this query *
* 2. Run INI3_BILLDB.sql *
* 3. Run INI3_PY_ACCOUNT.sql *
* 4. Run Pangya_S4_TH.sql *
* 5. ??? (Add/Alter procedures, etc.) *
*******************************************/
DECLARE @DBUSER_PASSWORD VARCHAR(256)
DECLARE @DBUSER_CREATE_SQL NVARCHAR(256)
SET @DBUSER_PASSWORD = N'Password' / <<<<< Coloque sua senha de SA = SQL
/* =========== Create users =========== */
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3web')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3web] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3web already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3gamebr')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3gamebr] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3gamebr already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3webstat')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3webstat] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3webstat already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'pangya')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [pangya] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User pangya already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Pangya_TH')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Pangya_TH] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Pangya_TH already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Biller')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Biller] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Biller already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'pangyaclub')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [pangyaclub] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User pangyaclub already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'payletter')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [payletter] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User payletter already exists, skipping creation...'
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'yogurting')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [yogurting] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User yogurting already exists, skipping creation...'
/* For people using the modified database we need a user Monk */
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Monk')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Monk] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Monk already exists, skipping creation...'
/* =========== ini3_py_account =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ini3_py_account')
BEGIN
ALTER DATABASE [ini3_py_account] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [ini3_py_account] SET ONLINE
DROP DATABASE [ini3_py_account]
END
/* -- Recreate Database -- */
CREATE DATABASE [ini3_py_account]
GO
/* =========== INI3Bill_DB =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'INI3Bill_DB')
BEGIN
ALTER DATABASE [INI3Bill_DB] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [INI3Bill_DB] SET ONLINE
DROP DATABASE [INI3Bill_DB]
END
/* -- Recreate Database -- */
CREATE DATABASE [INI3Bill_DB]
GO
/* =========== Pangya_S4_TH =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Pangya_S4_TH')
BEGIN
/* -- Close all connections and drop the database -- */
ALTER DATABASE [Pangya_S4_TH] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Pangya_S4_TH] SET ONLINE
DROP DATABASE [Pangya_S4_TH]
END
/* -- Recreate Database -- */
CREATE DATABASE [Pangya_S4_TH]
DECLARE @DATABASE_PATH VARCHAR(256)
SELECT @DATABASE_PATH = physical_name
FROM sys.master_files
WHERE name = N'Pangya_S4_TH'
SET @DATABASE_PATH = REPLACE(@DATABASE_PATH, 'Pangya_S4_TH.mdf', '')
PRINT 'Pangya_S4_TH database resides at: ' + @DATABASE_PATH
/* --- Add File Groups and Files to the database Pangya_S4_TH --- */
DECLARE @ADDFILE_CARD_SQL varchar(256)
DECLARE @ADDFILE_GLOG_SQL varchar(256)
DECLARE @ADDFILE_ITEM_SQL varchar(256)
DECLARE @ADDFILE_MAIL_SQL varchar(256)
SET @ADDFILE_CARD_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Card, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Card.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Card'
SET @ADDFILE_GLOG_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_gLog, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_gLog.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_gLog'
SET @ADDFILE_ITEM_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Item, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Item.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Item'
SET @ADDFILE_MAIL_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Mail, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Mail.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Mail'
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Card;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_gLog;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Item;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Mail;
EXEC(@ADDFILE_CARD_SQL)
EXEC(@ADDFILE_GLOG_SQL)
EXEC(@ADDFILE_ITEM_SQL)
EXEC(@ADDFILE_MAIL_SQL)
GO
@Update_15.01.11
@Update_01.02.11
USP_CHAR_EQUIP_SAVE_S4 <<< Procedure que corrige o erro de desequipar a Roupa dos Char's
Código:
USE [Pangya_S4_TH]
GO
/****** Im not sure everyone needs this but i didnt get the other fix to work, here is a SP that works. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
IF ( @DEBUG = 1 ) BEGIN
SELECT *
FROM #TD_CHAR
END
/*****************************************
?? ???? ITEM ??: SELECT * FROM TD_CARD_EQUIP
??? ??? ????? ????? ??? ???..
? ??????....
?? ?? ??? ??? ??? ?.?
******************************************/
UPDATE DBO.TD_CHAR_EQUIP_S4
SET USE_YN = 'N'
WHERE UID = @UID
AND CHAR_ITEMID = @CHAR_ITEM_ID
INSERT INTO DBO.TD_CHAR_EQUIP_S4 ( [UID], CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, USE_YN, IN_DATE )
SELECT
B.[UID]
, B.CHAR_ITEMID
, CASE B.ITEMID WHEN 0 THEN 0 ELSE B.ITEMID END
, B.TYPEID
, B.EQUIP_NUM
, 'Y'
, GETDATE()
FROM (
SELECT [UID], CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, 'Y' AS USE_YN
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
Vamos aos creditos: Advance/Darkknight/bubbastic/Tsukasa/Fernando.
------------------------------------------------------------------------------------------------------------------- |
