Mustafa EL-Masry Database mail

How to use Database Mail for sending emails with MSSQL


In the first you must read this Article { https://mostafaelmasry.wordpress.com/2012/01/31/how-to-configure-database-mail-in-sql-server/ }

Sending a test email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Artsi Fartsi…’,

@subject = ‘Blah Blah’,

@recipients = ‘junk@sqlhacks.com’

go

Email sent verification

SELECT CAST(subject AS VARCHAR(20)) AS ‘Subject’, CAST(body AS VARCHAR(20)) AS ‘Body’, send_request_date, CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’, sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;. go

Emailing a query result

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Main’,

@body = ‘Here it is…’,

@subject = ‘SQL query’,

@recipients = ‘junk@sqlhacks.com’,

@execute_query_database = ‘sql911’,

@attach_query_result_as_file = 0,

@query = ‘select sales_date,sales_qty,sales_price from sales;’

go

SELECT CAST(subject AS VARCHAR(17)) AS ‘Subject’,

send_request_date,

CAST(send_request_user AS VARCHAR(20)) AS ‘Sent by’,

CAST(query AS VARCHAR(55)) AS ‘Query’,

sent_account_id, sent_status, sent_date FROM msdb.dbo.sysmail_allitems;

go

Mustafa EL-Masry General topics

Mustafa EL-Masry's avatarDB Cloud TECH

select @@servernameASServerName

Select @@VERSIONAsSQLServerVerjion

go

selectserverproperty(‘Edition’)AsServerEdition

selectserverproperty(‘ProductLevel’)ServerProductLevel

selectserverproperty(‘BuildClrVersion’)BuildVersion

View original post

Mustafa EL-Masry backups, backups

list down all SQL Server backups


Dwenload Script : http://hotfile.com/dl/149375389/c747113/List_Dowen_SQL_Server_Backups.rar.html

/*——————————————————————————————————————————-

— Description    :     This SQL Script will list down all SQL Server backups which were not backup in last 2 days

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.1

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   MAX(bs.backup_finish_date) AS last_db_backup_date,

   DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM    msdb.dbo.backupset bs

WHERE     bs.type = ‘D’

GROUP BY bs.database_name

HAVING      (MAX(bs.backup_finish_date) < DATEADD(hh, – 48, GETDATE()))

UNION

–Databases without any backup history

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   master.dbo.sysdatabases.NAME AS database_name,

   NULL AS [Last Data Backup Date],

   9999 AS [Backup Age (Hours)]

FROM

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset bs

       ON master.dbo.sysdatabases.name  = bs.database_name

WHERE bs.database_name IS NULL AND master.dbo.sysdatabases.name <> ‘tempdb’

ORDER By bs.database_name

————————————————————————————————————————————————

/*——————————————————————————————————————————-

Description    :     This SQL Script will list down all SQL Server backups which were taken in last 10 days with details

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.0

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   bs.backup_start_date,

   bs.backup_finish_date,

   bs.expiration_date,

   CASE bs.type

       WHEN ‘D’ THEN ‘Full Database’

       WHEN ‘L’ THEN ‘Log’

       WHEN ‘I’ THEN ‘Differential’

       WHEN ‘F’ THEN ‘File Level’

       WHEN ‘G’ THEN ‘File Level Differential’

       WHEN ‘P’ THEN ‘Partial’

       WHEN ‘Q’ THEN ‘Differential partial’

   END AS backup_type,

   convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as ‘Backup Size in MB’,

   bmf.logical_device_name,

   bmf.physical_device_name,

   bs.name AS backupset_name,

   bs.description

FROM   msdb.dbo.backupmediafamily  bmf

   INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 10)

ORDER BY

   bs.database_name,bs.backup_finish_date

Mustafa EL-Masry New Feature

Sequences in SQL Server 2012


introducation

you can easily generate auto-incrementing numbers With Sequence, it will be a different object which you can attach to a table column while inserting

Creating a Sequence in SQL Server Coding

/****** Create Sequence Object ******/

CREATE SEQUENCE SEquence2012

START WITH 1

INCREMENT BY 2;

/****** Create Table ******/

Create  TABLE Employee

(  ID int NOT NULL PRIMARY KEY,

FullName nvarchar(100) NOT NULL  );

/****** Insert Some Data ******/

INSERT Employee (ID, FullName)

VALUES (NEXT VALUE FOR SEquence2012, ‘Mostafa’),

(NEXT VALUE FOR SEquence2012, ‘Mohamed’),

(NEXT VALUE FOR SEquence2012, ‘Elmasry’);

go

Creating a Sequence in SQL Server Wizerd