How Make Mail Sending Job In Sql Server 2005

Step 1: First of all you will have make your email account into your database with given below queries
Run to these quereis as given below

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘sample@Orient.com’,
@description = ‘Maill Account for database mail’,
@email_address = ‘sample@Orient.com’,
@display_name = ‘sample@Orient.com’,
@username = ‘sample@Orient.com’,
@password = ‘orient’,
@mailserver_name = ‘ipaddres’
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘sample@Orient.com’,
@description = ‘Profile used for database mail’
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘sample@Orient.com’,
@account_name = ‘sample@Orient.com’,
@sequence_number = 1
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘sample@Orient.com’,
@principal_name = ‘public’,
@is_default = 1;

Step 2: Now Start Your Sqlserver2005 With Short Command Sqlwb or by programm>sqlserver>managemetnstudio2005
and make new as right click given below:

Step 3: Do as given in image below Make a Job Step By click on step tab

STep 4 : how to make step is given below

Query Which you will paste Into Step 1 into command box
————————————————
DECLARE @strTenderTitle VARCHAR(500)
DECLARE @strTenderNumber VARCHAR(100)
DECLARE @dtTenderClosingDate VARCHAR(50)
DECLARE @dtTenderOpeningDate VARCHAR(50)
DECLARE @intID INT
DECLARE @strEmail_Recipients VARCHAR(MAX)

SET @strTenderTitle = ‘ ‘
SET @strTenderNumber = ‘ ‘
SET @dtTenderClosingDate = ‘ ‘
SET @dtTenderOpeningDate = ‘ ‘
SET @intID = 0
SET @strEmail_Recipients = ‘ ‘

CREATE TABLE #temp(intID INT, strTenderTitle VARCHAR(500), strTenderNumber VARCHAR(100), dtTenderClosingDate VARCHAR(50), dtTenderOpeningDate VARCHAR(50))
INSERT INTO #temp SELECT DISTINCT TOP (100) PERCENT intID, strTenderTitle, strTenderNumber, dtTenderClosingDate, dtTenderOpeningDate FROM dbo.tbl_TenderMaster WHERE (dtTenderClosingDate < GETDATE()) AND (isActivated 1)

WHILE (SELECT COUNT(strTenderTitle) FROM #temp) > 0
BEGIN
SELECT DISTINCT TOP(1) @intID = intID, @strTenderTitle = strTenderTitle, @strTenderNumber = strTenderNumber, @dtTenderClosingDate = dtTenderClosingDate, @dtTenderOpeningDate = dtTenderOpeningDate FROM #temp

SELECT TOP (100) PERCENT @strEmail_Recipients = @strEmail_Recipients + dbo.tbl_Vender.strEmail + ‘;’
FROM dbo.tbl_Vender INNER JOIN
dbo.tbl_VenderProduct ON dbo.tbl_Vender.intId = dbo.tbl_VenderProduct.intVenderId INNER JOIN
dbo.tbl_TenderMaster ON dbo.tbl_VenderProduct.intProductId = dbo.tbl_TenderMaster.intRelatedProductId
WHERE (dbo.tbl_TenderMaster.intID = @intID)

PRINT @strEmail_Recipients

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N’

‘ + N” +N” +
N” +
N’ ‘ +
N” +
N” +
N’
‘ +N” +
N” +
N’

‘ +
N’

‘ +
N’REMAINDER:- TENDER CLOSING

‘ +
N’

‘ +
N’ ‘ +
N’

‘ + N” +N” +
N” +
N’
‘ +N” +
N” +
N’
‘ +N” +
N” +
N’
‘ +N” +
N” +
N’
‘ +N” +
N” +
N’

‘ +
N’

‘ +
N’Dear Vendor/Customer,’ +
N’

‘ +
N’

‘ +
N’ ‘ +
N’
‘ +
N’

‘ +
N’As you know very well that, our tender ‘+@strTenderTitle+’ under reff. no. ‘+@strTenderNumber+’‘ +
N’ was opened on ‘+@dtTenderOpeningDate+’. And now it`s going to be closed on ‘+@dtTenderClosingDate+’.’ +
N’ You are respectfully requested to float your best quotation for this tender before’ +
N’tender to be closed.

‘ +
N’

‘ +
N’Please ignore this mail, if you already had applied for this tender.

‘ +
N’

‘ +
N’ ‘ +
N’
‘ +
N’you get this mail because your are registered with our portal. please click here’ +
N’to login into your account.http://ipaddres/tenderportal‘ +
N’
‘ +
N’feel free to contact us at etender@owmnahar.com‘ +
N’for any furthur enquiry’ +
N’

‘ +
N’

‘ +
N” +
N’ ‘ +
N” +
N” +
N’
‘ +N” +
N’ ‘ +
N” +
N” +
N’ ‘ +
N” +
N” +
N’ ‘ +
N” +
N” +
N’

exec msdb.dbo.sp_send_dbmail
@recipients = @strEmail_Recipients,
@subject = ‘Mail testing’,
@body = @tableHTML,
@body_format = ‘HTML’;

–PRINT @tableHTML

DELETE TOP(1) FROM #temp
END
drop table #temp
————————————————


Note : Remember this for this don’t forget to make your sampledb named database and tbltendermaster table into your database whose queries are given below
USE [master]
GO
/****** Object: Database [SampleDb] Script Date: 10/09/2010 10:39:17 ******/
CREATE DATABASE [SampleDb] ON PRIMARY
( NAME = N’SampleDb’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SampleDb.mdf’ , SIZE = 6336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’SampleDb_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SampleDb_log.LDF’ , SIZE = 768KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’SampleDb’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [SampleDb].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [SampleDb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SampleDb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SampleDb] SET ARITHABORT OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SampleDb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SampleDb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SampleDb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SampleDb] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SampleDb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SampleDb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SampleDb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SampleDb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SampleDb] SET ENABLE_BROKER
GO
ALTER DATABASE [SampleDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SampleDb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SampleDb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SampleDb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SampleDb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SampleDb] SET READ_WRITE
GO
ALTER DATABASE [SampleDb] SET RECOVERY FULL
GO
ALTER DATABASE [SampleDb] SET MULTI_USER
GO
ALTER DATABASE [SampleDb] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SampleDb] SET DB_CHAINING OFF

Queries For Table
USE [SampleDb]
GO
/****** Object: Table [dbo].[tbl_TenderMaster] Script Date: 10/09/2010 10:40:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TenderMaster](
[intID] [int] NOT NULL,
[intRelatedDepartmentId] [int] NULL,
[intPublishedByPersonID] [int] NULL,
[intRelatedProductId] [int] NULL,
[intAwardedPersonId] [int] NULL,
[intUnitOfMeasurementId] [int] NULL,
[intAlternateUnitOfMeasurement] [int] NULL,
[intTenderTypeId] [int] NULL,
[fltProductQty] [float] NULL,
[strDocumentName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strTenderNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strTenderTitle] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strTenderShortDesc] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strTenderFullDesc] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dtTenderOpeningDate] [datetime] NULL,
[dtTenderClosingDate] [datetime] NULL,
[btTenderAllocationStatus] [bit] NULL,
[btTenderReleasedStatus] [bit] NULL,
[isActivated] [bit] NULL,
[dtPublishingDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

And Insert Some Record Into Your Database As Given Below Into Picture
INSERT INTO [SampleDb].[dbo].[tbl_TenderMaster]
([intID]
,[intRelatedDepartmentId]
,[intPublishedByPersonID]
,[intRelatedProductId]
,[intAwardedPersonId]
,[intUnitOfMeasurementId]
,[intAlternateUnitOfMeasurement]
,[intTenderTypeId]
,[fltProductQty]
,[strDocumentName]
,[strTenderNumber]
,[strTenderTitle]
,[strTenderShortDesc]
,[strTenderFullDesc]
,[dtTenderOpeningDate]
,[dtTenderClosingDate]
,[btTenderAllocationStatus]
,[btTenderReleasedStatus]
,[isActivated]
,[dtPublishingDate])
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,)
as this insert 10 records into your table with data difference
query for vendor product table

CREATE TABLE [dbo].[tbl_VenderProduct](
[intId] [int] IDENTITY(1,1) NOT NULL,
[intVenderId] [int] NULL,
[intProductId] [int] NULL,
[dtDatetime] [datetime] NULL,
[btIsVerify] [bit] NULL,
CONSTRAINT [PK_tbl_VenderProduct] PRIMARY KEY CLUSTERED
(
[intId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_VenderProduct] WITH CHECK ADD CONSTRAINT [FK_tbl_VenderProduct_tbl_Product] FOREIGN KEY([intProductId])
REFERENCES [dbo].[tbl_Product] ([intProductId])
GO
ALTER TABLE [dbo].[tbl_VenderProduct] WITH CHECK ADD CONSTRAINT [FK_tbl_VenderProduct_tbl_Vender] FOREIGN KEY([intVenderId])
REFERENCES [dbo].[tbl_Vender] ([intId])
Query For Product Table
CREATE TABLE [dbo].[tbl_Product](
[intProductId] [int] IDENTITY(1,1) NOT NULL,
[intUnitOfMeasurementId] [int] NULL,
[intAlternateUnitOfMeasurement] [int] NULL,
[strProductName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dtDatetime] [datetime] NULL,
[btIsActive] [bit] NULL,
[isSpecificationRequired] [bit] NULL,
CONSTRAINT [PK__tbl_Product__17F790F9] PRIMARY KEY CLUSTERED
(
[intProductId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Product] WITH CHECK ADD CONSTRAINT [FK_tbl_Product_tbl_UnitOfMeasurement] FOREIGN KEY([intUnitOfMeasurementId])
REFERENCES [dbo].[tbl_UnitOfMeasurement] ([intID])
GO
ALTER TABLE [dbo].[tbl_Product] WITH CHECK ADD CONSTRAINT [FK_tbl_Product_tbl_UnitOfMeasurement1] FOREIGN KEY([intAlternateUnitOfMeasurement])
REFERENCES [dbo].[tbl_UnitOfMeasurement] ([intID])
Query for Unit of Measurement table
CREATE TABLE [dbo].[tbl_UnitOfMeasurement](
[intID] [int] IDENTITY(1,1) NOT NULL,
[strName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_UnitOfMeasurement] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Remember to insert some demo records in these tables because these are relation table.

Step 5: Now Make A Maintenace Plan As Given Below

Step 6: Now Insert Execute Sql Server Job Task Into Maintenance Plan

Step 7: Now Right Click on That and Click on Edit option then check to your job
and save him it will give you connection error click ok and Right Click on maintenace plan then click on option execute and test if success then it means it is right and if failure then their you did something mistake

STep 8:If you want to Test your Job then Right Click on your Job and Test by Click on Start Job option and check out your email addres you will get new mails’
Be happy and Enjoy in programming World!!
Whole Query Of Job
copy and paste and create above job if facing problem in doing practicle
——————————————–
USE [msdb]
GO
/****** Object: Job [ABC] Script Date: 10/09/2010 11:00:29 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 10/09/2010 11:00:29 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’ABC’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’This Is Sample Mail job’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
/****** Object: Step [Send Mail Step 1] Script Date: 10/09/2010 11:00:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Send Mail Step 1′,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’DECLARE @strTenderTitle VARCHAR(500)
DECLARE @strTenderNumber VARCHAR(100)
DECLARE @dtTenderClosingDate VARCHAR(50)
DECLARE @dtTenderOpeningDate VARCHAR(50)
DECLARE @intID INT
DECLARE @strEmail_Recipients VARCHAR(MAX)

SET @strTenderTitle = ” ”
SET @strTenderNumber = ” ”
SET @dtTenderClosingDate = ” ”
SET @dtTenderOpeningDate = ” ”
SET @intID = 0
SET @strEmail_Recipients = ” ”

CREATE TABLE #temp(intID INT, strTenderTitle VARCHAR(500), strTenderNumber VARCHAR(100), dtTenderClosingDate VARCHAR(50), dtTenderOpeningDate VARCHAR(50))
INSERT INTO #temp SELECT DISTINCT TOP (100) PERCENT intID, strTenderTitle, strTenderNumber, dtTenderClosingDate, dtTenderOpeningDate FROM dbo.tbl_TenderMaster WHERE (dtTenderClosingDate < GETDATE()) AND (isActivated 1)

WHILE (SELECT COUNT(strTenderTitle) FROM #temp) > 0
BEGIN
SELECT DISTINCT TOP(1) @intID = intID, @strTenderTitle = strTenderTitle, @strTenderNumber = strTenderNumber, @dtTenderClosingDate = dtTenderClosingDate, @dtTenderOpeningDate = dtTenderOpeningDate FROM #temp

SELECT TOP (100) PERCENT @strEmail_Recipients = @strEmail_Recipients + dbo.tbl_Vender.strEmail + ”;”
FROM dbo.tbl_Vender INNER JOIN
dbo.tbl_VenderProduct ON dbo.tbl_Vender.intId = dbo.tbl_VenderProduct.intVenderId INNER JOIN
dbo.tbl_TenderMaster ON dbo.tbl_VenderProduct.intProductId = dbo.tbl_TenderMaster.intRelatedProductId
WHERE (dbo.tbl_TenderMaster.intID = @intID)

PRINT @strEmail_Recipients

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N”

” + N”” +N”” +
N”” +
N” ” +
N”” +
N”” +
N”
” +N”” +
N”” +
N”

” +
N”

” +
N”REMAINDER:- TENDER CLOSING

” +
N”

” +
N” ” +
N”

” + N”” +N”” +
N”” +
N”
” +N”” +
N”” +
N”
” +N”” +
N”” +
N”
” +N”” +
N”” +
N”
” +N”” +
N”” +
N”

” +
N”

” +
N”Dear Vendor/Customer,” +
N”

” +
N”

” +
N” ” +
N”
” +
N”

” +
N”As you know very well that, our tender ”+@strTenderTitle+” under reff. no. ”+@strTenderNumber+”” +
N” was opened on ”+@dtTenderOpeningDate+”. And now it`s going to be closed on ”+@dtTenderClosingDate+”.” +
N” You are respectfully requested to float your best quotation for this tender before” +
N”tender to be closed.

” +
N”

” +
N”Please ignore this mail, if you already had applied for this tender.

” +
N”

” +
N” ” +
N”
” +
N”you get this mail because your are registered with our portal. please click here” +
N”to login into your account.http://OrientDeveloper.com” +
N”
” +
N”feel free to contact us at etender@owmnahar.com” +
N”for any furthur enquiry” +
N”

” +
N”

” +
N”” +
N” ” +
N”” +
N”” +
N”
” +N”” +
N” ” +
N”” +
N”” +
N” ” +
N”” +
N”” +
N” ” +
N”” +
N”” +
N”

exec msdb.dbo.sp_send_dbmail
@recipients = @strEmail_Recipients,
@subject = ”Mail testing”,
@body = @tableHTML,
@body_format = ”HTML”;

–PRINT @tableHTML

DELETE TOP(1) FROM #temp
END
drop table #temp’,
@database_name=N’SampleDb’,
@flags=0
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s