Tool to Execute SQL Server Scripts and Automatically Recognize and Prompt for Scripting Variables
-1
votes
2
answers
133
views
I have a folder of scripts that contain multiple objects and jobs that I roll out every time I deploy a new SQL Server Instance. The scripts utilize scripting variables, as an example, here is an abridged example of a job creation script:
DECLARE @Owner SYSNAME = (SELECT [name] FROM sys.server_principals WHERE [sid] = 0x01)
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Myjob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'blah',
@category_name=N'Database Maintenance',
@owner_login_name=@Owner,
@notify_email_operator_name=N'$(AlertOperator)', @job_id = @jobId OUTPUT
Note that
@notify_email_operator_name
will be set to whatever value is passed to $(AlertOperator)
These scripts are usually run through a Powershell script which loops through the folder and passes values to the $(AlertOperator)
variable.
This approach allows a suite of scripts to be kept which can be rolled out to a new server easily.
I was wondering if there was a GUI tool where I can open one or more .sql files and it would automatically recognize the scripting variables in those files and prompt for their values before running the files against one or more defined servers?
This feature of SSMS Almost does what I want, the only problem is that the parameters must be in the ` format rather than
$(variableName) making it incompatible with the current Powershell setup. Another issue with it (which I could live with) is that it only really does a find and replace, rather than executing the script. I could live with that but the non support for
$()` is unfortunately a deal breaker
Asked by SE1986
(2192 rep)
Feb 26, 2021, 01:48 PM
Last activity: Feb 26, 2021, 04:27 PM
Last activity: Feb 26, 2021, 04:27 PM