SQL Server 2016 Service Pack 1

I have started working with Microsoft SQL Server 7.0 in late 1999 and even today I become very excited with the new version of Microsoft SQL Server.  

SQL Server 2016 has been in market for quite some time but now finally Microsoft has released service pack 1 for SQL Server 2016 to download. This is one of the big release after SQL Server 2012 so I am happy to welcome SQL Server 2016 in market officially as now I can start using SQL Server 2016 in my production system and can upgrade my old SQL Server box to new and latest RDBMS.

I am happy that Microsoft has given many enterprise level feature into standard edition of SQL Server 2016 at the same time I am little disappointed as well because I am seeing no sign of horizontal scale out feature in this big release which is becoming mandatory in today’s data world.

Let us not talk negative today as I, really, want to adopt this new system into my production as soon as possible especially the powerful standard edition (I can save big money of my company by using standard edition).

Here is the official product page of Microsoft SQL Server 2016. I have already downloaded my free copy of SQL Server 2016 Developer Edition.

microsoft-sql-server-2016-geospatial-query-support

I could not resist to mention some of my favorite features, mostly enterprise level , which are available in SQL Server 2016 Standard Edition.

Dynamic Data Masking: Data security is becoming very critical nowadays especially if you are dealing with sensitive data/information like SSN number, Credit Card number. By having dynamic data masking, you can store full SSN/CC number in database but user can see only those digits, (may last few digits) which you want whereas if user has UNMASK permission, they can see complete SSN/CC number.

Row level Security: Row level security is very important especially (not limited to) for the SAAS product. I might have billing/payroll information of many different vendors in my billing/payroll table but Vendor1 shouldn’t be able to see the data of Vendor2.

Database Snapshot: As per MSDN, A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.

I am still trying to understand where I can use this feature in some of the production system I manage as it has a list of pro/cons and needs to decide whether the pro list is big or the cons.

Columnstore: First I have learnt about Column store index in SQL Server 2008 R2 during some BI project. Columnstore index is available in database engine (from SQL Server 2012) but still I think it is more usable for analytical queries, maybe I am very much used to with row-store traditional index, especially non-clustered covering index.

Compression: SQL Server 2016 supports GZip algorithm for COMPRESS/DECOMPRESS data during DML operations. Finally, I could save some more valuable SSD disk space in my server.

In-Memory OLTP: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for transaction processing. In-Memory OLTP could surely increase performance of application by improving throughput and by reducing latency for transaction processing.

Always Encrypted: As per MSDN, Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). By ensuring on-premises database administrators, cloud database operators, or other high-privileged, but unauthorized users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Polybase: And lasts but not the least in my favorite list, PolyBase is a query engine designed to fill the gap between the relational database of SQL Server with the big unstructured data mostly stored in Hadoop which can be used for analytics. We could even use Polybase with Azure Blob storage.

  

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

 

SQL Server 2014 CTP 1 available to download

SQL Server 2014 CTP 1 available to download

SQL Server 2014 is recently announced in TechED Europe. I am really very excited to download and knew more about it.

You can download SQL Server 2014 CTP 1 from here.

Each new version of software needs compatible hardware and software. Those are mandatory before you try your hands on new version. You can find Hardware & Software requirement from here.

SQL Server 2014 comes up with enhancement in piece of SQL Server like database engine, analysis service, integration service, reporting service and replication. Have a look at this in more details from this link.

When we talk about Microsoft product then how can we forget wonderful MSDN, have a look at MSDN here.

It is interesting to discuss…!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server

I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc.

Let us see this practically with an example. Create one sample table with row given in following TSQL Script.

CREATE TABLE Employee 
(ID INT IDENTITY(1,1)
, Name VARCHAR(10)
, Dept VARCHAR(10) ) 
GO 

INSERT INTO Employee 
SELECT 'Ritesh','DBA' 
UNION ALL 
SELECT 'Rajan','Dev' 
UNION ALL 
SELECT 'Bihag','Net' 
GO 

Now we have situation that if we pass “ALL” from application, it should show data from all department otherwise it should show data from the specific department which is provided in the variable. Let us see method one where people used to use IF condition to check value and have two separate SELECT statement. I don’t prefer this one, why we should write two SELECT statement whereas we can write only single. If we have many conditions, we have to repeat SELECT query in this method which is not preferable from the maintenance point of view.

--Method 1: 
DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 
IF @Dept='ALL' 
BEGIN 
	SELECT * FROM Employee 
END ELSE 
BEGIN 
	SELECT * FROM Employee WHERE Dept=@Dept 
END

There are some people who uses method 2 where they take value in temporary table to find filter value and pass it to main SELECT clause so in this method you will have only one main SELECT clause but this is also less preferable method as it has multiple SELECT statement in set along with temporary table which affect performance adversary in most cases so don’t use temporary table unless there is no way around.

--Method 2:

DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 
IF OBJECT_ID('tempdb..#DeptList') IS NOT NULL 
	DROP TABLE #DeptList 
CREATE TABLE #DeptList (
Dept VARCHAR(10)
)  

IF @Dept='ALL' 
BEGIN 
	INSERT INTO #DeptList (Dept) 
	SELECT Dept FROM Employee 
END ELSE 
BEGIN 
	INSERT INTO #DeptList (Dept) 
	SELECT Dept FROM Employee 
	WHERE Dept=@Dept 
END 

SELECT * FROM Employee WHERE Dept IN (SELECT Dept From #DeptList) 
GO 

/*method 3 which is my preferable shortest method in this kind of situation which has good performance as well when compare it with above two method.*/   

--Method 3 
DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 

SELECT * FROM Employee WHERE Dept=CASE WHEN @Dept='ALL' THEN Dept ELSE @Dept END

 

Are you using any other method to achieve the task explained here? It is interesting to discuss…!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah http://Extreme-Advice.com http://www.sqlhub.com Note: Microsoft Books online is a default reference of all articles.

SQL Server Login permission to read queue message count of Service Broker

SQL Server Login permission to read queue message count of Service Broker

Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application to find the service broker queue count but, unfortunately, It was returning 0 value in web application but if he tries to execute UDF function in SSMS itself, it used to return proper queue count.

He wrote me back to find the solution of the problem he was facing. I asked him the login he is using in SSMS as well as from application and as soon as I get this answer from him, I get to know the reason exact issue he was facing.

He was using Windows Authentication “Administrator” login while using SSMS and was using WebAppEnt SQL Server login from web application. I guessed that Administrator login would by sysAdmin and WebAppEnt login would have only required permission which my reader confirmed. I told him to give proper permission to WebAppEnt login with following script.

USE Master GO 
GRANT VIEW ANY DEFINITION TO WebAppEnt; 
GO 

As soon as He ran above given script, everything started working fine. :)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah http://Extreme-Advice.com http://www.sqlhub.com Note: Microsoft Books online is a default reference of all articles.

Count active Thread for Service Broker Queue in SQL Server

Count active Thread for Service Broker Queue in SQL Server

Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to each queue. Suppose we have one billing queue then we suppose to name it as “BillingInitiatorQueue”, “BillingTargetQueue” etc.

Since target queue has responsibility to implement actual business logic, I am always interested to see how many thread currently running for each target queue. Suppose I have set 15 thread for “BillingTargetQueue” but it may happen that I have only 5 to 10 messages in queue and hence all 15 thread will not be active. If I don’t have any message in queue then obviously no thread will be active though I set 15. Sometime I may have 1000 message to process in target queue but even I have 10 thread active only. Remaining 5 thread may not be getting proper system resources or anything else. I can’t even decide whether to increase thread or decrease without knowing actual number of message, time thread is taking to process. I have already written articles about “Find Service Broker Queue Count in SQL Server (Click Here) ” and “Keep watch on Service Broker Queue Delay in SQL Server (Click Here) ”. Today I am going to show the script which will give the count of thread for target queue running at the moment.

[sourcecode language=”sql”]

SELECT

OBJECT_NAME(queue_id) AS QueueName,

COUNT(*) AS ThreadCount

FROM

sys.dm_broker_activated_tasks WITH (NOLOCK)

–Comment the WHERE clause to see thread count for all queue

–not only target queue

WHERE (OBJECT_NAME(queue_id) LIKE ‘%Target%’)

GROUP BY

OBJECT_NAME(queue_id)

HAVING

(COUNT(*) > 0)

ORDER BY

ThreadCount DESC

GO

[/sourcecode]

I have written few more article about service broker, have a look if you are interested.

Service Broker Articles:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)
  • Keep watch on Service Broker Queue Delay in SQL Server (Click Here)
  • Deal with message queue conversation with state CD  in Service Broker (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error Fix – Replication subscriber does not exist on the server anymore

Error Fix – Replication subscriber does not exist on the server anymore

Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have restored mirrored database backup to primary server.

Now situation is complicated as subscriber was removed, distributor was removed but in primary server, they are seeing publication as well as subscriber. They have already tried the way I have explained in one of my earlier article “Forcefully remove replication publisher, subscriber and distributor in SQL Server” but no luck.

When they tried to remove publication by pressing “Del” key after selecting “Publication” under “Replication” in SSMS, they faced following message.

[sourcecode]
TITLE: Microsoft SQL Server Management Studio
——————————
Could not delete publication ‘SMXPPublisher’.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476
——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————

Cannot drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information.
Changed database context to ‘SMXP’. (Microsoft SQL Server, Error: 14005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=14005&LinkId=20476
——————————
BUTTONS:

OK
——————————
[/sourcecode]

Here is the screen capture of the error:

RemovePublicationError

This is obvious message that they can’t delete Publisher until and unless they remove subscriber under publication but the twist is they have already deleted subscriber from the secondary server and primary server were down at that time so effect doesn’t came there so while deleting subscriber from the primary server, they were greeted with following message.

[sourcecode]
TITLE: Microsoft SQL Server Management Studio
——————————
"MARS:SMXPRepl" does not exist on the server anymore. Please refresh its parent tree node.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=ObjectNoLongerExists&LinkId=20476
——————————

BUTTONS:

OK
——————————
[/sourcecode]

Here is the capture for this error:

RemoveSubscriberError

Now, there is a big question, how to remove this replication?

After getting in this clumsy situation, they have called me up to help. They needed very fast solution so I provided undocumented and dirty but yet workable way. Here is what I have asked them to perform and update me with the result.

Here is the small TSQL script I told them to run on primary server in replicated database.

[sourcecode language=”sql”]
USE master
EXEC sp_removedbreplication @dbname=’smxp’
GO

sp_dropsubscription @subscriber=’smxprepl’

sp_droppublication ‘smxppublisher’
[/sourcecode]

After executing above given commands, they were facing following error:

[sourcecode]
–Cannot drop the table ‘dbo.MSpeer_lsns’ because it is being used for replication.
[/sourcecode]

Well, my suggestion now is to unmark “MSPeer_lsns” object from replication with following command and then try to execute above script to remove and drop replication.

[sourcecode language=”sql”]
sp_MSunmarkreplinfo ‘MSpeer_lsns’
[/sourcecode]

Again there wasn’t luck with them so finally I send them one more TSQL which did the magic.

[sourcecode language=”sql”]
DROP TABLE sysarticleupdates
DROP TABLE sysarticles
sp_removedbreplication ‘smxp’
GO
DROP TABLE syspublications
DROP TABLE sysschemaarticles
DROP TABLE systranschemas
DROP TABLE syssubscriptions
DROP TABLE sysarticlecolumns
DROP TABLE MSpub_identity_range
DROP TABLE MSpeer_response
DROP TABLE MSpeer_request
DROP TABLE MSpeer_lsns
DROP VIEW sysextendedarticlesview
[/sourcecode]

After executing above given TSQL script, it has removed everything related to replication. However, I don’t recommend this script for normal situation.

Here is the list of my other replication related articles, if you are interested!!!!

Replication Related Articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)
  • Keep an eye on replication error in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Bug Fix – Show Result Pane not working in SQL Server 2012 with CTRL + R

Bug Fix – Show Result Pane not working in SQL Server 2012 with CTRL + R

Once you are addicted with some keyboard short keys then you hate to use mouse for those things for sure. SQL Developer/DBA are, mostly, addicted with “CTRL + R” short key which is being used to show/hide result panel in SSMS.

I, many time, received question/complaint that CTRL+R is not working in SQL Server 2012’s SSMS, especially from the user who are using Windnows 7 (Windows 8 doesn’t have this behavior, at least for me).

Well, this is very known issue and there is a workaround for this. Let us see how shall we do this…..!!!

Once you open the SSMS, go to Tools | Customize.

1

Once you will get “Customize” dialog box, click on “Keyboard” button from the “Customize” dialog box.

2

Now you will be greeted with “Options” dialog box, where you have to select “Windows.ShowResultPane” option from listbox given under “Show Commands Containing”, select “SQL Query Editor” under “Use new Shortcut in”, also Press the short key you wanted to use (I have selected very known CTRL+R) under “Press shortcut keys” and click on “Assign” button and close the dialog box by clicking on “OK” button.

3

Now, you are there with your very own shortcut key “CTRL+R”. It will work in SSMS even in Windows 7.

Have you ever faced this issue?

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles

Find disabled job in SQL Server

Find disabled job in SQL Server

I, generally, don’t tend to keep disabled job in my server. I would always like to remove disabled job after generating the script and keep the backup of script and this is the reason whenever someone intentionally or unintentionally disabled the job, my monitoring application shows the server name and disable job.

There is one small TSQL SELECT script which shows the disabled job along with server name and last modified date.

[sourcecode language=”sql”]
SELECT
@@servername as ServerName,
name as JobName,
date_modified as LastModifiedDate
FROM
msdb.dbo.sysjobs WITH (NOLOCK)
WHERE
enabled = 0
[/sourcecode]

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)
  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)
  • Query to Find missing job in SQL Server(Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Copy and archive SQL Server database backup

Copy and archive SQL Server database backup

Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different ways to accomplish this task but I, personally, prefer to go for .BAT file which can be called from windows schedule task.

Suppose I want following policy (obviously one can change the policy given below as per their need).

  • I want full/trn backup to be kept in same server where SQL Server is installed for one day
  • Very next day that backup to be moved to network location
  • Network location should keep backup for three days and older backups then three days should be removed

I already have maintenance plan running in SQL Server which keeps taking full backup everyday and transaction backup at every 15 minutes. I will handle copy/archive process from .BAT file and schedule this batch file to run at everyday 12:05AM to move previous days backup and remove older backup.

Here is the script I would have in my batch file:

[sourcecode]
Sqlcmd -E -S"RiteshPC\Dev2012" -dmsdb -Q"set nocount on ; SELECT DISTINCT bmf.Physical_device_name +’,’+’\\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\’FROM msdb.dbo.backupset (nolock) bs INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) WHERE bs.backup_finish_date > DATEADD(HOUR,-24,GETDATE()) " -o "E:\Batchfiles\BackupFileDev.txt"
FOR /F "tokens=1,2 skip=2 delims=," %%G IN (E:\BatchFiles\BackupFileDev.txt) DO xcopy /Y %%G %%H

forfiles.exe /p E:\Database-Backup\Dev /s /m *.bak /d -1 /c "cmd /c del @file"
forfiles.exe /p E:\Database-Backup\Dev /s /m *.trn /d -1 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.bak /d -3 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.trn /d -3 /c "cmd /c del @file"
[/sourcecode]

Herewith I want to list some points where you may need change while setting up batch file for your environment.

  • “RiteshPC\Dev2012” is my SQL Server Instance name, you have to provide yours.
  • I have used “-E” parameter in the first line of batch file as I want my SQL Server can be connected with windows authentication, if you want SQL Authentication, you have to provide SQL Server login/password which has permission to read system table in MSDB database
  • I have used “DATEADD(HOUR,-24,GETDATE())” in SELECT query as I want to deal with all the backup comes in last 24 hours only.
  • \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\” is the network path where I want to put backup for three days.
  • Select query ran in MSDB database will output results in text file at “E:\Batchfiles\BackupFileDev.txt”. That file will be read, copy the backup to network path.
  • In first two “forfiles.exe” command, I am deleting .bak and .trn from the server which are older then 1 day (you can observe “-1” parameter value I gave after “/d” parameter)
  • In 2nd and 3rd “ForFiles.exe” I ham deleting .bak and .trn files from the networks which are older then 3 days.

Do you have any other way to manage copy/archive of backup?

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error Fix Database Mail XPs error in SQL Server

Error Fix Database Mail XPs error in SQL Server

Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing:

SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, see “Surface Area Configuration” in SQL Server Books Online.

The statement has been terminated.

Here is the screen capture he sent to me:

EmailError

Well, by default, SMTP mail functionality comes disabled and if you intend to use it, you have to enable it with the help of SP_Configure system stored procedure.

Here is the small TSQL snippet which helps you to enable database mail functionality.

[sourcecode language=”sql”]

USE MASTER
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘Database Mail XPs’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO
[/sourcecode]

Once you execute above given TSQL Snippet, you can use database mail functionality.

BTW, I have written few articles related to SP_Configure system stored procedure, if you are interested, have a look:

  • OpenRowSet and OpenQuery in SQL Server 2005/2008 (Click Here)
  • Enable XP_cmdshell in SQL Server 2008 (Click Here)
  • Copy files with SQL Server from one location to another location. (Click Here)
  • Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.