Microsoft SQL Server Databases - Lesson 25: Built-In Stored Procedures

	A Review of Transact-SQL Built-In Stored Procedures

Introduction

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

To rename an object that was created in the current database, you can call the sp_rename stored procedure. Its syntax is:

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ]

The object_name is the name of the object you want to delete.

The new_name is the new name the object will have.

The object_type is option. It allows you to specify the type of object you are trying to rename.

The sp_rename stored procedure can be used to rename a table. Here is an example:

USE Exercise;
GO
EXEC sp_rename N'Contractors', N'Employees';
GO

When this code runs, a table named Contractors will have its name changed to Employees.

The sp_rename stored procedure can also be used to rename a column or an index. In this case, the name of the object must be qualified.

Sending Email

To give you the ability to send an email from a database, Transact-SQL provides the sp_send_dbmail stored procedure. This store procedure is created in the msdb database. This means that you must reference it when executing this procedure. It's syntax is:

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

As you may guess, most of the arguments are optional. Otherwise:

The profile_name value must hold a valid for an existing profile. If you omit this argument, the database engine uses the current user or a default profile created in the msdb database. If there is no default profile, you must create one. To do this, in the Object Explorer, expand the Management node and double-click Database Mail. A wizard would start:

Click Next. If you are trying a new profile, click the first radio button:

Click Next. This would bring the third page of the wizard:

In the Profile Name text box, enter the desired name. If you want, type some explanation in the Description text box. To specify an account that can send emails, click Add and fill out the form. Once you are ready, click OK. If you want to add another profile, click Add again, fill out the form, and click OK. On the third page of the wizard, click Next twice. When the wizard has finished, click Close.

The recipients value represents one or more email addresses that will receive the message. If you want to send the message to only one recipient, provide the address. To get the message to more than one recipient, separate their email addresses with commas
The copy_recipient value represents one or more email addresses that will receive a copy of the message. This is equivalent to cc. To send the message to one recipient, provide the address. To send the message to many recipients, provide the necessary email addresses separated by commas
The blind_copy_recipient value follows the same rules as the copy_recipient argument except that the recipients will not see each other's email address. This is equivalent to bcc
The subject value is the subject of the message
The body value is the actual message to send
The body_format value specifies the format to use
The importance value specifies the level of importance of the message
The sensitivity value specifies the type of sensitivity of the message

Here is an example of executing this stored procedure:

USE Exercise;
GO

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = N'Central Administrator',
	@recipients = N'jaywiler@hothothot.net',
	@body = N'The Persons table has received a new record.',
	@subject = N'New Record';
GO

Before executing this procedure, you should check the security settings on your server. Otherwise you may receive an error. Here is an example:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
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.

To solve this problem, open a Query window and type the following:

sp_configure N'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure N'Database Mail XPs', 1;
GO
RECONFIGURE;
GO