search

pillajagadishwar / Scripts

0 stars 1 forks source link

New SP Correct #9

Open pillajagadishwar opened 1 year ago

pillajagadishwar commented 1 year ago

ALTER PROCEDURE [dbo].[DiskAlertOpsgenie] AS BEGIN DECLARE @RowCount INT

-- Execute your select query here and store the count in @RowCount SELECT @RowCount = COUNT(*) FROM tbl_diskreport WHERE isLatest = 1 and FreeSpacePercent < = 15

-- Check if the count is greater than or equal to 1 IF @RowCount >= 1 BEGIN DECLARE @Subject NVARCHAR(255) = 'Drive space alert' DECLARE @Body NVARCHAR(MAX) = 'Raise a Req immediately' --'Drive Space Alert ' + CAST(@RowCount AS NVARCHAR(10))

-- Replace 'YourEmailAddress' with the actual recipient's email address
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Server Admin',
    @recipients = 'jagadish.pilla@man.com',
    --;sql_alerts@mangroup.opsgenie.net',
    --@body_format = 'HTML' ,
    @subject = @Subject,
    @attach_query_result_as_file = 1,
    @body = @Body,
    @query = 'SELECT servername, diskname, capacitygb, freespacegb, freespacepercent, report_date FROM [DBAdmin].[dbo].tbl_diskreport
    WHERE diskname like 'F:\SQL%' and isLatest=1 and FreeSpacePercent < 20';

END END

pillajagadishwar commented 7 months ago

CREATE PROCEDURE staging.ni_load_prices_dom_legacy

@security_id_start BIGINT = NULL,
@security_id_end BIGINT = NULL,
@start_date DATETIME = NULL,
@end_date DATETIME = NULL
AS
/Begin parameter logging/
DECLARE @loggingParameters NVARCHAR(MAX)
DECLARE @loggingValues NVARCHAR(MAX)
DECLARE @loggingDatabaseName SYSNAME
DECLARE @loggingProcedureSchema SYSNAME
DECLARE @loggingProcedureName SYSNAME
DECLARE @callId INT
SELECT
@loggingDatabaseName = SPECIFIC_CATALOG,
@loggingProcedureSchema = SPECIFIC_SCHEMA,
@loggingProcedureName = SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECT_ID(ROUTINE_SCHEMA + '.' + ROUTINE_NAME) = @@PROCID
SET @loggingParameters = '@security_id_start|@security_id_end|@start_date|@end_date'
SET @loggingValues = REPLACE(ISNULL(CAST(@security_id_start AS NVARCHAR(MAX)), 'XNULLX'),'|','') +'|'+REPLACE(ISNULL(CAST(@security_id_end AS NVARCHAR(MAX)), 'XNULLX'),'|','') +'|'+REPLACE(ISNULL(CAST(@start_date AS NVARCHAR (MAX)), 'XNULLX'),'|','') +'|'+REPLACE(ISNULL(CAST(@end_date AS NVARCHAR(MAX)), 'XNULLX'),'|','')
EXEC log.log_procedure_call @databaseName = @loggingDatabaseName, @procedureName = @loggingProcedureName, @procedureSchema = @loggingProcedureSchema, @loggingParameters = @loggingParameters, @loggingValues = @loggingValues, @callId = @callId OUTPUT
/Endof parameter logging/
BEGIN
set nocount on
set xact_abort on

DECLARE @max_price_date datetime
declare @error_msg varchar(max)
declare @NOW datetime
declare @PRC_DEFAULT_LOOKBACK int
declare @BATCH_SIZE int
declare @PROCEDURE_NAME varchar(50) = 'staging.ni_load_prices_dom_legacy'
DECLARE @PREV_BIZ_DAY datetime

set @NOW = getdate()
set @PRC_DEFAULT_LOOKBACK = convert(int, util.get_config_value('PRC_DEFAULT_LOOKBACK', null))
set @BATCH_SIZE = convert(int, util.get_config_value('PRC_BATCH_SIZE', null))
SET @PREV_BIZ_DAY = (SELECT prev_business_date FROM ref.ni_calendar_date WHERE calendar_code = 'INTL' AND obsdate = CONVERT(DATE, GETDATE()))

-- if @end_date is null, then set it to today's date
set @end_date = isnull(@end_date, convert(datetime, floor(convert(float, @NOW))))

-- set the start_date
set @start_date = isnull(@start_date, dateadd(day, abs(@PRC_DEFAULT_LOOKBACK) * -1, @end_date))

print @PROCEDURE_NAME + '<'+ convert(varchar, getdate(), 121) + '>' +': begin'
print @PROCEDURE_NAME + '<'+ convert(varchar, getdate(), 121) + '>' +': @PRC_DEFAULT_LOOKBACK=' + convert(varchar, @PRC_DEFAULT_LOOKBACK)