February 22, 2009 at 5:55 pm
Hi,
I have a job, which runs dts package as below in sql 2000 ABC. I have moved the dts package to sql 2005. Now I need to script this job in sql 2000 and run it sql 2005,XYZ
What should I have to change in this script before running it in sql server 2005(XYZ)-- Script generated on 2/22/2009 3:21 PM
-- By: ABC\admin
-- Server: (local)
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)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Agent Import')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Agent Import'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Agent Import'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Agent Import', @owner_login_name = N'sa', @description = N'No description available.', @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
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Agent Import', @command = N'dtsrun /S ABC /E /N agent_import /A gstrServer:8=ABC /A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'c:\agent_import.txt', @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
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'daily agent import', @enabled = 1, @freq_type = 4, @active_start_date = 20070829, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
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:
February 22, 2009 at 6:06 pm
I'm not completely sure. Why not try it in 2005 and see where it might error out? You can do the compile and not run it.
February 22, 2009 at 6:13 pm
I just want to make sure that Shoud I need to change the servername from ABC to XYZ\ins1 or XYZ in the below statements. In the sql 2005, I have named instance INs1.
@command = N'dtsrun /S ABC /E /N agent_import /A gstrServer:8=ABC /A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec',
February 23, 2009 at 2:26 am
I guess you need to change server name as you would like to execute it on new server but make sure and test it on dev or test environment before doing changes on production.
Cheers!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 23, 2009 at 2:01 pm
thanks
@command = N'dtsrun /S ABC /E /N agent_import /A gstrServer:8=ABC /A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec'
In this job script, when Iam going to run in sql 2005, should I replace ABC with XYZ\ins1 or just XYZ?
ABC:sql 2000 with default instance.
XYZ: new sql server
Ins1: Instance name
February 23, 2009 at 2:08 pm
Mani (2/22/2009)
What should I have to change in this script before running it in sql server 2005(XYZ)
I would suggest running the SQL Server 2000 to 2005 Migration Assistant on this script.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 9:47 pm
I would suggest running the SQL Server 2000 to 2005 Migration Assistant on this script
What is the Migration Assistantant to run?
February 24, 2009 at 7:04 am
It is a free tool downloadable from Microsoft.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply