SQL job calling stored procedure with a variable
Posted on 2013-01-14
I'd like to find a way to have a Job call a stored procedure with the Job providing a 'variable' to the SP dicatating where the SP updates its data.
I have SQLServer 2008 running on SERVER1. I have SQLExpress running on 3 Win7 workstations [WS1, WS2, WS3]. I have linked servers setup on SERVER1 that connect to the SQLExpress instances on each of the 3 workstations.
An example instance name is WS1\SQLExpress
Each workstation has the same database on it [LOCAL_DB].
SERVER1 hosts a SERVER_DB. I need to update a table in the LOCAL_DB database(s) with data from the SERVER_DB database on a daily basis.
I have a Stored Procedure (named SP_update) in SERVER_DB that clears the LOCAL_DB table and then copies records from SERVER_DB to the LOCAL_DB. The SP currently calls the specific instance name.
--Stored Procedure example:
SET NOCOUNT ON;
INSERT INTO [WS1\SQLEXPRESS].LOCAL_DB.dbo.Table1
--End of Example
I'd like to be able to use 1 Stored Procedure to update all 3 of the workstation databases.
I'd like to be able to call the SP from a Job and provide a 'variable' that tells the SP which workstation to do the update on.
In other words, I'd call the SP from a job and provide a variable of 'WS1\SQLExpress' for the first iteration, so workstation 1 data gets updated. Then run Job again and then provide a variable of 'WS2\SQLExpress' so workstation 2 data gets updated. And so on.
--Job step code:
--End of Job step
How would I need to update my SP to do this?
And how to I structure my Stored Procedure call in the Job?
Thank you for your assistance.