Category Archives: MS SQL Server

How to set up Database Mail in SQL Server?

To send email using SQL Server, we need to setup Database mail. In this article, I will demonstrate how to set up SQL Server Database Mail.

Steps to Setup Database mail in SQL Server:
Step 1:
Expand “Management” folder and select “Database Mail”.

1

Step 2:
Right click on “Database Mail” and select “Configure Database Mail”.

2

Step 3:
After click on “Database Mail” you will get below screen, click on “Next” button.

3

Step 4:
Here select first option and click on “Next” button.

4

Step 5:
Here enter the “Profile Name” and “Description” and click on Add button to add SMTP account.

5

Step 6:
Enter “Account Name” and “Description”. In the Outgouing Mail Server(SMTP) section enter Email Address, Display Name, Reply E-Mail Address (If required), Server Name and Port Number. In SMTP authentication select “Basic Authentication” option enter User Name, Password and Confirm Password and then click on “Ok” button.

6

Step 7:
Click on “Next” button.

7

Step 8:
Click on “Next” button.

8

Step 9:
Click on “Finish” button.

9

Step 10:
Click on “Close” button.

10

Step 11:
After create the Account and the Profile successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, Below are the sample code:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

11

Now I have completed all configuration for “Database Mail” setup and ready to send email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters. Below are sample code to send a test mail:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='Test Profile',
@recipients='biswajeet@biswajeetsamal.com',
@subject='Test message',
@body='This is a test message.'

12

Also we can send test email from here.

Step 1:
Expand management folder and right click on “Database Email” and click on “Send Test E-Mail”.

13

Step 2:
Here select Database Mail profile, from which profile you want to send E-Mail, and enter to E-Mail address, subject, body and click on “Send Test E-Mail” button. And now check your To e-mail box.

14

Creating Job in SQL Server 2008

SQL Server Job:
A job is a specified series of actions that SQL Server Agent performs. Its internal setup consists of a windows service, that is used to execute jobs and a set of SQL Server tables that has the metadata about these jobs. We use jobs to define an administrative task that can be run one or more times and monitored for success or failure. If the Job encounters any problem, SQL Server Agent can record the event and notify you. A job can run on one local server or on multiple remote servers. In this article, I will demonstrate how to create a SQL Server Job.

Note: Before create a SQL Server Job, make sure that your SQL Server Agent is running.

In below example I have a database named “MyTestDB”. I want to backup this database from server, from Monday to Friday on a specific time. So here using SQL Server Agent Job I can automate this task. Not only for database backup, But we can automate a numerous types of task using SQL Server Agent Job.

Steps To Create SQL Server Job:

Step 1:
Click on SQL Server agent and right-click on “Jobs” and click on “New Job”.

1

Step 2:
There are many steps to create a Job, so let’s go one by one. In the first Step “General” provide a job name and description and select the database owner and click “Ok”.

2

Step 3:
Now click on second Step “Steps”. Click on the “New” button for a new step and provide a step name, type and select database and write a command to save in a location and then click “Ok”.

Note: In the command area, you can write any SQL query or stored procedures according to your requirement for execute in this job.

3

4

Step 4:
Now click on the third step “Schedules”. Click on the “New” button for a new schedule and provide name, schedule type, frequency and click “Ok”.

Note: For this example my job schedule will be weekly at 1 AM.

5

6

Step 5:
Now click on the fourth step “Alerts”. Click on the “Add” button and provide alert name, database name and click “Ok”.

Note: An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

7

8

Step 6:
Now click on the fifth step “Notifications” and select how you want notifications. Here you have option for sending email when job complete or it runs successfully or it fails. Chose the appropriate option as per your requirement and click “Ok”.

9

Step 7:
The final step is the “Targets”, that shows you the options of the target server, you can also select multiple target servers.

10

And here the new job has created in SQL Server Agent.

11

How to split a comma delimited string in SQL Server

In this article I’ll demonstrate how to split a comma delimited string in SQL Server.
There can be different types of delimiters like Comma ‘ , ‘, vertical bar ‘ | ‘, Single space or a Tab.
Here in below example i’m using Comma ‘ , ‘ delimiter.

Here is sample table:

download (1)

And here is the expected output:

download (2)

Create a sample table and insert data:

CREATE TABLE Test 
( 
   Id INT, 
   Country VARCHAR(200) 
) 
GO 
 
-- Insert Sample Data 
INSERT INTO Test SELECT
1, 'India, USA, UK' UNION ALL SELECT
2, 'Singapore, Dubai, Japan'  UNION ALL SELECT
3, 'Vietnam, South Africa, Ireland'
GO
 
-- Verify the Sample Data of Test Table
SELECT Id, Country FROM Test

Here is the query for How to split a comma delimited string:

;WITH cde AS
 
( 
    SELECT
        Id, 
        CAST('<m>' + REPLACE(Country,  ',' , '</m><m>') + '</m>' AS XML) AS Country
    FROM Test 
) 
SELECT
    Id, 
    Split.a.value('.', 'VARCHAR(100)') AS Country
FROM cde
CROSS APPLY Names.nodes('/M') Split(a)

Removing hidden CRLF from a string in MS SQL Server

Sometimes in SQL string the hidden CRLF character looks like a blank space, but when we copy/paste it into MsWord, we can see that CLRF characters.

To remove this hidden CRLF charcter:

UPDATE dbo.MyTable SET MyColumn = REPLACE(MyColumn,CHAR(160),'')