| Author |
 Topic  |
|
|
Pepo
Starting Member
Bulgaria
5 Posts |
 Posted - 08/15/2012 : 01:40:47
|
Hello.
I want to make a server job, that every day copy values from one table to other. I wrote a script, but I want to use it in two databases on one server. Do I need to create the other job or can used only one with different steps (where only new will be database name) ? I want to use it in DB1 and DB2.
This is my script, can you tell me that is correct?
Thanks!
EXEC msdb..sp_add_job
@job_name='myJob',
@enabled=1,
@description='Test'
GO
EXEC msdb..sp_add_jobstep
@job_name='myJob',
@step_name='jobStepChangeFuturedAddress',
@subsystem='TSQL',
@command='
UPDATE Users SET Address = t1.Address,City = t1.City
FROM
(SELECT Address,City
FROM Address
where DATEDIFF(dd,0, EffectiveDate) = DATEDIFF(dd,0, GETDATE())) t1
WHERE ID = t1.UserID
',
@database_name='DB1'
GO
EXEC msdb..sp_add_jobschedule
@job_name='myJob',
@name = 'jobScheduleMyJob',
@freq_type=4,
@freq_interval=1,
@active_start_time='000000'
GO
EXEC msdb..sp_add_jobserver
@job_name='myJob'
@server_name='MyServerName'
GO
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48031 Posts |
Posted - 08/15/2012 : 10:14:32
|
where's the second step?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Pepo
Starting Member
Bulgaria
5 Posts |
Posted - 08/17/2012 : 02:22:04
|
| I have (and need) only one step, but need to make it in both database. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48031 Posts |
Posted - 08/17/2012 : 10:12:39
|
even then you should have two steps. the step command being exactly same but databases being different
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Pepo
Starting Member
Bulgaria
5 Posts |
Posted - 08/27/2012 : 03:32:13
|
If I understand correctly, the only thing I should add is
EXEC msdb..sp_add_jobstep
@job_name='myJob',
@step_name='jobStepChangeFuturedAddress',
@subsystem='TSQL',
@command='
UPDATE Users SET Address = t1.Address,City = t1.City
FROM
(SELECT Address,City
FROM Address
where DATEDIFF(dd,0, EffectiveDate) = DATEDIFF(dd,0, GETDATE())) t1
WHERE ID = t1.UserID
',
@database_name='DB2'
GO
right? |
Edited by - Pepo on 08/27/2012 03:32:56 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48031 Posts |
Posted - 08/27/2012 : 11:42:15
|
yep..as per your stated requirement
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Pepo
Starting Member
Bulgaria
5 Posts |
Posted - 08/28/2012 : 04:49:58
|
Thank you a lot!
I have one more question - If I execute all from my first post (now, I want to run this job only for first database) and after few days I want to start this job to my second Database, I only need to execute the jobstop from my previous post, right? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48031 Posts |
Posted - 08/28/2012 : 23:19:26
|
yep...thats enough...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Pepo
Starting Member
Bulgaria
5 Posts |
Posted - 08/29/2012 : 05:09:01
|
OK,
Thank you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48031 Posts |
Posted - 08/29/2012 : 12:47:47
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
SQL Server Job
