Posted in Development, Stored Procedure

Sysmail_help_Account_sp – SQL Server Database Mail Stored Procedure – Part 6

Hi friends, in this blog we will learn how to get information about database mail account using stored procedure sysmail_hep_account_sp.

Sysmail_help_account_sp stored procedure displays information about database mail account.

When the procedure executes without passing any parameters then it will display all database accounts information.

Syntax of sysmail_help_account_Sp is as follows:

sysmail_help_account_sp [ [ @account_id = ] account_id | [ @account_name = ] ‘account_name’ ]

Arguments

@account_id – Account Id of the database mail account.

@account_name – Account name of the database mail account.

Let’s execute the stored procedure to get the information of the database mail account which we created in our previous blog here. Here we define the account name in the parameter list.

EXEC msdb.dbo.sysmail_help_Account_sp NULL, ‘DB_Email_Account’

sysmail_help_account

After executing the stored procedure we will get the following columns in the result set:

Account_Id – Account Id with datatype Int.

Name – Account Name with datatype sysname.

Description – Description of the account having datatype nvarchar(256).

Email_address – Email address of the sender having datatype nvarchar(128).

Display_name – Display name of the account having datatype nvarchar(128).

Replyto_address – The address where replies to message are sent.

Servertype – Type of email server for mail account.

Servername – Name of email server

Port – Port number of email server

Username – Username to use login to email server.

User_default_credentials – Specifies whether to send the email using SMTP server using credentials of SQL Server database engine.

Enable_ssl – Specifies whether Database email encrypts communication using SSL.

That’s all for the day friends. We will continue with other database mail stored procedures in next blogs. Have a happy learning 🙂

Advertisements
Posted in Development, Stored Procedure

Sysmail_add_account_sp – SQL Server Database Mail Stored Procedure – Part 5

Hi friends, today we will continue with database mail stored procedure and will learn how to create database mail account using stored procedure sysmail_add_account_sp.

Sysmail_add_account_sp stored procedure is used to create database mail account which contains information about SMTP account.

Syntax of sysmail_add_account_sp is as:

sysmail_add_account_sp  [ @account_name = ] ‘account_name’,

    [ @email_address = ] ’email_address’ ,

    [ [ @display_name = ] ‘display_name’ , ]

    [ [ @replyto_address = ] ‘replyto_address’ , ]

    [ [ @description = ] ‘description’ , ]

    [ @mailserver_name = ] ‘server_name’

    [ , [ @mailserver_type = ] ‘server_type’ ]

    [ , [ @port = ] port_number ]

    [ , [ @username = ] ‘username’ ]

    [ , [ @password = ] ‘password’ ]

    [ , [ @use_default_credentials = ] use_default_credentials ]

    [ , [ @enable_ssl = ] enable_ssl ]

    [ , [ @account_id = ] account_id OUTPUT ]

Arguments

@account_name – Name of new database mail account.

@email_address – It is email address which will be used to send the message. Its datatype is nvarchar(128).

@display_name – Display name to use on email-address from this database mail account. Its datatype is nvarchar(128).

@replyto_address – It is email address where reply to messages will be sent under sent to. Its datatype is nvarchar(128).

@description – Description of the database mail account.

@mailserver_name – Name or ip address of the SMTP mail server used for this database mail account.

@mailserver_type – Type of email server with default value ‘SMTP’.

@port – Port number of the email server with default value 25.

@username – Username to use to login on email server. Its datatype is nvarchar(128),

@password – Password used to logon email server. Its datatype is nvarchar(128).

@user_default_credentials – Flag used to specify whether to send the mail to SMTP server using credentials of SQL Server Database Engine or not having default value 0. When flag value is 1, it will use credentials of Database Engine. When flag value 0, it will use credentials defined in parameters @username and @password.

@enable_SSL – Flag used to specify whether database mail use SSL or not with default value 0.

@account_id – It will return the account id of new database mail account.

Now we will create a database mail account using following code:

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ‘DB_Email_Account’,

@display_name = ‘SQL Database Email Account’,

@email_address = ‘kapil190588@gmail.com’,

@replyto_address = ‘kapil_k@outlook.com’,

@mailserver_name = ‘smtp.gmail.com’,

@port=587,

@enable_ssl=0

After executing this code, a database mail accounts gets created.  Now we need to add this mail account to a database profile which we will see in our later blogs.

That’s all for the day folks.

Posted in Development, Stored Procedure

Sysmail_help_profile_sp – SQL Server Database Mail Stored Procedure – Part 4

Hi friends, today we will learn how to get information about database mail profile using stored procedure sysmail_help_profile_sp.

Sysmail_help_profile_sp stored procedure provides information about the database mail profiles. This stored procedure is stored in msdb database and owned by dbo schema. The procedure must be executed with a three part name if current database is not msdb.

Syntax:

sysmail_help_profile_sp  [   [ @profile_id = ] profile_id | [ @profile_name = ] ‘profile_name’ ]

Arguments:

@profile_id  is the id of database mail profile with default value NULL.

@profile_name is the name of the database mail profile.

When profile_id or profile_name is specified, sysmail_help_profile_sp stored procedure return information about that profile. If a profile_id or profile_name is not specified it returns information about all profiles in current SQL Server instance.

In our previous blog we created a database mail profile with name ‘Database Test Mail’. Now we will pass that profile name in the parameter to retrieve information about that profile.

EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = ‘Database Test Mail’

sysmail_help_profile

Here, in result set we get information of that database mail profile.

Profile_id – Profile id of database mail profile.

Name – Name of database mail profile.

Description – Description about database mail profile.

That’s all folks for the day. In next blog we will see how to update the information about database mail profile. Have a nice day 🙂

Posted in Development, Stored Procedure

Sysmail_add_profile_sp – SQL Server Database Mail Stored Procedure – Part 3

Hi friends, today we will continue with database mail stored procedure sysmail_add_profile_sp.

Sysmail_add_profile_Sp stored procedure is used to create database mail profile. A database mail profile can hold multiple database mail accounts. Database mail profile name must be unique for Microsoft SQL Server Database Engine. This stored procedure is stored in msdb database and owned by schema dbo.

Syntax:

sysmail_add_profile_sp [ @profile_name = ] ‘profile_name’

[ , [ @description = ] ‘description’ ]

[ , [ @profile_id = ] new_profile_id OUTPUT ]

Arguments:

@profile_name is the name of new database mail profile.

@description is the description for new profile having datatype nvarchar (256).

@profile_id is the ID of new profile having datatype int.

Let’s, execute the following code to create the new database mail profile:

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = ‘Database Test Mail’,

       @description = ‘Profile for Test mail.’;

So, a new database mail profile is created and we can check information related to this in stored procedure sysmail_help_profile_sp.

EXEC msdb.dbo.sysmail_help_profile_sp

sysmail_add_profile

That’s all for the day friends. We will continue with another database mail stored procedure in next post. Have a nice day 🙂

Posted in Development, Stored Procedure

Sysmail_help_configure_sp – SQL Server Database Mail Stored Procedure – Part 2

Hi friends, today we will continue with another database mail stored procedure sysmail_help_configure_sp.

Sysmail_help_configure_sp stored procedure is used to display current Database mail configuration settings. The stored procedure sysmail_help_configure_sp is stored in msdb database and owned by schema dbo. Three part name is required if it is used in database other than msdb.

Syntax:

sysmail_help_configure_sp  [ [ @parameter_name = ] ‘parameter_name’ ]

Arguments:

@parameter_name is the name of configuration setting whose information we want to display.

When no parameter_name is defined then it displays the entire database mail configuration setting.

As we configure database mail configuration in our previous blog here.

We changed the configuration setting for Prohibited Extension in previous blog and added bat file extension in parameter value, so we can check the new changed value using this procedure as –

EXEC msdb.dbo.sysmail_help_configure_sp ‘ProhibitedExtensions’

sysmail_help_configure

To display all the configuration setting we will executed this stored procedure without passing any parameter.

EXEC msdb.dbo.sysmail_help_configure_sp

sysmail_configure_sp

So in this blog we learn how to display current database mail configuration setting values.

I will continue with other database mail stored procedure in my next blog. Have a nice day 🙂

Posted in Uncategorized

Sysmail_configure_sp – SQL Server Database Mail Stored Procedure – Part 1

Hi friends, from today we will discuss about database mail stored procedures. In next few days you will learn how to configure database mail, how to add mail accounts, how to add profile, and how to send mail.

Today, we will discuss about configuration setting for database mail using stored procedure sysmail_configure_sp.

Sysmail_configuration_sp stored procedure is used to change the configuration setting for database mail which applies to the entire SQL Server instance. This stored procedure is stored in msdb database and owned by schema dbo. If current database is not msdb then this stored procedure must be executed with three-part name. Permission to execute this stored procedure is default to sysadmin server role.

Syntax of sysmail_configure_sp is as follows:

sysmail_configure_sp [ [ @parameter_name = ] ‘parameter_name’ ]

[ , [ @parameter_value = ] ‘parameter_value’ ]

[ , [ @description = ] ‘description’ ]

Arguments

@parameter_name  is the name of the parameter to change.

@parameter_value is the new value of the parameter.

@description is the description of the parameter.

First we need to enable database mail service using following code –

use master

go

sp_configure ‘show advanced options’, 1

go

reconfigure with override

go

sp_configure ‘Database Mail XPs’, 1

go

reconfigure

go

There are many options to configure and change database mail which are:

AccountRetryAttempts – The number of times the external mail process attempts to send the e-mail message using each account in specified profile. Default value is 1.

AccountRetryDelay – The number of times, in seconds, for the external mail process to wait between attempts to send a message. Default value is 5000.

DatabaseMailExeMinimumLifeTime – The minimum amount of time, in seconds, that the external mail process remains active. Default value is 600.

DefaultAttachmentEncoding – The default encoding for e-mail attachment. Default value is MIME.

MaxFileSize – The maximum size of an attachment in bytes. Default value is 1000000.

ProhibitedExtensions – A list of extensions which cannot be sent as an e-mail attachment to an e-mail message. Default value is exe,dll,vbs,js.

LoggingLevel – Specify which messages are recorded in Database mail log with possible value 1, 2, or 3. Default value is 2.

  • 1 – This is normal mode. Logs only errors.
  • 2 – This is extended mode. Logs errors, warnings, and informational messages.
  • 3 – This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting.

Now let’s change the configuration value of Prohibited extension and add bat value in the list.

EXEC msdb.dbo.sysmail_configure_sp ‘ProhibitedExtensions’, ‘exe,dll,vbs,js,bat’;

After executing this procedure one more value gets added in the Prohibited Extension.

Similarly we can change the attachment size to 40000 –

EXEC msdb.dbo.sysmail_configure_sp ‘MaxFileSize’, ‘40000’;

Now to see the changed values execute below stored procedure –

EXEC msdb.dbo.sysmail_help_configure_sp

sysmail_configure_sp

We will continue with other database mail procedure in next blogs.

Hope you will like this post.

Posted in Stored Procedure

Sp_spaceused stored procedure in SQL Server

Hi folks, today you will learn about how to get information about allocated and unallocated space in database and database objects using stored procedure sp_spaceused.

Sp_spaceused stored procedure displays the disk space used by database, disk space used by table, indexed view, number of rows in a table. If objectname is not specified then it gives information about space used by current database.

Syntax of sp_spaceused is as follows:

sp_spaceused [[ @objname = ] ‘objname’ ]

[,[ @updateusage = ] ‘updateusage’ ]

Arguments

@objname is the name of table, indexed view whose space information is required. Objname is nvarchar(776) and has default value of NULL. When objname is not specified, it displays the information about database.

@updateusage used to indicates whether DBCC UPDATEUSAGE should be run or not. It can store values TRUE or FALSE having datatype varchar(5) with default value FALSE.

Now let’s run the procedure at database level and object level.

EXEC sp_spaceused

sp_spaceused1

When you run procedure without specifying any object two datasets returned in result sets.

First result set shows the following information:

Database_name – Name of database

Database_size – size of current database.

Unallocated Space – Unreserved space for database object.

Second result set shows following information:

Reserved – Total reserved space in a database.

Data – Total space used by database

Index_size – Total space used by indexed in a database.

Unused – Total reserved space for objects in a database.

Now, let’s run this procedure for a table:

EXEC sp_spaceused [Production.Products]

sp_spaceused2

Name – Name of table

Rows – Number of rows in a table

Reserved – Total reserved space of an object

Data –              Total space used by table

Index_size – Total space used by index in a table.

Unused – Total reserved space used by table.

That’s all folks for the day. Hope you like it.