Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for June, 2014

Alert email sending

Posted by Simon Cho on 06/30/2014

Email send when @objName has data. @objName should include DB name as well.
Ex) @objName = ‘DBA.dbo.vw_Job_Status’

CREATE PROCEDURE [dbo].[dmp_send_email_HTML_format]
@objName VARCHAR(255)
, @Subject VARCHAR(255)
, @PreDescription varchar(8000) = NULL
, @Recipients VARCHAR(8000)
, @importance VARCHAR(255) = ‘High’
, @profile VARCHAR(255) = ‘Public_Profile’ –eMail profile name
as
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*
Return value @r
10001 : object is wrong
10002 : column is wrong
0 : Done or Nodata.
*/

DECLARE @tableHTML NVARCHAR(max)
, @HTML_TH NVARCHAR(max)
, @HTML_TD NVARCHAR(max)
, @sql NVARCHAR(4000)
, @param NVARCHAR(4000)
, @DBName VARCHAR(255)
, @rowcnt INT
, @r INT

DECLARE @tbl_datacheck TABLE (i INT)

SET @DBName = PARSENAME(@objName,3)

SET @r = -1

IF OBJECT_ID(@objName) IS NULL BEGIN
SET @r = 10001
GOTO ERROR;
END

IF @DBName = ” OR @DBName IS NULL BEGIN
SET @DBName = DB_NAME()
END
SET @sql = ‘select top 1 1 from ‘+ @objName

INSERT INTO @tbl_datacheck
EXEC (@sql)
SET @rowcnt = @@ROWCOUNT

IF @rowcnt <=0 BEGIN
SET @r = 0
GOTO ERROR;
END

IF OBJECT_ID('tempdb.dbo.#tbl_column') IS NOT NULL
DROP TABLE #tbl_column
CREATE TABLE #tbl_column (idx INT IDENTITY(1,1), NAME VARCHAR(255))

set @sql = '
INSERT #tbl_column
SELECT name
FROM '+@DBName+'.SYS.syscolumns
WHERE ID = OBJECT_ID(@objName)
ORDER BY colorder
'
set @param = ' @objName varchar(255) '
exec sp_executesql @sql, @param, @objName

SET @rowcnt = @@ROWCOUNT
–PRINT @rowcnt

IF @rowcnt = 0 BEGIN
SET @r = 10002
GOTO ERROR;
END

SELECT @HTML_TH =
CAST ((SELECT th = isnull(NAME,' ')
FROM #tbl_column
FOR XML PATH(''), TYPE
) AS NVARCHAR(MAX) )

–print @HTML_TH
SET @HTML_TD = ''
SELECT @HTML_TD = @HTML_TD + 'td = isnull('+NAME+','' ''), '''', '
FROM #tbl_column

SET @HTML_TD = SUBSTRING(@HTML_TD, 1, LEN(@HTML_TD)-1)

SET @sql = 'SELECT @HTML_TD = CAST ((SELECT '+ REPLACE(@HTML_TD,'','''') +'
FROM '+@objName+'
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )
'

–PRINT @sql

EXEC sp_executesql @sql, N' @HTML_TD NVARCHAR(MAX) OUTPUT ', @HTML_TD OUTPUT

–PRINT @HTML_TD

set @Recipients = isnull(@Recipients,'NowcomDBDevTeam@nowcom.com')

set @subject = @@servername +' – ' + convert(varchar(255), getdate(), 101) + ' ' + convert(varchar(5), getdate(), 108)+' ' + @Subject

SET @tableHTML = '

h1{
font-size:17px;
}
body{
font-size:15px;
}

table{
border-collapse:collapse;
border:1px solid black;
font-size:12px;
}

table th{
font:bold;
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px;
background-color:lightblue;
font-size:12px;
}

table td{
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px
font-size:12px;
}

‘+ @subject +’
‘+ ISNULL(@PreDescription,”) + ‘

‘+ @HTML_TH +’

‘
+ @HTML_TD

set @tableHTML = @tableHTML + ‘

‘

SELECT @tableHTML = ‘From Server :’ + ISNULL(@@SERVERNAME, ”) + char(13) + char(10) + ISNULL(@tableHTML, ”);

EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile
, @body_format = ‘HTML’
, @recipients = @Recipients
, @importance = @importance
, @copy_recipients =”
, @subject = @subject
, @body = @tableHTML
IF @@error 0 BEGIN
RAISERROR(‘Error in dmp_send_email_HTML_format. Failed to send Database Mail.’, 16, 1);
END
RETURN 0
ERROR:
return @r
END
Go

Posted in Common | Tagged: , | Leave a Comment »

Selecting Rows Randomly from a Large Table

Posted by Simon Cho on 06/12/2014

http://msdn.microsoft.com/en-us/library/cc441928.aspx

SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10 — Percent

SELECT * FROM Table1 TABLESAMPLE (1 percent) ORDER BY NEWID()

Posted in Common | Tagged: , , | Leave a Comment »

SQL Server Job Agent – Invoke job sequence.

Posted by Simon Cho on 06/11/2014

1. [SQLAgent – Job invocation engine] : execute it based on active schedule and job at scheduled time.

 

exec sp_executesql N'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3',N'@P1 int,@P2 uniqueidentifier,@P3 int',1,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

 

2. [SQLAgent – Job Manager] : Update start time. Check Permission. Get Jobstep. And execute each step

exec sp_executesql N'DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = @P3 and session_id = @P4',N'@P1 int,@P2 int,@P3 uniqueidentifier,@P4 int',20140611,144430,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1',N'@P1 nvarchar(128)',N'sa'

exec sp_executesql N'EXECUTE msdb.dbo.sp_help_jobstep @job_id = @P1',N'@P1 uniqueidentifier','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT,
                                              'NO_TEST'

--Execute each step

3. [SQLAgent – TSQL JobStep(Job 0x…) or SSIS JobStep(Job 0x…)] : Execute each step.

4. [SQLAgent – Job Manager] : Update job history and status.

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',1,0,0,1,20140611,144430,0,0,0,0,0,14,N'Executed as user: NT AUTHORITY\NETWORK SERVICE. The step succeeded.'

5. [SQLAgent – Update job activity] – Update next schedule.

exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = @P3 AND job_id = @P4',N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',20140611,144440,14,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

6. [SQLAgent – Job Manager] – Update last run time

exec sp_executesql N'UPDATE msdb.dbo.sysjobservers SET last_run_date = @P1, last_run_time = @P2, last_run_outcome = @P3, last_outcome_message = @P4, last_run_duration = @P5 WHERE (job_id = @P6) AND (server_id = 0)',N'@P1 int,@P2 int,@P3 int,@P4 nvarchar(4000),@P5 int,@P6 uniqueidentifier',20140611,144430,1,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).',0,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',0,0,0,1,20140611,144430,0,0,0,0,0,14,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).'

Done.

Posted in Common | Tagged: , , | Leave a Comment »