best approach to have a job on sql 2005 update sql 2008 server

• timscronin

  SSCertifiable

  Points: 6851

  More actions

  April 11, 2011 at 12:57 pm

  #234691

  I am having some conflicts between servers pushing a pulling some data for a warehouse. I have a sql 2005 server which I am targeting for a job to load a table locally on one of it's db's. After completion I would like the same job to initiate a job on a sql 2008 server we have here locally to pull that data to our warehouse. Both servers use the same high level service account. Would the best approach be to on success of the job on the sql 2005 server have it try to initiate the job on the 2008 server, or maybe have it fire a sp on the 2008 server to do the same? Looking for anyone with similar experience

  thanks

• Portes Ysaias

  SSCrazy

  Points: 2144

  More actions

  April 11, 2011 at 1:36 pm

  #1310075

  Follow these steps and you will accomplish what you need:

  Step 1)

  USE master

  GO

  --Create a Linked server on the SQL 2005 that points to the SQL 2008'

  EXEC master.dbo.sp_addlinkedserver @server = N'SQLSERVER2008',

  @srvproduct = N'SQL Server'

  GO

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLSERVER2008',

  @useself = N'True', @locallogin = NULL, @rmtuser = NULL,

  @rmtpassword = NULL

  GO

  Step 2)

  Add a step to your SQL 2005 Job that executes the code below:

  Change the "MySQL2008_Job" parameter to the name of your SQL 2008 Job

  --Create a variable that holds the script to be executed on the SQL 2008 Server'

  DECLARE @StartJobScript VARCHAR(256)

  SET @StartJobScript = 'msdb..sp_start_job @job_name=''MySQL2008_Job'''

  --Execute the script using the linked server'

  EXEC(@StartJobScript) AT SQLSERVER2008

  If you have any further needs, please feel free to email me.

  Ysaias Portes | Forward Thinkers Consulting
  sp_SQLFlightRecorder — open-source SQL Server diagnostic recorder | forwardthinkersconsulting.com

• timscronin

  SSCertifiable

  Points: 6851

  More actions

  April 11, 2011 at 1:48 pm

  #1310081

  Thanks I was missing the rpc setting