Notification --Email SP Results in SQL Server

 --Notification --Email SP Results

--https://www.sqlservercentral.com/scripts/email-sp-results

This piece of code will allow you to add a stored procedure name as a parameter, and will then take the output results of the procedure and email it to an address of your choosing, automatically formatting the results into an HTML table embedded in the email for clear and easy reading, or alternatively as an attachment.


Regretfully, there are a few limitations which I will eventually get around to fixing when I am interested in this again, namely:


Uses a static 100 length on the output columns instead of dynamically applying a length by referencing the source data

Cannot use procedures that have a blank column aliased ie.SELECT  ' ' as Store FROM table.

Requires Ad Hoc Distributed Queries server setting to be enabled.

Change cursors into tally or alternate loop

Customization of colours, table length, font etc.

It is executed as follows:


EXEC DBAdmin.[dbo].[dba_EmailProcResults] @vcProcName='sp_lock'


,@vcDatabaseName='master'


, @vcRecipients='<UrEmailHere>'


, @vcDynamicSubject = 'Log Backup Check'

=============================================================

 USE [tempdb]

GO

CREATE PROC [dbo].[dba_EmailProcResults]  --'dba_CheckLogbackups','DBAdmin', 'nicholaswilliams@', @vcDynamicSubject = 'Log Backup Check'

@vcProcName VARCHAR(1000)

,@vcDatabaseName VARCHAR(100)

,@vcRecipients VARCHAR(1000)

,@vcSchemaName VARCHAR(100) = NULL

,@bAttachQueryAsFile BIT = 0

,@vcDynamicSubject VARCHAR(100) = 'Query results'


AS

/*

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

*/

/*

Author: Nicholas Williams

Date: 20140519

Desc: Uses a proc as in input and email's the results.


Note: still plenty to update...

Procs cant reference non existant objects, even if logic would prevent them from being used ie. conditional if clause refering to non-existent table, as the remote call seems to force a parse on the query.

yet to make the table columns "smart" in terms of data lengths.

Requires Distributed Queries :/

add in a '?' reference help parameter

Cannot handle '' or blank string columns aliased as an actual column... need to fix.


@attach_query_result_as_file = 1,

    @query_attachment_filename='filename.csv',

*/

SET NOCOUNT ON

DECLARE

@vcServerName VARCHAR(100)

,@vcString VARCHAR(325)

,@vcColumnName VARCHAR(200)

,@nvcTable1 NVARCHAR(MAX)

,@nvcQuery NVARCHAR(MAX)

,@nvcBody NVARCHAR(MAX)

,@vcHeader VARCHAR(1000)

,@vcTrailer VARCHAR(1000)


DECLARE @MyTable TABLE

(

string NVARCHAR(MAX)

)


IF (@vcSchemaName) IS NULL

BEGIN

SET @vcSchemaName = 'dbo'

PRINT 'Warning: Schema name was null, setting it to dbo as a default.'

END


SET @vcServerName = 'Server='+(SELECT @@SERVERNAME)+';Trusted_Connection=yes;'

SELECT @vcString = 'EXEC ['+@vcDatabaseName+'].['+@vcSchemaName+'].'+(REPLACE(@vcProcName, '''',''''''))+''


EXEC ('

SELECT * INTO ##MyTempTable 

FROM OPENROWSET(''SQLNCLI'', '''+@vcServerName+''','''+@vcString+''')

')

IF (SELECT COUNT(1) FROM ##MyTempTable) >=1

BEGIN


IF @bAttachQueryAsFile <> 1

BEGIN

SET @vcHeader = '

<html><style>table{font-family:Arial;font-size:10;background=black;

color:Black;}html, body, title, 

head{font-family:Arial;font-size:10;background:white;color:Black;}th{font-family:Arial;font-size:15;background=orange;color:Black;}

{font-family:Arial;font-size:10;color:Black;background:White;align:left;valign:top;}</style><body><p><font face="Arial" size="2">Greetings,<br><br>Please see below.<br></font></p>'

SET @vcTrailer = '</body></html>'

--SELECT * FROM ##MyTempTable

SET @nvcTable1 = 

'<br>'+

'<table width=600 border=1 cellspacing=0 cellpadding=5>'+

'<tr style="background-color: White;">'

SET @nvcQuery = '

SELECT CAST((SELECT '

DECLARE column_cursor SCROLL CURSOR FOR

SELECT name FROM tempdb.sys.columns

WHERE object_id = 

(

SELECT id FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID('tempdb..##MyTempTable')

)

/*

Need to put in a check to ensure that the result set is less than 4MB, so that it can be emailed.

*/

OPEN column_cursor

/*

Note: The odd use of the double fetch statement here is to force the force record of the fetch to be formatted differently, ie. no "," for the first record of the select.

hmm... now that I have changed how the select statement is used, i dont think i need it in the cursor anymore... change later.

*/

   FETCH FIRST FROM column_cursor

   INTO @vcColumnName


    SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

['+@vcColumnName+'] AS ''td'','''''

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'

   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


WHILE @@FETCH_STATUS = 0

BEGIN 

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'


SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

,['+@vcColumnName+'] AS ''td'','''''


   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


END

SET @nvcTable1 = @nvcTable1 +

'</tr>'

SET @nvcQuery = @nvcQuery+ 

'

FROM ##MyTempTable

FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))'

SET @vcTrailer= '</table><br><br><font face="Arial" size="2"><p>Regards</p><font face="Arial" size="2"><p>UrCompanyName<br></font><font size="1">Microsoft SQL Sever<br><br>Sender info:<br>'+'User - '+SYSTEM_USER+'<br>'+'Procedure - '+@vcProcName+'<br>'+'Database - '+@vcDatabaseName+'<br>'+'Server - '+isnull(@@SERVERNAME,'')+'<br></font></p>'

CLOSE column_cursor

DEALLOCATE column_cursor


INSERT INTO @MyTable

EXEC (@nvcQuery)


SELECT @nvcQuery = String FROM @MyTable

SELECT @nvcBody = @vcHeader+@nvcTable1+@nvcQuery+@vcTrailer

--SELECT @nvcBody


EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = @nvcBody

,@body_format = 'HTML'  


END

ELSE

BEGIN

EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = 'Please View attached file for query results.'

,@body_format = 'Text' 

,@attach_query_result_as_file = 1

,@query_attachment_filename = 'QueryResults.xls'

,@query_result_separator = ' '

,@query_result_no_padding = 0

,@query = 'SET NOCOUNT ON SELECT * FROM ##MyTempTable SET NOCOUNT OFF'

END


END

DROP TABLE ##MyTempTable

SET NOCOUNT OFF


/*

set a variable for the table width and the column widths.

add to each length for table for each iteration of the cursor.

look at sys.columns table for data type of the columns to manage table size effectively.

-- td = tranDate,''''

*/

SET NOCOUNT OFF


No comments:

T-SQL Script to get SQL Data & Log Files Free Space & Space Used of all databases

 --TSQL Script to get SQL Data & Log Files Free Space & Space Used of all databases


Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 

       filName varchar(520) NULL, sizeMB decimal(10,2) null, 

       usedMB decimal(10,2) null, freeMB decimal(10,2) null, 

       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMB, usedMB)

Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 

Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo Set

freeMB = sizeMB - usedMB,

pcntUsed = (usedMB/sizeMB)*100,

pcntFree = ((sizeMB-usedMB)/sizeMB)*100


select * from #dbInfo 

--where dbname in ('works')

--where filname like '%G:\%' order by freemb desc -- Put drive name here for files residing on specific drive

drop table #dbInfo


No comments:

Enable the SQL Server Agent in Linux

 sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 

sudo systemctl restart mssql-server


Microsoft Article

No comments:

SQLServerAgent is not currently running

……………………………………………………………………………………………………………………………………………………………………………….

 Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”

 If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

……………………………………………………………………………………………………………………………………………………………………………….

1.Click Start, click Run, type Services.msc, and then click OK.


2.In the Services window, locate the SQL Server Agent service.

3.Make sure that the value of the Status column of the SQL Server Agent service is Running.

then 


EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXEC sp_configure ‘allow updates’, 0;

RECONFIGURE;

EXEC sp_configure ‘Agent XPs’, 1;

RECONFIGURE;

GO

No comments:

--create a tample table to gather the information of error log files in SQL Server

 --create a tample table to gather the information of error log files

CREATE TABLE #ErrorLog
(
       Archieve INT,
       Dt DATETIME,
       FileSize INT
)
GO

INSERT INTO #ErrorLog
EXEC xp_enumerrorlogs
GO

--delete all the old log files if the size of all the log files is larger than 30GB
DECLARE @i int = 1;                                                  
DECLARE @Log_number int;
DECLARE @Log_Max_Size int = 40*1024; --here is the max size (M) of all the error log files we want to keep, change the value according to your requirement
DECLARE @SQLSTR VARCHAR(1000);

SET @Log_number = (SELECT COUNT(*) FROM #ErrorLog);

IF (SELECT COUNT(FileSize/1024/1024) FROM #ErrorLog) >= @Log_Max_Size
BEGIN
       WHILE @i <= @Log_number
              BEGIN
                     SET @SQLSTR = 'DEL C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG.' + CONVERT(VARCHAR,@i);
                     EXEC xp_cmdshell @SQLSTR;
                     SET @i =@i + 1;
              END
END

DROP TABLE #ErrorLog
No comments:
Subscribe to: Posts (Atom)