/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Thursday, December 10, 2015

Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format

Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format


I am just providing the steps that are enough to give you the basic undrstanding to create HTML Format


-- Creating HTML Format Using Stored Procedures for sending email from database the following Stored Procedures Required


-- 1) Create DataSourceCheck stored procedures

CREATE PROCEDURE [dbo].[DataSourceCheck]

@dataSource varchar (100) = NULL,

@db varchar(50) = NULL output,

@table varchar(100) = NULL output

AS

BEGIN

DECLARE

@buffer varchar(100),

@object varchar(100),

@objectId bigint,

@schema varchar(50),

@rcd_cnt int,

@tableHtml varchar(200),

@sql nvarchar(1000)

SET @buffer = @dataSource;

--cannot accesss a local temp table. Return.

IF SUBSTRING (@buffer, 1, 1) = '#' and SUBSTRING (@buffer, 2, 1) <> '#'

BEGIN

--use LEFT 25 to make sure the local temp table name isn't too long for the @table varchar(100) variable.

SET @table = '
Table ' + LEFT (@dataSource, 25) + ' is a local temp table. Must use a global temp or permanent table.
';

RETURN;

END;

--set up the object name in the right format so you can check the OBJECT_ID

ELSE IF (SUBSTRING (@buffer, 1, 2) = '##')

BEGIN

SET @db = 'tempdb';

SET @table = @dataSource;

SET @object = @db + '..' + @table; --need to include tempdb so OBJECT_ID finds the temp table

END;

ELSE

BEGIN

--deal with schema

SET @db = SUBSTRING (@buffer, 1, charindex ('.', @buffer) - 1);

SET @buffer = replace (@buffer, @db + '.', '');

IF SUBSTRING (@buffer, 1, 1) = '.'

BEGIN

SET @schema = '..';

SET @buffer = replace (@buffer, '.', '');

END

ELSE

BEGIN

SET @schema = SUBSTRING (@buffer, 1, charindex ('.', (@buffer)) - 1);

SET @buffer = replace (@buffer, @schema + '.', '');

END

SET @table = @buffer;

SET @object = @dataSource;

END;

--does our data source exist? Check the object_id. If object does not exist, return.

SET @objectId = OBJECT_ID (@object, 'U');

IF @objectId is NULL

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' does not exist or is improperly qualified.
';

RETURN;

END;

--we have a valid data source. Check that it has rows and notify if empty.

SET @sql = 'SELECT @rcd_cnt = count(*) from ' + @dataSource;

EXEC master.sys.sp_executesql @sql, N'@rcd_cnt int OUTPUT', @rcd_cnt OUTPUT;

IF @rcd_cnt = 0

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' is empty.
';

RETURN;

END;

END


-- 2) Create Stored Procedures For HtmlTable


CREATE PROCEDURE [dbo].[HtmlTable]

@data_source varchar (100) = NULL,

@tableHTML varchar(max) OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE

@db varchar(50),

@table varchar(100),

@cmd varchar(400),

@rcd_cnt int,

@sql nvarchar(1000);

--use procedure DataSourceCheck to see if @data_source is valid

EXEC dbo.DataSourceCheck @data_source, @db output, @table output;

IF @db is NULL --if the data source is not good, @db comes back NULL, and @table holds info as to the problem (either the table does not exist, or it is empty).

BEGIN

SET @tableHtml = @table;

RETURN;

END;


--We have a good table. Use information_schema metadata for table to get column names.

IF OBJECT_ID ('tempdb..##columnNames') IS not null DROP TABLE ##columnNames;

CREATE table ##columnNames (column_name varchar(50), position int identity);


SET @sql = 'USE ' + @db + '; INSERT into ##columnNames SELECT column_name from information_schema.columns where table_name = ''' + @table + ''' order by ordinal_position';

EXEC master.sys.sp_executesql @sql;


--use ##columnNames to create table ##columnPivot with the proper number of fields to hold data

IF OBJECT_ID ('tempdb..##columnPivot') IS not null DROP TABLE ##columnPivot;

CREATE table ##columnPivot (f1 varchar(200));


DECLARE

@i int = 2,

@fieldct int,

@column varchar(50),

@field varchar(200),

@value varchar(100),

@html varchar(max) = '';

SET @fieldct = (SELECT COUNT(*) from ##columnNames);

WHILE @i <= @fieldct --loop through adding a field to ##columnPivot for each column. Max field len is 200.

BEGIN

SET @sql = 'ALTER table ##columnPivot ADD f' + cast (@i as varchar(2)) + ' varchar(200)';

EXEC master.sys.sp_executesql @sql;

SET @i = @i + 1;

END

--##columnPivot is constructed but empty. Columns are named f1, f2, f3, etc

--construct dynamic SQL string that will be executed to populate ##columnPivot

SET @sql = 'INSERT into ##columnPivot SELECT ';

SET @i = 1;

SET @fieldct = (SELECT count(*) from ##columnNames);


WHILE @i <= @fieldct - 1

BEGIN

SET @column = (SELECT top 1 column_name from ##columnNames where position = cast (@i as varchar(2)));

SET @field = 'CAST([' + @column + '] as varchar(200)),';

SET @sql = @sql + @field;

SET @i = @i + 1;

END


SET @column = (SELECT top 1 column_name from ##columnNames where position = @fieldct);

SET @field = 'CAST([' + @column + '] as varchar(200)) FROM ' + @data_source;

SET @sql = @sql + @field; --@sql now contains the SQL statement that will insert data from @data_source into ##columnPivot

--execute @sql to insert into ##columnPivot the data from @data_source table

EXEC master.sys.sp_executesql @sql;


--format the output

IF OBJECT_ID ('tempdb..#columns') IS not null DROP TABLE #columns;

--use a copy of ##columnNames, because next steps delete from this table, and ##columnNames data is needed below. Does not need to be a global temp.

SELECT *

into #columns

from ##columnNames

order by position;

SET @fieldct = (SELECT count(*) from #columns);

SET @i = 1;

--create the header row for the table containing column names from the @data_source

WHILE @i <= @fieldct

BEGIN

SET @field = (SELECT top 1 column_name from #columns order by position);

SET @html = @html + '
' + @field + '
';

SET @i = @i + 1;

DELETE from #columns where column_name = @field;

END


SET @html = ' ' + @html + ' '; --now @html contains the header row of the output table


--populate ##columnPivot by working through the data row by row.

ALTER table ##columnPivot add id_key int identity;

DECLARE

@j int = 1,

@fieldcnt int,

@cell varchar(100),

@row varchar(500) = '';

SET @i = 1;

SET @fieldcnt = (SELECT count(*) from ##columnNames);

SET @rcd_cnt = (SELECT count(*) from ##columnPivot);

WHILE @i <= @rcd_cnt --this loop executes one time for each row of data

BEGIN

SET @j = 1;

WHILE @j <= @fieldcnt --this loop executes one time for each column (cell) of data

BEGIN

SET @sql = 'SELECT @value = f' + cast (@j as varchar(2)) + ' from ##columnPivot where id_key = ' + cast (@i as varchar(2));

EXEC master.sys.sp_executesql @sql, N'@value varchar(200) OUTPUT', @value OUTPUT;

SET @cell = '
' + ISNULL (@value, '
') + '
'; --need to use
if the cell is empty

SET @row = @row + @cell;

SET @j = @j + 1;

END

SET @row = ' ' + @row + ' ';

SET @html = @html + @row;

SET @row = '';

DELETE from ##columnPivot where id_key = cast (@i as varchar(2));

SET @i = @i + 1;

END

SET @tableHTML = '
' + @html + '

';

END


_________________________________________________________________________________

Testing With the below given query temp query


CREATE table ##Temptest (test1 int, test2 varchar(20), test3 datetime)

INSERT into ##Temptest values (1, 'Testing1', getdate())

INSERT into ##Temptest values (2, 'Testing2', '05/05/15')

DECLARE @tableHtml varchar(max)

EXEC dbo.HtmlTable

'##foo',

@tableHtml output

PRINT @tableHtml


-- Forcefully want to delete the global temporary table use this

drop table ##Temptest

No comments:

Post a Comment

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago