May 21, 2014 at 3:06 pm
I've had to write a couple of fairly complex SQL jobs, which I developed and tested on our test server. Got all of the bugs worked out. Now I want to move those jobs to the production server. Both are SQL 2012. My inclination is to click on the SQL Job in SSMS, right mouse button click and then select "Script Job As ... | Create To ..." and send it to the Windows clipboard, where I can then paste it into SSMS on the server. However, when I sent the script to a new query window, there was a lot of apparent errors. All dealing with single tic marks (') as not being properly terminating either strings or dates.
So is there a better way to copy the SQL Job from one machine to another? (I'm guessing this is where I would use the SQL Server Import and Export Wizard, or am I wrong about that?)
Kindest Regards, Rod Connect with me on LinkedIn.
May 21, 2014 at 3:10 pm
I've always been successful by scripting the job to a new Query Editor Window, then changing the connection on that window to the destination server.
Be still, and know that I am God - Psalm 46:10
May 21, 2014 at 3:57 pm
david.gugg (5/21/2014)
I've always been successful by scripting the job to a new Query Editor Window, then changing the connection on that window to the destination server.
This is the same approach I take.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 11:56 pm
you can use powershell scripts commands to move all your jobs by a single job on server.
May 22, 2014 at 6:36 am
srikantmeister (5/21/2014)
you can use powershell scripts commands to move all your jobs by a single job on server.
Do you have any links or example of these scripts?
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 22, 2014 at 6:42 am
SQLRNNR (5/21/2014)
david.gugg (5/21/2014)
I've always been successful by scripting the job to a new Query Editor Window, then changing the connection on that window to the destination server.This is the same approach I take.
Me too. It's very consistent.
May 22, 2014 at 7:53 am
david.gugg (5/21/2014)
I've always been successful by scripting the job to a new Query Editor Window, then changing the connection on that window to the destination server.
So you just script the job to a new Query Editor Window, maybe copy it from there and paste it into another query window for the other server, and run it? That's what I'd like to do, but I thought it would complain.
Kindest Regards, Rod Connect with me on LinkedIn.
May 22, 2014 at 8:16 am
The only problem I've ever seen with that is if your job has a schedule. Your script will have a section something like this:
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 20:00',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130218,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959,
@schedule_uid=N'b74a3fab-4c59-4aaa-9412-494f6870b918'
The @schedule_uid parameter often causes problems when you run the script on a different server, so just remove that line and the preceding comma.
John
May 22, 2014 at 8:19 am
Rod at work (5/22/2014)
david.gugg (5/21/2014)
I've always been successful by scripting the job to a new Query Editor Window, then changing the connection on that window to the destination server.So you just script the job to a new Query Editor Window, maybe copy it from there and paste it into another query window for the other server, and run it? That's what I'd like to do, but I thought it would complain.
You don't even need to do any copy and paste. Just right click in the whitespace in the code window, then go to Connection -> Change Connection... and choose your destination server.
Be still, and know that I am God - Psalm 46:10
May 22, 2014 at 11:14 pm
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply