November 8, 2006 at 10:30 am
My name is José Luis Moreno Ubeda,
I am a nicaraguan systems engineer,
I work like Analyst Developer of Software.
In SQL Server 2000, I had created sp, a Job, an error, an operator and an alert through
T-SQL.The steps that job made are the following ones, every day to 12:00 p.m. executed sp dynamic with parameters that endorsed 4 bds of a Server automatically protected by password, if sp failed, the work shot a tie error to the alert before mentioned and towards arriving a notification at certain operators from the failure in the endorsement so that automatically problematic one was verified this.
Now in SQL Server 2005 I cannot dynamically create these elements with script that we had in T-SQL.
Since this required to temporarily qualify the direct edition in system catalogues (bd masters), to add the new error, it alerts, operators etc, from script.
Since I can do this, if SQL 2005 does not allow the updates ad hoc? I must form the Job of artisan form, the error, operators, sp, alert for each server that will use east Job?
We cannot waste time and resources manually doing this. I would like to know a solution on the matter.
Sorry for my bad english.
November 8, 2006 at 3:15 pm
Hi Jose,
Don't worry about your English - it's still better than my Spanish.
If you were using stored procedures in SQL 2K, you can probably use SSIS to execute those same procedures and capture whatever values they return by using the Execute SQL Task in conjunction with Package Parameters. Then you can use those values to trigger what ever tasks and/or execute whatever SQL statements that you need to perform. And you can run that SSIS pkg as a Job from SSMS.
The Execute SQL Task is not intuitive (especially the passing variables as parameters to it, and retrieving values and populating pkg varaibles from it), however once you understand how it works it's a great tool.
Here's a wonderful explanation of it from another developer.
http://www.sqlis.com/default.aspx?58
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply