Configure Database mail using T SQL script

-- using T-SQL

use msdb
declare @profile_id smallint
exec msdb.dbo.sp.sysmail_add_profile_sp
@profile_name=’DATABASEMAIL’,
@description=’DATABASEMAIL’,
@profile_id=@profile_id output;
select @profile_id
--–exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name=’samardeep’,
@email_address=’samardeep.skhera@diaspark.com’,
@display_name=’samardeep’,
@description=’Test Mail’,
@account_id=@account_id output;
select @account_id
/* to update
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1,
@account_name=’test’,
@email_address=’samae_99@gmail.com’,
@display_name=’ElixirCT’,
@description=’test mail’,
@replyto_address=’samae_99@gmail.com’,
@mailserver_name=’smtp.gmail.com’,
@mailserver_type=’SMTP’,
@port=25,
@username=samae_99@gmail.com,
@password=password,
@use_default_credentials=0,
@enable_ssl=0;
exec sysmail_help_account_sp
*/
–select * from sysmail_account exec sysmail_help_account_sp
–exec sysmail_help_profile_sp exec sysmail_help_account_sp
/*
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id=1,
@account_name=’samardeep’,
@sequence_number=1
–or
@account_id=1,
@profile_name=’DATABASEMAIL’,
@sequence_number=2
*/
–exec sysmail_help_profileaccount_sp
–make the profile public
exec sysmail_add_principalprofile_sp
@profile_name=’DATABASEMAIL’,
@profile_id=1,
@is_default=0,
@principal_name=’public’
–exec sysmail_help_principalprofile_sp
exec msdb.dbo.sp_send_dbmail
@profile_name=’DATABASEMAIL’,
@recipients=’samae_99@yahoo.com’,
@subject=’hello…test mail’,
@body=’hi,
h r u’
SELECT * FROM sysmail_allitems


Enjoy

Stored Procedure to Generate Insert Statements with Columns and values for a Table in SQL Server

Requirement-

I wanted to generate Insert statements for all the rows in the table with the column name and their values.

For example,

If the Table name is Emp and with following structure and data

EmpId	EmpName	EmpLastName	Department
1	Amit	Gupta	Technical
2	Sumit	Verma	Functional
3	Ritesh	Sharma	Financial
4	Hitesh	Dubey	Sales

On calling the Stored procedure

GetInsertStatement 'Emp'

It should generate the following

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘1’, ‘Amit’, ’Gupta’, ’Technical’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘2’, ‘Sumit, ’Verma’, ’Functional’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘3’, ‘Ritesh’ ,Sharma’, ’Financial’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘4’, ‘Hitesh’, ’Dubey’, ’Sales’)

Following is the stored procedure to generate the desired result.

NOTE- Currently it includes identity column also in the Insert Statement since that was the requirement, but you can see the other version also without Identity column in Insert statement.



GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetInsertStatement]

(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information
--for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000) --for storing the first half
--of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data
--(VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned
--for respective columns

SET @string='INSERT INTO '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0

BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+
isnull('+@colName+','''')+'''''',''+'
END

ELSE
if @dataType in ('text','ntext') --if the datatype
--is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END

ELSE

IF @dataType = 'money' --because money doesn't get converted
--from varchar implicitly
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+'''''',''+'
END

ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast(convert(varbinary,'+@colName+')
as varchar(6)),''0'')+'''''',''+'
END

ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000) -- provide for the whole query,
-- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query --load and run the built query

CLOSE cursCol
DEALLOCATE cursCol

CTE to delete duplicate rows from a table

Common-table expressions are a very useful new feature in SQL Server 2005. You can use them for recursive queries,

removing duplicates, and even simple looping procedures.

With some crafty TSQL, this is a relatively easy task to do when a primary key defined on the table. Luckily, the

new CTE feature in SQL Server 2005 makes it very easy to remove these duplicates, with or without a primary key.

The script below defines my CTE. I am using a windowing function named DENSE_RANK to group the records together

based on the Product, SaleDate, and SalePrice fields, and assign them a sequential value randomly. This means that

if I have two records with the exact same Product, SaleDate, and SalePrice values, the first record will be ranked

as 1, the second as 2, and so on.

WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(

SELECT Product, SaleDate, SalePrice,Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY

NEWID() ASC)
FROM SalesHistory

)

DELETE FROM SalesCTE WHERE Ranking > 1
Because a CTE acts as a virtual table, I am able to process data modification statements against it, and the

underlying table will be affected. In this case, I am removing any record from the SalesCTE that is ranked higher

than 1. This will remove all of my duplicate records.

To verify my duplicates have been removed, I can review the data in the table, which should now contain 8 records,

rather than the previous 10.

SELECT *FROM SalesHistory