How to set up mail notification for SQL Server job failures

When we set up SQL Server Job, its is a very common requirement ‘need to get email notification once Job is failed’. It’s not difficult, just need few clicks in SQL Server Management Studio to implement this requirement.

Overview Steps

  1. Configure Database Mail.
  2. Set up SQL Server Agent Alert System .
  3. Create an Operator.
  4. Set up SQL Server job notifications

Configuring Database Mail

Open SQL Server Management Studio (SSMS), expand Management and right-click on Database Mail and then click Configure Database Mail.

SQL Server Management Studio
Database Mail Configuration Wizard

Select “Set up Database Mail by performing the following tasks” and Click Next.

Click Yes and then click Next if mail hasn’t been configured yet.

Give the New Profile a name and then click Add.

create new profile

Fill in the correct SMTP information as below

click Next

New Profile

Check the option to make the new profile Public. If the profile isn’t set to Public

Manage Profile Security

Review the System Parameters , please keep the default settings if your are not sure how to adjust them. Click Next and Finish the Mail set up.

Configure system parameters

You should see that each Action was completed and has a Status of Success. Click Close.

Configuration Complete Test

Set up SQL Server Agent Alert System

Right-click SQL Server Agent and select Properties.

SQL Server Agent Properties

Click on Alert System

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

SQL Server Agent Operator

Type in the recipient email address in the E-mail name and click OK.

Set up SQL Server job notifications

Expand SQL Server Agent and click on Jobs. Find the job you want to change, right click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job fails. Click OK.

How To Get Day Of Week In SQL Server?

SQL Server has a couple of inbuilt functions to get the day of week from the given date. To get the name of the day of week, you can use DATENAME function and to get the number of the day of week, you can use DATEPART function.

Example

SELECT DATENAME(WEEKDAY, GETDATE())
/* Result */
Monday

SELECT DATEPART(WEEKDAY, GETDATE())
/* Result */
2

By default, SQL server always uses Sunday as the first day of the week. You can use SET DATEFIRST 1 to set Monday as the first day of the week.

Why left join not returning nulls?

Issue

I am trying to get all products and category name if category is still active with below left join script. But I cannot get production information if category is not active. What’s wrong with this left join script?

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
where c.isactive = 1 or c.isactive is null

Reason

To find out what’s the issue of this script have, we need to understand order of a SQL query. In the script above we used SELECT, FROM, LEFT JOIN and WHERE

  1. FROM and JOIN: The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried
  2. WHERE: Once we have the total working set of data, the WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.
  3. SELECT: Any expressions in the SELECT part of the query are finally computed.

The WHERE clause is execution after the ON clause. which means the predicates in the ON clause are applied to the table before the join, WHERE clause is semantically applied to the result of the join.

So below script will get all the data from product table and will keep categories data as null if cannot find category.

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid

WHERE clause is applied to the JOIN result which will remove products result if category missing.

where c.isactive = 1 or c.isactive is null

Solution

if we want to get all product information even if categories is missing or is not active, we need to changed the WHERE clause to ON clause as below:

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
and (c.isactive = 1 or c.isactive is null )

SQL server nchar and nvarchar size

This blog I will show you a very trick issue thing a bout nchar and nvarchar actual size and storage size.

Issue

When I use sp_help to check the table property. I got below result:

which shows length 100

but when I using SSMS to check the column size, it shows nvarchar(50)

so what’s the different between sp_help and SSMS design view?

Finding

I checked sp_help , it is get the max length field from sys.all_columns. In Microsoft document website, it mentioned maximum length (in bytes) of the column.

But we know nchar and nvarchar, the storage size is two times n bytes (n is the actual number of characters ). so this explained, sp_help is not real character size, it is the storage size in bytes. But SSMS design view is using real character size.

For details you can check Microsoft article nchar and nvarchar and sys.all_columns

SQL SERVER : Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

Recently, I try to setup a new SQL SERVER on AWS. When I try to connect to server via private IP. I got below SQL Login error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 172.31.23.96]
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.23.96]

WORKAROUND/SOLUTION

Loopback check can be removed by adding a registry entry as follows:

  • Edit the registry using regedit. (Start –> Run > Regedit )
  • Navigate to: HKLM\System\CurrentControlSet\Control\LSA
  • Add a DWORD value called “DisableLoopbackCheck”
  • Set this value to 1

Refer : https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/12/19/error-message-login-failed-the-login-is-from-an-untrusted-domain-and-cannot-be-used-with-windows-authentication/

SQL Server Date and Time styles

One of the most frequently question I asked is which date format style number I should to use. Here’s a summary of different date and time style matrix that we can used in T-SQL as part of the CONVERT function.

Matrix of Date and Time style

Style numberInput/Output Format
100 mon dd yyyy hh:miAM (or PM)
1011 = mm/dd/yy
101 = mm/dd/yyyy
1022 = yy.mm.dd
102 = yyyy.mm.dd
1033 = dd/mm/yy
103 = dd/mm/yyyy
1044 = dd.mm.yy
104 = dd.mm.yyyy
1055 = dd-mm-yy
105 = dd-mm-yyyy
1066 = dd mon yy
106 = dd mon yyyy
1077 = Mon dd, yy
107 = Mon dd, yyyy
108hh:mi:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
11010 = mm-dd-yy
110 = mm-dd-yyyy
11111 = yy/mm/dd
111 = yyyy/mm/dd
11212 = yymmdd
112 = yyyymmdd
113 dd mon yyyy hh:mi:ss:mmm (24h)
114hh:mi:ss:mmm (24h)
 120 yyyy-mm-dd hh:mi:ss (24h)
 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 mm/dd/yy hh:mi:ss AM (or PM)
23yyyy-mm-dd
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yyyy hh:mi:ss:mmmAM

Sample convert string to datetime

select convert(datetime, ‘2015/12/31 13:58:03’,111)

select convert(datetime, ‘2012-11-07T18:26:20.096Z’,127)

How to compare SQL Server Table schemas

We can use sp_help to display all table schema

sp_help import_OAN_clients

Will get below result:

But how we can compare two tables schema quickly via T-SQL? We can use INFORMATION_SCHEMA.COLUMNS to compare two tables schema easily, but this way cannot compare table cross database or instance. In this blog I will show how to compare it with internal function sys.dm_exec_describe_first_result_set

Create Demo Tables

CREATE TABLE demo1(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] datetime NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](20) NULL
) 
CREATE TABLE demo2(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] [nvarchar](30) NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL
) 

How sys.dm_exec_describe_first_result_set is working

If you want to know all detail specs of sys.dm_exec_describe_first_result_set, you can go to microsoft developer community

before we start compare two tables, let’s see what kind of information the function can get

select * from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1)

We can see the returned schema as below:

Doing Comparisons

To do two tables comparison, we just need to simply doing OUTER JOIN between the two function results. (To do comparisons between to database or instance, please use linked servers and full part name to address the table. like product.dbo.demo2)

select a.column_ordinal,b.column_ordinal,a.name,b.name ,a.is_nullable,b.is_nullable ,a.system_type_name,b.system_type_name,a.max_length,b.max_length,a.precision,b.precision,a.scale,b.scale
from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1) as a
full outer join sys.dm_exec_describe_first_result_set ( N’select * from dbo.demo2′, NULL, 1) as b
on a.name = b.name

We can see the comparison result between to tables now.

SQL SERVER BCP UTF8 import and export, support UTF8 after SQL SERVER 2014SP2

The bulk copy program utility (bcp) is a very powerful bulk copy tool between database and flat file. Normally used for large number of data import and export. But if you sql server version is older then 2014 SP2, unlucklly you cannot process UTF8 data even if there is a code page parameter -C , but it doesn’t support UTF8. Refer to Microsoft community document

code_pageSpecific code page number; for example, 850.

** Important ** SQL Server does not support code page 65001 (UTF-8 encoding).

If you want to process UTF8, then must need to upgrade SQL Server to version 2014 SP2 or later. Once you installed the upgrade, then you can use BCP to import or export UTF8 as below:

To import UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users in “D:\test.csv” -c -C 65001

To export UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users out ” D:\test.csv ” -c -C 65001

SQL performance tuning: variable in where condition cause performance issue

I am seeing a hug performance difference between two quires that are almost identical.

Query 1:

declare @type varchar(10) = ‘OR-‘
select * from client_details t1, Orders t2
where t1.client_code = @type +t2.client_code

Query 2:

select * from client_details t1, Orders t2
where t1.client_code = ‘OR-‘+t2.client_code

client_details has around 400 thousands records, Orders table has around 200 thousands.

Query 1 take long then 5 hours to processing, but Query 2 is finished within few minutes.

I did lots of search and testing, it seems SQL SERVER optimizer issue.

Please the difference of Literal Values and local variable from Kendra Little’s Blog:

https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

Solution to fix my issue, I created an additional column called type and then use the value from this new column:

select * from client_details t1, Orders t2
where t1.client_code = t2.OrderType +t2.client_code