April 11, 2011 at 12:57 pm
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
April 11, 2011 at 1:36 pm
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.
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 11, 2011 at 1:48 pm
Thanks I was missing the rpc setting
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply