It’s quite easy to encounter error Msg 214, Level 16 when executing stored procedures such as sp_executesql or sp_describe_first_result_set.

Fortunately it’s easy to fix too!

The most common reason for getting this error is that you forgot to prefix your string with N.

Therefore, to fix this issue, try prefixing your string with N.

Example of Code that Causes the Error

The following code causes this error.

EXEC sp_executesql 'SELECT * FROM Cats'; 

Result:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

The reason for this error is that the first argument of the sp_executesql procedure must be either a Unicode constant or a Unicode variable.

Therefore, when you’re providing the argument as a string, you need to prefix it with N.

The Solution

Here’s the solution to the above issue.

EXEC sp_executesql N'SELECT * FROM Cats'; 

Result:

+---------+-------------+
| CatId   | CatName     |
|---------+-------------|
| 1       | Meow        |
| 2       | Fluffy      |
| 3       | Scratch     |
| 4       | Bulldog     |
| 5       | King George |
| 6       | Sharp       |
| 7       | Fritz       |
| 8       | Garfield    |
| 9       | Boss        |
+---------+-------------+

Note that this has nothing to do with the columns in the table. For example, in my Cats table, the CatId column is int and the CatsName column is varchar(60).

Variables

If you’re passing a variable instead of a string, you could change the variable type. Doing this will save you from having to prefix the argument with N.

Here’s an example of a variable that causes the error.

DECLARE @tsql_query varchar(max);
SET @tsql_query = 'SELECT * FROM Cats';
EXEC sp_executesql @tsql_query;

Result:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Once again we get the 214 error, because the argument isn’t a Unicode constant or a Unicode variable.

We can fix this by declaring the variable as a Unicode variable.

DECLARE @tsql_query nvarchar(max);
SET @tsql_query = 'SELECT * FROM Cats';
EXEC sp_executesql @tsql_query;

Result:

+---------+-------------+
| CatId   | CatName     |
|---------+-------------|
| 1       | Meow        |
| 2       | Fluffy      |
| 3       | Scratch     |
| 4       | Bulldog     |
| 5       | King George |
| 6       | Sharp       |
| 7       | Fritz       |
| 8       | Garfield    |
| 9       | Boss        |
+---------+-------------+

One More Example

The above examples use the sp_executesql procedure, but you can get this error any time a procedure is expecting Unicode but doesn’t get it.

Another system procedure that accepts a Unicode argument is sp_describe_first_result_set. Therefore, we can force the same error using that procedure.

EXEC sp_describe_first_result_set 
    @tsql = 'SELECT * FROM Cats', 
    @params = null, 
    @browse_information_mode = 1;

Result:

Msg 214, Level 16, State 21, Procedure sp_describe_first_result_set, Line 1
Procedure expects parameter '@tsql' of type 'nvarchar(max)'.

Although the exact wording is slightly different, it’s the same error (Msg 214, Level 16), and has the same fix.