In Progress

DB Mail MS SQL Email alerts in HTML

Convert 10 DB Mail MS SQL Email alerts in HTML.

Example of query result attached:

USE [msdb]

GO

/****** Object: Job [rec] Script Date: 03/14/2012 08:26:31 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/14/2012 08:26:31 ******/

IF NOT EXISTS (SELECT name FROM [url removed, login to view] WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Receivals',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Sent daily at 9pm',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'name', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [SEND] Script Date: 03/14/2012 08:26:31 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SEND',

@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'EXEC msdb.dbo.sp_send_dbmail

@profile_name = ''SQLMAIL'',

@recipients = '''',

@query = ''SELECT top(1000) * FROM [url removed, login to view]'' ,

@subject = ''Perfection Fresh Daily Arrivals '',

@body = ''Daily Arrivals '',

@importance = ''High'',

@query_result_header = 1,

@query_result_separator= ''|'',

@query_result_no_padding = 0,

@attach_query_result_as_file = 1 ;',

@database_name=N'PerfectionFresh',

@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_jobschedule @job_id=@jobId, @name=N'daily dvine sent',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20120220,

@active_end_date=99991231,

@active_start_time=210000,

@active_end_time=235959,

@schedule_uid=N'b2eea343-daf2-474e-b2d4-7fafee98897c'

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:

GO

Skills: HTML, SQL

See more: top query, job mail, job in html, 68 in binary, db transaction, TSQL, Sql 2012, rec, perfection, MS SQL, html mail, db, db query, sql select select, query email, sql email, sql select, html object, convert object step, class convert, step step 1000, script email html, subject email sent, select name, convert object

About the Employer:
( 1 review ) homebush, Australia

Project ID: #1502407

Awarded to:

sikandermandal

Please see PMB.

$400 AUD in 3 days
(14 Reviews)
5.1

8 freelancers are bidding on average $288 for this job

weonlydo

Hi, I am an expert on SQL Server and can do this HTML db mail formatting for you. Please see PM for more details and questions. *update* I have now completed a prototype that handles any query easily.

$250 AUD in 1 day
(35 Reviews)
5.5
intechwebworks

Hello Sir I am intrested in helping you in this project Thankyou

$250 AUD in 1 day
(8 Reviews)
5.3
phpgenin

interested in your project. please see reviews for reference.

$250 AUD in 4 days
(15 Reviews)
5.2
manhpham

I am confident to handle this project for you. Please let me do it. Thanks

$250 AUD in 0 days
(20 Reviews)
4.5
lata5

hi, I can work on this. I have experience of working on such stuff.

$250 AUD in 1 day
(0 Reviews)
0.0
boudilova

I can do it. I have 12 years' experience with MS SQL Server, I did stuff like this before. It will take me about 1 day, may be less and will cost 100$.

$250 AUD in 1 day
(0 Reviews)
0.0
deyparikshit

Hi, Alvicreatives has been long into web application development & designing and development business. The last 5 years, we are working on web based ASP.net / C#, Sqlserver 2005/2008, Ajax, CSS, Flash & SEO proje More

$400 AUD in 10 days
(0 Reviews)
0.0