Topic: Auto Reset - script SQL 2000

Auto Reset - script SQL 2000

There is SQL 2000 auto reset script, working on 0.99.62XT versions:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'AutoReset')
IF (@JobID IS NOT NULL)
BEGIN
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
RAISERROR (N'Unable to import job ''AutoReset'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb.dbo.sp_delete_job @job_name = N'AutoReset'
SELECT @JobID = NULL
END

BEGIN

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'AutoReset', @owner_login_name = N'sa', @description = N'AutoResetScript', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'AutoReset', @command = N'UPDATE Character
SET clevel = 1, Experience = 0, LevelUpPoint = 1000 * (Reset + 1), Money = Money - (50000000), Strength = 18, Dexterity = 18, Vitality = 15, Energy = 30, MapNumber = 0, MapPosX = 130, MapPosY = 130, PkTime = 0, PkCount = 0, PkLevel = 0, Reset = Reset + 1
FROM Character JOIN MEMB_STAT ON Character.AccountID=MEMB_STAT.memb___id
COLLATE Latin1_general_CI_AI
WHERE ((class = 0) OR (class = 1)) AND (clevel >= 380) AND (Money >= (50000000)) AND (ConnectStat = 0) AND (Reset < 500);

UPDATE Character
SET clevel = 1, Experience = 0, LevelUpPoint = 1000 * (Reset + 1), Money = Money - (50000000), Strength = 28, Dexterity = 20, Vitality = 25, Energy = 10, MapNumber = 0, MapPosX = 130, MapPosY = 130, PkTime = 0, PkCount = 0, PkLevel = 0, Reset = Reset + 1
FROM Character JOIN MEMB_STAT ON Character.AccountID = MEMB_STAT.memb___id
COLLATE Latin1_general_CI_AI
WHERE ((class = 16) OR (class = 17)) AND (clevel >= 380) AND (Money >= (50000000)) AND (ConnectStat = 0) AND (Reset < 500);

UPDATE Character
SET clevel = 1, Experience = 0, LevelUpPoint = 1000 * (Reset + 1), Money = Money - (50000000), Strength = 22, Dexterity = 25, Vitality = 20, Energy = 15, MapNumber = 0, MapPosX = 130, MapPosY = 130, PkTime = 0, PkCount = 0, PkLevel = 0, Reset = Reset + 1
FROM Character JOIN MEMB_STAT ON Character.AccountID = MEMB_STAT.memb___id
COLLATE Latin1_general_CI_AI
WHERE ((class = 32) OR (class = 33)) AND (clevel >= 380) AND (Money >= (50000000)) AND (ConnectStat = 0) AND (Reset < 500);

UPDATE Character
SET clevel = 1, Experience = 0, LevelUpPoint = 1000 * (Reset + 1), Money = Money - (50000000), Strength = 26, Dexterity = 26, Vitality = 26, Energy = 26, MapNumber = 0, MapPosX = 130, MapPosY = 130, PkTime = 0, PkCount = 0, PkLevel = 0, Reset = Reset + 1
FROM Character JOIN MEMB_STAT ON Character.AccountID = MEMB_STAT.memb___id
COLLATE Latin1_general_CI_AI
WHERE (class = 48) AND (clevel >= 380) AND (Money >= (50000000)) AND (ConnectStat = 0) AND (Reset < 500);

UPDATE Character
SET clevel = 1, Experience = 0, LevelUpPoint = 1000 * (Reset + 1), Money = Money - (50000000), Strength = 26, Dexterity = 26, Vitality = 26, Energy = 26, MapNumber = 0, MapPosX = 130, MapPosY = 130, PkTime = 0, PkCount = 0, PkLevel = 0, Reset = Reset + 1
FROM Character JOIN MEMB_STAT ON Character.AccountID = MEMB_STAT.memb___id
COLLATE Latin1_general_CI_AI
WHERE (class = 64) AND (clevel >= 380) AND (Money >= (50000000)) AND (ConnectStat = 0) AND (Reset < 500);
', @database_name = N'MuOnline', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'AutoReset', @enabled = 1, @freq_type = 4, @active_start_date = 20060831, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Before you launch the query, be sure, that:

1st:

Exist column "Reset" in Database => MuOnline =>Character
If no, right click on table "character" and add column name: "Reset" , data type: "int" , lenght: "4", allow nulls: "NO", and setin table "Columns" default value to "(0)" .

2nd:
Column "Reset" must be set to default value "(0)". To do this, right click on character table, find reset column and disable nulls. Under the Reset column, in sction "columns", set default value to "(0)".

3rd:

Do not forget to run SQL Server agent, otherwise the script wont work.

Autoreset parameters:
Reset lvl: 380
Addpoints after reset: 1000
Stats stay: NO
Reset Cost: 50m

To autoreset: Log off for 1 minute
Done

PS: Do not write "" in tables!

Re: Auto Reset - script SQL 2000

Can you describe more thoroughly for this command???