script for mssql

Question

team.

i need a PowerShell script each below,

powershell to check, sql job named "abc_input" . if it's available then leave it, else create that job.
powershell to check. sql job named "abc_input" . if it's contain last step named "job3" , then remove last step.
powershell need to create DB mail configuration ( by passing input arguments ).
powershell need to create sql alerts ( by passing input arguments ).

or Else in Tsql xmd,

i need a Tsql script each below,

Tsql to check, sql job named "abc_input" . if it's available then leave it, else create that job.
Tsql to check. sql job named "abc_input" . if it's contain last step named "job3" , then remove last step.
Tsql need to create DB mail configuration ( by passing input arguments ).
Tsql need to create sql alerts ( by passing input arguments ).

Answer 1

Hi there,

again: you do not need to re-invent the wheel.
There a plenty of ways out there.
One of my favourites: dbatools

https://dbatools.io/commands/

Take a look which commands are available.

You want to know if a job is available? Here is the command https://docs.dbatools.io/#Get-DbaAgentJob

You want to check for a job step?
Here is the command:
https://docs.dbatools.io/#Get-DbaAgentJobStep

Database Mail?
Set up a new account
https://docs.dbatools.io/#New-DbaDbMailAccount
set up a new profile
https://docs.dbatools.io/#New-DbaDbMailProfile

Want to setup alert?
Here we go
https://docs.dbatools.io/#Set-DbaAgentAlert

It is all out there and easy to utilize

Answer 2

for - DB mail Config using T-sql,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <DB Mail configuration>
-- =============================================
alter PROCEDURE USP_DBA_dbmail_config_V1
-- Add the parameters for the stored procedure here
@Anton _name nvarchar(50),@Creat _name nvarchar(50),@tiedtlaw email _Address nvarchar(100), @publicly _Name nvarchar(50),
@smtp_server nvarchar(50),@port_number int

--USP_DBA_dbmail_config 'prof1','accname','DB-Services-MSSQL.TSG@jaswant .com','Displayname1','smtp.abc.com',25
AS
BEGIN

SET NOCOUNT ON;  

Declare @is_broker_enabled sql_variant
Declare @is_DBMail_Xps_Enabled sql_variant
Declare @is_advanced_options sql_variant
Declare @errormessage nvarchar(1000)
Declare @ReturnCode int

					select @is_DBMail_Xps_Enabled = value_in_use FROM  sys.configurations WHERE name = 'Database Mail XPs'  
					select @is_broker_enabled = is_broker_enabled from sys.databases where is_broker_enabled = 1 and name = 'msdb'  
					select @is_advanced_options = value_in_use from sys.configurations where name = 'show advanced options'  

					--select @is_broker_enabled, @is_DBMail_Xps_Enabled  


					if ( (@profile_name  = '') or (@profile_name is null))  
					Begin  
					Raiserror('Profilename cannot be null or empty, supply appropriate profilename!!!!!',16,1)  
					Return  
					end  


					if ( @is_DBMail_Xps_Enabled  = '' or (@is_DBMail_Xps_Enabled is null))  
					Begin  
					Raiserror('Check the sp_configure with Adavanced Options for Database Mail XPs',16,1)  
					Return  
					end  

					if (@is_broker_enabled = '' or (@is_broker_enabled is null))  
					Begin  
					Raiserror('Check can you manually enable is_broker_enabled option ',16,1)  
					Return  
					end  

					if (@is_advanced_options = '' or (@is_advanced_options is null))  
					Begin  
					Raiserror('Check can you manually enable @is_advanced_options option',16,1)  
					Return  
					end  



					if ((@is_DBMail_Xps_Enabled = 0) and (@is_advanced_options=0))  
					Begin  
					Print 'Database Mail XPs configuration Option is disabled and enabling it(Database Mail XPs)'  

					Exec sp_configure 'show advanced options',1  
					Reconfigure with override  
					Exec sp_configure 'Database Mail XPs',1  
					Reconfigure with override ;  
					End  
					else if ((@is_DBMail_Xps_Enabled = 0) and (@is_advanced_options=1))  
					Begin  
					Exec sp_configure 'Database Mail XPs',1;  
					Reconfigure with override ;  
					end  

					if(@is_broker_enabled=0)  
					Begin  
					alter database msdb set enable_broker with rollback immediate  
					end  
					else  
					Begin  
					Print 'is_broker_enabled on msdb......already!!!!!'  
					end  


					-- Code Starts here  

					Begin Try  
					--Creating the profile  
					BEGIN  

					IF not EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)  
							BEGIN  

									EXECUTE   msdb.dbo.sysmail_add_profile_sp    
									@profile_name = @profile_name,    
									@description = 'Profile.'  

									  

							  
									-- Grant access to the profile to the DBMailUsers role  
									EXECUTE   msdb.dbo.sysmail_add_principalprofile_sp    
									@profile_name = @profile_name,    
									@principal_name = 'public',    
									@is_default = 1 ;  

									Print @profile_name+' profile Created successfully'  

							END  


                          ELSE  
									Begin  
									Print @profile_name+ 'Profile name already there !!!!!'  
									end  


					IF not EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )  

					  BEGIN  
									--Create a Database Mail account  
									EXECUTE   msdb.dbo.sysmail_add_account_sp    
									@account_name = @account_name,    
									@description = '',    
									@email_address = @Email_Address,    
									@display_name = @Display_Name,    
									@mailserver_name = @smtp_server,  
									@port = @port_number,  
									@use_default_credentials = 1;    

									-- Add the account to the profile    
									EXECUTE   msdb.dbo.sysmail_add_profileaccount_sp    
									@profile_name = @profile_name,    
									@account_name = @account_name,    
									@sequence_number =1 ;    


									Print @account_name+' account Created successfully'  
					  END  

					   ELSE  
									Begin  
									Print @account_name+'Account name already there !!!!!'  
									end  

				     END  

					--Print @profile_name+' profile Created successfully... you can test it using sending email.....'  

					End Try  

					Begin Catch  

					select @errormessage = ERROR_MESSAGE()  
					Raiserror(@errormessage,16,1)  
					Raiserror('Please refer to above error messgae',16,1)  

					End Catch  

END
GO

Answer 3

Hi @KIRUBAKARAN J A ,

Please refer below and check whether it is helpful to you.

Tsql to check, sql job named "abc_input" . if it's available then leave it, else create that job.

DECLARE @jobId binary(16)  
  
SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'abc_input')  
IF (@jobId IS NULL)  
BEGIN  
    EXEC msdb.dbo.sp_add_job @job_name=N'abc_input'  
END  

Tsql to check. sql job named "abc_input" . if it's contain last step named "job3" , then remove last step.

DECLARE @stepId binary(16)  
  
SELECT @stepId=step_id FROM   sysjobs Job  
 INNER JOIN sysjobsteps JobStep ON Job.job_id = JobStep.job_id   
WHERE step_id=3 and step_name =N'job3' and name= N'abc_input'  
IF (@stepId IS NOT NULL)  
BEGIN  
    EXEC dbo.sp_delete_jobstep    
    @job_name = N'job3',    
    @stepId = 3 ;    
END  

Tsql need to create DB mail configuration ( by passing input arguments ).

sp_send_dbmail (Transact-SQL)
How to configure Database Mail in SQL Server

Tsql need to create sql alerts ( by passing input arguments ).

T-SQL: Create and Test an Alert

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Tips for the T-SQL questions: create a job with three steps. Just put simple dummy statements in the jobs. The select the job in Object Explorer and select Script job as. Then you can work from there.