Tag Archives: SQL Server Tips & Tricks

How SOQL differs from SQL?

  • No INSERT, UPDATE or DELETE statements, only SELECT.
  • No command execution.
  • No wild cards for fields, all fields must be explicitly typed.
  • No JOIN statement. However, we can include information from parent objects like Select name, phone, account.name from contact.
  • No UNION operator.
  • Queries cannot be chained together.

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

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),'')