[HELP] Siege script every week

Page 3 of 3 12 3
10/09/2020 08:45 [DEV]Dekaron#31
Quote:
Originally Posted by Enthrize View Post
I've tried to read everything from this thread, but I still get error when executing the scripts. The error line was on EXEC SP_SIEGE_INFO_R saying this error. [Only registered and activated users can see links. Click Here To Register...]

I've already cleaned the GUILD_INFO and SIEGE_INFO tables. I'm using SQL Server 2014, hopefully someone could give some hints to fix this? Thanks you in advance.
Witch Query :)

Quote:
Use master;
Go
EXEC sp_addlinkedserver 'LSV_GHBILL','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRACNT','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'DKRCASH','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRHELION','','MSDASQL',NULL,NULL,'DRIVER={SQ L Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRNUNVICE','','MSDASQL',NULL,NULL,'DRIVER={S QL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRQUNE','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRRONDOW','','MSDASQL',NULL,NULL,'DRIVER={SQ L Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRSIZ','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRTEST','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRZUTO','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'LSV_DKRSTA','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
EXEC sp_addlinkedserver 'DKRACCOUNT01','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=localhost;UID=;PWD=;'
Quote:
Use master;
Go
exec sp_serveroption 'LSV_GHBILL','rpc out','true'
exec sp_serveroption 'LSV_GHBILL','rpc','true'
exec sp_serveroption 'LSV_DKRACNT','rpc out','true'
exec sp_serveroption 'LSV_DKRACNT','rpc','true'
exec sp_serveroption 'DKRCASH','rpc out','true'
exec sp_serveroption 'DKRCASH','rpc','true'
exec sp_serveroption 'LSV_DKRHELION','rpc out','true'
exec sp_serveroption 'LSV_DKRHELION','rpc','true'
exec sp_serveroption 'LSV_DKRNUNVICE','rpc out','true'
exec sp_serveroption 'LSV_DKRNUNVICE','rpc','true'
exec sp_serveroption 'LSV_DKRQUNE','rpc out','true'
exec sp_serveroption 'LSV_DKRQUNE','rpc','true'
exec sp_serveroption 'LSV_DKRRONDOW','rpc out','true'
exec sp_serveroption 'LSV_DKRRONDOW','rpc','true'
exec sp_serveroption 'LSV_DKRSIZ','rpc out','true'
exec sp_serveroption 'LSV_DKRSIZ','rpc','true'
exec sp_serveroption 'LSV_DKRTEST','rpc out','true'
exec sp_serveroption 'LSV_DKRTEST','rpc','true'
exec sp_serveroption 'LSV_DKRZUTO','rpc out','true'
exec sp_serveroption 'LSV_DKRZUTO','rpc','true'
exec sp_serveroption 'LSV_DKRSTA','rpc out','true'
exec sp_serveroption 'LSV_DKRSTA','rpc','true'
exec sp_serveroption 'DKRACCOUNT01','rpc out','true'
exec sp_serveroption 'DKRACCOUNT01','rpc','true'
or maby this one
Quote:
USE [Character]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 10/12/2020 by ULTIMO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strAux varchar(20)
DECLARE @v_strtime varchar(20)
SET @v_strAux = CAST(CAST(@i_bin_time as int) as varchar(20))
IF len(@v_strAux) = 7
SELECT @v_strtime = '20200' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
IF len(@v_strAux) = 8
SELECT @v_strtime = '2020' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
SELECT @v_strtime = '202' + CAST(CAST(@i_bin_time as int) as varchar(20))

SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END


////////////////////////////////////////////////////////////////


USE [Character];
GO
DECLARE @v_siege_no char(10)
DECLARE @guild_code varchar(10)
DECLARE @guild_name varchar(30)
DECLARE @v_strDate varchar(10)
DECLARE @v_regdate varchar(10)
DECLARE @v_strChannel varchar(2)
DECLARE @i_bychannel tinyint
DECLARE @o_dwDungeonRegistTime varbinary(4)
DECLARE @o_DeadFrontTimeInfo varbinary(1000)
DECLARE @o_sp_rtn int
DECLARE @o_dwRegistTimeStart varbinary(4)
DECLARE @o_dwRegistTimeEnd varbinary(4)
DECLARE @o_dwTaxRegistTime varbinary(4)
DECLARE @o_dwStartTime varbinary(4)
DECLARE @o_dwRegistTime varbinary(4)
DECLARE @v_strDate1 varchar(10)
DECLARE @i_GetDate datetime
DECLARE @v_siege_start_date varchar(14)
DECLARE @v_siege_start_time varbinary(4)
SET @i_GetDate=getdate()
-- Siege War Every Saturday:
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 7-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'
--
SELECT @v_siege_start_time = SUBSTRING(@v_siege_start_date,4,1) * 100000000
+ SUBSTRING(@v_siege_start_date,5,2) * 1000000
+ SUBSTRING(@v_siege_start_date,7,2) * 10000
+ SUBSTRING(@v_siege_start_date,9,2) * 100
+ SUBSTRING(@v_siege_start_date,11,2)
SET @guild_code = '001'
SET @guild_name = 'FirstSiege'
SET @v_siege_no = SUBSTRING(dbo.FN_DateToShortStrDate(GetDate()), 3, 4) + '0001'
INSERT
INTO GUILD_INFO(guild_code, guild_name, guild_Level, bystate, bychannel)
VALUES (@guild_code, @guild_name, 3, 1, 1)
INSERT
INTO SIEGE_INFO(SIEGE_NO, CHANNEL_NO, GUILD_CODE, GUILD_NAME, DWSTARTTIME, BYREGISTTIMEFLAG, DEFENDER_INFO, SIEGE_TAG)
VALUES (@v_siege_no, 1, @guild_code, @guild_name,@v_siege_start_time, 1, 0X0000, 'Y')
EXEC SP_SIEGE_START_TIME_U @guild_code,1,0x01,@v_siege_start_time output,@o_dwRegistTime output,@o_sp_rtn output
SET @v_strDate1=dbo.FN_BinDateToDateTime(@v_siege_star t_time)
DECLARE @P1 varchar(13)
SET @P1=NULL
DECLARE @P2 varchar(33)
SET @P2=NULL
DECLARE @P3 varchar(25)
SET @P3=NULL
DECLARE @P4 varbinary(4)
SET @P4=NULL
DECLARE @P5 varbinary(4)
SET @P5=NULL
DECLARE @P6 varbinary(4)
SET @P6=NULL
DECLARE @P7 varbinary(4)
SET @P7=NULL
DECLARE @P8 varbinary(4)
SET @P8=NULL
DECLARE @P9 varbinary(4)
SET @P9=NULL
DECLARE @P10 varbinary(4)
SET @P10=NULL
DECLARE @P11 varbinary(2)
SET @P11=NULL
DECLARE @P12 varbinary(2)
SET @P12=NULL
DECLARE @P13 varbinary(2)
SET @P13=NULL
DECLARE @P14 int
SET @P14=NULL
DECLARE @P15 int
SET @P15=NULL
DECLARE @P16 varbinary(4)
SET @P16=NULL
DECLARE @P17 varbinary(996)
SET @P17=NULL
DECLARE @p18 varbinary(996)
SET @p18=NULL
DECLARE @P19 varbinary(1000)
SET @P19=NULL
DECLARE @P20 int
SET @P20=NULL
EXEC SP_SIEGE_INFO_R 1, @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, @P12 output, @P13 output, @P14 output, @P15 output, @P16 output, @P17 output, @p18 output, @P19 output, @P20 output
SELECT @P1, @P2, @P3, @P4, @P5, @P6,@v_strDate1
10/09/2020 08:49 guesswho-.-#32
Quote:
Originally Posted by Enthrize View Post
I've tried to read everything from this thread, but I still get error when executing the scripts. The error line was on EXEC SP_SIEGE_INFO_R saying this error. [Only registered and activated users can see links. Click Here To Register...]

I've already cleaned the GUILD_INFO and SIEGE_INFO tables. I'm using SQL Server 2014, hopefully someone could give some hints to fix this? Thanks you in advance.
Did you even read the error? It says the query has too many parameters specified. Clearly you need to compare the SP and see what parameters it takes and adjust your query to match the parameters you're using.

Really it was there all along, all you had to do is read the error.
10/09/2020 08:52 [DEV]Dekaron#33
Quote:
Originally Posted by guesswho-.- View Post
Did you even read the error? It says the query has too many parameters specified. Clearly you need to compare the SP and see what parameters it takes and adjust your query to match the parameters you're using.

Really it was there all along, all you had to do is read the error.
Paul they don't read any think they just want open the server :) and get cash :)
10/10/2020 13:45 Enthrize#34
Quote:
Originally Posted by guesswho-.- View Post
Did you even read the error? It says the query has too many parameters specified. Clearly you need to compare the SP and see what parameters it takes and adjust your query to match the parameters you're using.

Really it was there all along, all you had to do is read the error.
I actually understand that the parameters from the script is over when it's compared to the SP parameters. I just wondered why the other DEVs doesn't get the error line at SP_SIEGE_INFO_R. I tried what you said and compared the script params and SP params they have one difference so I removed it from the script params and tried to execute it and get another error.

"Msg 242, Level 16, State 3, Procedure SP_SIEGE_INFO_R, Line 104
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I am not sure enough if I had to remove or edit something on the given script or just stick to it. Sorry for the trouble, still figuring out how it really works.
10/10/2020 14:06 guesswho-.-#35
You removed the wrong param then.
10/10/2020 15:31 Enthrize#36
Quote:
Originally Posted by guesswho-.- View Post
You removed the wrong param then.
Does it mean in order to fix this, I'll just focus on what to remove in the Script Params? Please enlighten me.

To be honest I'm not asking for a direct fix to the problem, I also want to learn it I just need some hints what to focus on to solve the issue.
12/31/2021 04:39 jerbo_28#37
so easy problem
01/05/2022 15:49 Azuraeee#38
Quote:
Originally Posted by nemesidevmv View Post
USE [Character]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 10/12/2020 by ULTIMO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strAux varchar(20)
DECLARE @v_strtime varchar(20)
SET @v_strAux = CAST(CAST(@i_bin_time as int) as varchar(20))
IF len(@v_strAux) = 7
SELECT @v_strtime = '20200' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
IF len(@v_strAux) = 8
SELECT @v_strtime = '2020' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
SELECT @v_strtime = '202' + CAST(CAST(@i_bin_time as int) as varchar(20))

SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END


////////////////////////////////////////////////////////////////


USE [Character];
GO
DECLARE @v_siege_no char(10)
DECLARE @[Only registered and activated users can see links. Click Here To Register...]_code varchar(10)
DECLARE @[Only registered and activated users can see links. Click Here To Register...]_name varchar(30)
DECLARE @v_strDate varchar(10)
DECLARE @[Only registered and activated users can see links. Click Here To Register...]egdate varchar(10)
DECLARE @v_strChannel varchar(2)
DECLARE @i_bychannel tinyint
DECLARE @o_dwDungeonRegistTime varbinary(4)
DECLARE @o_DeadFrontTimeInfo varbinary(1000)
DECLARE @o_sp_rtn int
DECLARE @o_dwRegistTimeStart varbinary(4)
DECLARE @o_dwRegistTimeEnd varbinary(4)
DECLARE @o_dwTaxRegistTime varbinary(4)
DECLARE @o_dwStartTime varbinary(4)
DECLARE @o_dwRegistTime varbinary(4)
DECLARE @v_strDate1 varchar(10)
DECLARE @i_GetDate datetime
DECLARE @v_siege_start_date varchar(14)
DECLARE @v_siege_start_time varbinary(4)
SET @i_GetDate=getdate()
-- Siege War Every Saturday:
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 7-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'
--
SELECT @v_siege_start_time = SUBSTRING(@v_siege_start_date,4,1) * 100000000
+ SUBSTRING(@v_siege_start_date,5,2) * 1000000
+ SUBSTRING(@v_siege_start_date,7,2) * 10000
+ SUBSTRING(@v_siege_start_date,9,2) * 100
+ SUBSTRING(@v_siege_start_date,11,2)
SET @[Only registered and activated users can see links. Click Here To Register...]_code = '001'
SET @[Only registered and activated users can see links. Click Here To Register...]_name = 'FirstSiege'
SET @v_siege_no = SUBSTRING(dbo.FN_DateToShortStrDate(GetDate()), 3, 4) + '0001'
INSERT
INTO GUILD_INFO(guild_code, guild_name, guild_Level, bystate, bychannel)
VALUES @[Only registered and activated users can see links. Click Here To Register...]_code, @[Only registered and activated users can see links. Click Here To Register...]_name, 3, 1, 1)
INSERT
INTO SIEGE_INFO(SIEGE_NO, CHANNEL_NO, GUILD_CODE, GUILD_NAME, DWSTARTTIME, BYREGISTTIMEFLAG, DEFENDER_INFO, SIEGE_TAG)
VALUES (@v_siege_no, 1, @[Only registered and activated users can see links. Click Here To Register...]_code, @[Only registered and activated users can see links. Click Here To Register...]_name,@v_siege_start_time, 1, 0X0000, 'Y')
EXEC SP_SIEGE_START_TIME_U @[Only registered and activated users can see links. Click Here To Register...]_code,1,0x01,@v_siege_start_time output,@o_dwRegistTime output,@o_sp_rtn output
SET @v_strDate1=dbo.FN_BinDateToDateTime(@v_siege_star t_time)
DECLARE @P1 varchar(13)
SET @P1=NULL
DECLARE @P2 varchar(33)
SET @P2=NULL
DECLARE @P3 varchar(25)
SET @P3=NULL
DECLARE @P4 varbinary(4)
SET @P4=NULL
DECLARE @P5 varbinary(4)
SET @P5=NULL
DECLARE @P6 varbinary(4)
SET @P6=NULL
DECLARE @P7 varbinary(4)
SET @P7=NULL
DECLARE @P8 varbinary(4)
SET @P8=NULL
DECLARE @P9 varbinary(4)
SET @P9=NULL
DECLARE @P10 varbinary(4)
SET @P10=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] varbinary(2)
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] varbinary(2)
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @P13 varbinary(2)
SET @P13=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] int
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @P15 int
SET @P15=NULL
DECLARE @P16 varbinary(4)
SET @P16=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] varbinary(996)
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] varbinary(996)
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] varbinary(1000)
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
DECLARE @[Only registered and activated users can see links. Click Here To Register...] int
SET @[Only registered and activated users can see links. Click Here To Register...]=NULL
EXEC SP_SIEGE_INFO_R 1, @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @[Only registered and activated users can see links. Click Here To Register...] output, @[Only registered and activated users can see links. Click Here To Register...] output, @P13 output, @[Only registered and activated users can see links. Click Here To Register...] output, @P15 output, @P16 output, @[Only registered and activated users can see links. Click Here To Register...] output, @[Only registered and activated users can see links. Click Here To Register...] output, @[Only registered and activated users can see links. Click Here To Register...] output, @[Only registered and activated users can see links. Click Here To Register...] output
SELECT @P1, @P2, @P3, @P4, @P5, @P6,@v_strDate1

----------------------------------------------------------------
Can I ask for your SP_SIEGE_INFO_R?
Page 3 of 3 12 3