RSS

Monthly Archives: January 2012

How To Configure Database mail In SQL Server

introduction for database Mail in SQL Server 2012

Today I will explain how to configure database mail in SQL Server 2005,2008,2012 , No difference between this version in the database mail configuration , database Mail is good Option in SQL Server because very easy in the Configuration and its very helpful I think ALL DBA use this option because this option he can Follow up his database . if you want to configure Database Mail you have to ways one is the Wizard ant the Way no Two is Coding and i like this way more than the wizard so let me sow to you how can you Configure Database Mail in SQL Server 2012 by Coding *** I say SQL Server 2012 because when I write this Article i make this Example in my SQL Server 2012 ****

Configure Database mail In SQL Server Steps

Step No one [Enable database Mail option]

use master

go

sp_configure ‘show advanced options’,1

go

reconfigure with override

go

sp_configure ‘Database Mail XPs’,1  —— Enable database Mail

–go

–sp_configure ‘SQL Mail XPs’,0 ——- Disable database mail

go

reconfigure

go

Step No Two [Create database mail Account]

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ‘safeerp’,

@description = ‘Mail account for Database Mail For safeerp Company Created By Mr/ Mostafa Elmasry’,

@email_address = Elmasre_201038@Yahoo.com‘,

@display_name = ‘Database Mail SQL Server 2012′,

@username=’Elmasre_201038@Yahoo.com’,

@password=’Email Password’,

@mailserver_name = ‘smtp.mail.yahoo.com’

 Step No Three [Create database mail profile]

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ‘safe’,

@description = ‘Profile used for database mail safeerp Company Created By Mr /Mostafa Elmasry’

Step No Four [Add the database mail account on the profile]

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ‘safe’,

@account_name = ‘safeerp’,

@sequence_number = 1

Step No Five [Database mail profile Secuirty]

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = ‘safe’,

@principal_name = ‘public’,  ——- you can write here Puplic or Private (Puplic) any User in SQL Server have permision to use the database mail but (private) the User Create he only can use the database mail.

@is_default = 1 ;

Final Step No Sex [Send Email by DataBase Mail SQL Server]

declare @body1 varchar(100)

set @body1 = ‘Server :’+@@servername+ ‘ My First Database Email ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=’elmasre_201038@hotmail.com’,

@subject = ‘My Mail Test From Daem DataBase Mail’,

@body = @body1,

@body_format = ‘HTML’ ;

Or

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘safe’

,@recipients = ‘elmasre_201038@Hotmail.com’

,@from_address = ‘Elmasre_201038@Yahoo.com’

,@subject = ‘Mostafa sql server’

,@body=’My Friend Kemo  now i try to send new Email from my Databse mail Account’

,@query = ‘SELECT * FROM msdb..sysmail_allitems WHERE sent_status =”sent” ‘

/*

When you want to send mail like step no 6 you must learn the pramter to send it to the procedure [msdb.dbo.sp_send_dbmail] so lets check this pramter in this link http://msdn.microsoft.com/en-us/library/ms190307.aspx

*/

Now after you send the database mail sure you want to check the status for this mail so run tis script to check the mail status

SELECT * FROM msdb.dbo.sysmail_event_log

order by log_date

use msdb

exec sysmail_start_sp

GO

SELECT * FROM msdb..sysmail_allitems WHERE sent_status = ‘sent’

Database Mail Configuration

 
3 Comments

Posted by on January 31, 2012 in Database mail

 

Server Core in windows server 2008

What is Window Server core ?

Server Core is a minimal server installation option for computers running on the  Windows Server 2008 operating system or later. Server Core provides a low-maintenance server environment with limited functionality.

Server Core Requirements?

Windows server 2008 or later version .

Roles cans server core run .

Active Directory domain services (AD DS) (including Read only domain controllers RODS)

Active directory lightweight directory services (AD LDS)

DHCp Server

DNS Server

File Services

Print Services

Streaming Medias Services

Web Server (IIS)

 

 
Leave a comment

Posted by on January 28, 2012 in Introducation

 

Delete file on your PC by SQL Server

you must be in the first Configure SQl server to allow you to make this operation so Run this script on your server (take it from this link)

[https://mostafaelmasry.wordpress.com/2012/01/20/ole-automation-procedures-to-delete-file-from-pc/]

then run this Script

DECLARE @Result int

declare @File_Location as nvarchar(300) = ‘F:\fff.txt’

DECLARE @FSO_Token int

EXEC @Result = sp_OACreate ‘Scripting.FileSystemObject’, @FSO_Token OUTPUT

EXEC @Result = sp_OAMethod @FSO_Token, ‘DeleteFile’, NULL, @File_Location

EXEC @Result = sp_OADestroy @FSO_Token

 

 

 

 

Ole Automation Procedures to delete file from PC

if you want to write Script to delete file from  your pc by SQL Server must be Configure your SQL server to allow this option :

exec sp_configure ‘Ole Automation Procedures’, 1

go

reconfigure

go

 

 
1 Comment

Posted by on January 20, 2012 in Configuration, Configuration

 

Stored Procedure Restore From All Files In Directory

When i search in the internet i  see Stored procedure make restore from all files in directory i read it in the first then i take this Sp and make edit on it to customize it and add New paramter to make anather thing if you want to the source for this Sp go to this link

http://www.karaszi.com/SQLServer/code/sp_RestoreFromAllFilesInDirectory_2008sp1.txt

/*

Customized By Mostafa Elmasry

Dwenload Sp To SQL 2012: http://hotfile.com/dl/142851801/0d8e1c7/SP_Restore_Dynamic.sql.html

Dwenload  SP to SQL 2005 : http://hotfile.com/dl/143006210/7b26c20/SP_Restore_Dynamic_to_SQL_2005.sql.html

This Stored Procedure use to Restore All file backup From @SourceDirBackupFiles

its update to work on SQL 2012 but if you run this script on this version must be Add this column [Containment] in the #bdev table in the last i add it

but i make this column is Uncommite . It’s Work very good on all version 2000, 2005, 2008, 2008R2 + 2012 (but don’t forget make Commentto the Column Containment in #bdev Table)

to Exec this Sp must be give it 5 paramter

@SourceDirBackupFiles = Your backup file location

@DestDirDbFiles       = database file location MDF will creat on this path

@DestDirLogFiles      = database Log_File location lDF will creat on this path

@Mode_Restore         = 0 >> the database name will be = Logicalname , 1 >> Tha database name will be the  = same backup name

@Exce_Command      = 0 >> the Sp will Exec the Command restore    , 1 >> The SP will print the comand and don’t make exec

it dont make overwite

IF the backup have more than backup in one file this code make restore to the max position

Restores from all files in a certain directory

Sample execution:

EXEC sp_RestoreFromAllFilesInDirectory

@SourceDirBackupFiles = ‘E:\DataBase\Backup\’,

@DestDirDbFiles       = ‘E:\DataBase\R\’ ,

@DestDirLogFiles      = ‘E:\DataBase\R\’ ,

@Mode_Restore         =  1    ,

@Exce_Command         = 0

*/

USE master

GO

/****** Object:  StoredProcedure [dbo].[sp_RestoreFromAllFilesInDirectory]    Script Date: 04/01/2010 06:08:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc [dbo].[sp_RestoreFromAllFilesInDirectory]

(

@SourceDirBackupFiles NVARCHAR(200) ,

@DestDirDbFiles NVARCHAR(200)       ,

@DestDirLogFiles NVARCHAR(200)      ,

@Mode_Restore int                   ,

@Exce_Command int

)

AS

begin transaction

SET NOCOUNT ON

–Table to hold each backup file name in

CREATE TABLE #files(fname VARCHAR(200),depth INT, file_ INT)

INSERT #files

EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

–Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from

CREATE TABLE #bdev(

BackupName NVARCHAR(300)

,BackupDescription NVARCHAR(255)

,BackupType smallint

,ExpirationDate datetime

,Compressed tinyint

,Position smallint

,DeviceType tinyint

,UserName NVARCHAR(128)

,ServerName NVARCHAR(128)

,DatabaseName NVARCHAR(128)

,DatabaseVersion INT

,DatabaseCreationDate datetime

,BackupSize numeric(20,0)

,FirstLSN numeric(30,0)

,LastLSN numeric(30,0)

,CheckpointLSN numeric(30,0)

,DatabaseBackupLSN numeric(30,0)

,BackupStartDate datetime

,BackupFinishDate datetime

,SortOrder smallint

,[CodePage] smallint

,UnicodeLocaleId INT

,UnicodeComparisonStyle INT

,CompatibilityLevel tinyint

,SoftwareVendorId INT

,SoftwareVersionMajor INT

,SoftwareVersionMinor INT

,SoftwareVersionBuild INT

,MachineName NVARCHAR(128)

,Flags INT

,BindingID uniqueidentifier

,RecoveryForkID uniqueidentifier

,Collation NVARCHAR(128)

,FamilyGUID uniqueidentifier

,HasBulkLoggedData INT

,IsSnapshot INT

,IsReadOnly INT

,IsSingleUser INT

,HasBackupChecksums INT

,IsDamaged INT

,BegibsLogChain INT

,HasIncompleteMetaData INT

,IsForceOffline INT

,IsCopyOnly INT

,FirstRecoveryForkID uniqueidentifier

,ForkPointLSN numeric(25,0)

,RecoveryModel NVARCHAR(128)

,DifferentialBaseLSN numeric(25,0)

,DifferentialBaseGUID uniqueidentifier

,BackupTypeDescription NVARCHAR(128)

,BackupSetGUID uniqueidentifier

,CompressedBackupSize NVARCHAR(128)

,Containment int ———————– if you use SQl 2012 Enable this Column

)

–Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command

CREATE TABLE #dbfiles(

LogicalName NVARCHAR(128)

,PhysicalName NVARCHAR(260)

,Type CHAR(1)

,FileGroupName NVARCHAR(128)

,Size numeric(20,0)

,MaxSize numeric(20,0)

,FileId INT

,CreateLSN numeric(25,0)

,DropLSN numeric(25,0)

,UniqueId uniqueidentifier

,ReadOnlyLSN numeric(25,0)

,ReadWriteLSN numeric(25,0)

,BackupSizeInBytes INT

,SourceBlockSize INT

,FilegroupId INT

,LogGroupGUID uniqueidentifier

,DifferentialBaseLSN numeric(25)

,DifferentialBaseGUID uniqueidentifier

,IsReadOnly INT

,IsPresent INT

,TDEThumbprint NVARCHAR(128)

)

DECLARE @fname VARCHAR(200)

DECLARE @FNAME_RESTORE NVARCHAR(200)

DECLARE @dirfile VARCHAR(300)

DECLARE @LogicalName NVARCHAR(128)

DECLARE @PhysicalName NVARCHAR(260)

DECLARE @type CHAR(1)

DECLARE @DbName sysname

DECLARE @sql NVARCHAR(1000)

Declare @position int

—Check Mode_Reestore IF @Mode_Restore = 0 [Database Name will be = The Logical_name] ElseIF @mode_Restore = 0 [Database Name Will be = The backup Name] Else [Error massage]

DECLARE files CURSOR FOR

–SELECT FNAME FROM #files

select FNAME,left (fname,case charindex(‘.’,fname) when 0 then   len(fname)   else    charindex(‘.’,fname) -1   end)from #files

DECLARE dbfiles CURSOR FOR

SELECT LogicalName, PhysicalName, Type FROM #dbfiles

OPEN files

FETCH NEXT FROM files INTO @fname , @FNAME_RESTORE

WHILE @@FETCH_STATUS = 0

BEGIN

SET @dirfile = @SourceDirBackupFiles + @fname

–Get database name from RESTORE HEADERONLY, assumes there’s only one backup on each backup file.

TRUNCATE TABLE #bdev

INSERT #bdev

EXEC(‘RESTORE HEADERONLY FROM DISK = ”’ + @dirfile + ””)

set @position = (select max(position) from #bdev)

SET @DbName = (SELECT DatabaseName FROM #bdev where position = @position)

if @Mode_Restore = 1

begin

–Construct the beginning for the RESTORE DATABASE command

SET @sql = ‘RESTORE DATABASE ‘ + @FNAME_RESTORE + ‘ FROM DISK = ”’ + @dirfile + ”’ WITH MOVE ‘

end

Else If @Mode_Restore = 0

begin

–Construct the beginning for the RESTORE DATABASE command

SET @sql = ‘RESTORE DATABASE ‘ + @DbName + ‘ FROM DISK = ”’ + @dirfile + ”’ WITH MOVE ‘

end

else

BEGIN

— Rollback the transaction

ROLLBACK

— Raise an error and return

RAISERROR (‘Must be @mode_restore = 1 or o.’, 16, 1)

RETURN

end

–Get information about database files from backup device into temp table

TRUNCATE TABLE #dbfiles

INSERT #dbfiles

EXEC(‘RESTORE FILELISTONLY FROM DISK = ”’ + @dirfile + ””)

OPEN dbfiles

FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

–For each database file that the database uses

WHILE @@FETCH_STATUS = 0

BEGIN

IF @type = ‘D’

SET @sql = @sql + ”” + @LogicalName + ”’ TO ”’ + @DestDirDbFiles + @LogicalName  + ‘.mdf”, MOVE ‘

ELSE IF @type = ‘L’

SET @sql = @sql + ”” + @LogicalName + ”’ TO ”’ + @DestDirLogFiles + @LogicalName  + ‘.ldf”’

FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

END

If @Exce_Command = 1

begin

EXEC(@sql)

end

Else IF @Exce_Command = 0

begin

–Here’s the actual RESTORE command

PRINT @sql

End

Else

BEGIN

— Rollback the transaction

ROLLBACK

— Raise an error and return

RAISERROR (‘Must be @Exce_Command = 1 or o.’, 16, 1)

RETURN

end

CLOSE dbfiles

FETCH NEXT FROM files INTO @fname , @FNAME_RESTORE

IF @@ERROR <> 0

BEGIN

— Rollback the transaction

ROLLBACK

— Raise an error and return

RAISERROR (‘Error in restoring the File’, 16, 1)

RETURN

END

END

COMMIT

CLOSE files

DEALLOCATE dbfiles

DEALLOCATE files

 

Change The Server Collation after installation

After you finish installation for SQL server may be you want to change the Server collation or database Collation so leat’s see what will we do if we want to make this :

1- if you want to know what’s your database collation know

SELECT name, collation_name

FROM sys.databases WHERE name = ‘test’   — put your database name here

2- If you want to get a list of all available collations on the server using

SELECT * FROM ::fn_helpcollations()

3- Alter Database Collation

ALTER DATABASE test           — put your database name here

COLLATE Arabic_CI_AS        — replace with whatever collation you need

4- IF you want to know what’s your server collation

SELECT SERVERPROPERTY (‘Collation’)

 
1 Comment

Posted by on January 18, 2012 in Configuration, Configuration

 

Returen All File in Any Directory Stored Procedure Sp_ListFiles

USE master

GO

CREATE PROCEDURE dbo.sp_ListFiles

@PCWrite varchar(2000),

@DBTable varchar(100)= NULL,

@PCIntra varchar(100)= NULL,

@PCExtra varchar(100)= NULL,

@DBUltra bit = 0

AS

SET NOCOUNT ON

DECLARE @Return int

DECLARE @Retain int

DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(2000)

DECLARE @Work varchar(2000)

DECLARE @Wish varchar(2000)

SET @Work = ‘DIR ‘ + ‘”‘ + @PCWrite + ‘”‘

CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

SET @Retain = @@ERROR

IF @Status = 0 SET @Status = @Retain

IF @Status = 0 SET @Status = @Return

IF (SELECT COUNT(*) FROM #DBAZ) < 4

BEGIN

SELECT @Wish = Name FROM #DBAZ WHERE Work = 1

IF @Wish IS NULL

BEGIN

RAISERROR (‘General error [%d]’,16,1,@Status)

END

ELSE

BEGIN

RAISERROR (@Wish,16,1)

END

END

ELSE

BEGIN

DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING

(Name,40,1) = ‘.’ OR Name LIKE ‘%.lnk’

IF @DBTable IS NULL

BEGIN

SELECT SUBSTRING(Name,40,100) AS Files

FROM #DBAZ

WHERE 0 = 0

AND (@DBUltra = 0 OR Name LIKE ‘% %’)

AND (@DBUltra != 0 OR Name NOT LIKE ‘% %’)

AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)

AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)

ORDER BY 1

END

ELSE

BEGIN

SET @Task = ‘ INSERT ‘ + REPLACE(@DBTable,CHAR(32),CHAR(95))

+ ‘ SELECT SUBSTRING(Name,40,100) AS Files’

+ ‘ FROM #DBAZ’

+ ‘ WHERE 0 = 0’

+ CASE WHEN @DBUltra = 0 THEN ” ELSE ‘ AND Name LIKE ‘ + CHAR(39) + ‘% %’ + CHAR(39) END

+ CASE WHEN @DBUltra != 0 THEN ” ELSE ‘ AND Name NOT LIKE ‘ + CHAR(39) + ‘% %’ + CHAR(39) END

+ CASE WHEN @PCIntra IS NULL THEN ” ELSE ‘ AND SUBSTRING (Name,40,100) LIKE ‘ + CHAR(39) + @PCIntra + CHAR(39) END

+ CASE WHEN @PCExtra IS NULL THEN ” ELSE ‘ AND SUBSTRING

(Name,40,100) NOT LIKE ‘ + CHAR(39) + @PCExtra + CHAR(39) END

+ ‘ ORDER BY 1’

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

DROP TABLE #DBAZ

SET NOCOUNT OFF

RETURN (@Status)

GO

—-EXECUTE sp_ListFiles ‘E:\DataBase’,NULL,NULL,NULL,1