Tag Archives: sp_configure

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.


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

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.

 

Sp_configure in SQL Server 2005

Today I was reading few of my old articles, when I seen sp_configure there, idea of this article pops up in my mind as there are many properties to set and I have used few of them only in my past article. There are many more one can use in real life so I have started digging into Microsoft Book Online for those properties of sp_configure and here is a results of that.

Sp_configure: When you run this SP in your query window, it will show you current setting of the server. You can execute it like below

EXEC sp_configure

NOTE: many of the server property change will not take effect until and unless your restart server or its service that is why we have to use RECONFIGURE command rather than restart.

“Show Advanced Option”: This property will show you all advanced option you can set with sp_configure. If you run only EXEC sp_configure command as per above, you may see few rows as a result, I got 14 rows in my development server but there are lot more option other than these few rows. To see all the advanced option, run following commands in your query window.

–set advanced option to true

EXEC sp_configure ‘show advanced option’,1

–reconfigure the server so that property change

–take effect right a way

reconfigure

GO

–list the properies after advanced option true

EXEC sp_configure

GO

“Query Wait”: When memory is not available to execute the query, you can specify seconds, how many seconds will it wait to execute before gets time out?

EXEC sp_configure ‘query wait’,30

reconfigure

GO

Min and Max server memory: These properties mainly used to change the memory usage of SQL Server. I have used these properties along with “awe enabled” property in my following article.

http://www.sqlhub.com/2009/03/memory-configuration-of-sql-server-2005.html

“Min memory per query”: default 1MB set as a “Min memory per query” but you can increase or decrease this limit for better performance but beware, increasing limit could cause memory shortage problem as well.

EXEC sp_configure ‘min memory per query’,1024

reconfigure

GO

“Scan for startup procs”: I have used this property in my article of Startup Stored Procedure at http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

This property is mandatory if you are making startup stored procedure and want SQL Server to scan that SP while SQL Server restart.

EXEC sp_configure ‘scan for startup procs’,1

reconfigure

GO

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Startup Stored Procedure -SQL-Server 2005

Startup Stored Procedure -SQL-Server 2005
All of you may aware with Stored Procedure but you may not aware with startup stored procedures which
suppose to run every time SQL-Server’s services restart.
This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.
Before you start generating startup stored procedure, have a look at some basic ground regarding that.
You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.
–this will show you all advance option
EXEC sp_configure ‘show advanced option’, ‘1’;
RECONFIGURE
–this will enable startup procedure
EXEC sp_configure ‘scan for startup procs’, ‘1’;
RECONFIGURE
Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.
–Procedure should be reside in MASTER database
–Its owner should be DBO
–Mark your stored procedure as startup stored procedure with sp_procoption
–Only sysAdmin can set sp_procoption
–Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.
–Creating strored procedure
CREATE PROC spCreateDatabaseAtStartup
AS
EXEC(‘CREATE database StartupDatabase’)
GO

–Marking SP as startup
exec sp_procoption N‘spCreateDatabaseAtStartup’, ‘startup’, ‘on’
In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName” but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah