Tag: errors

OPENJSON “Incorrect syntax near the keyword ‘with’.” in SQL Server (SOLVED)

Posted on by Ian

If you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with'”, one possibility is that you really do have a syntax error.

But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.

Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.

Continue reading →

How to Fix “Invalid object name ‘OPENJSON’.” in SQL Server

Posted on by Ian

If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.

OPENJSON() is only available under compatibility level 130 or higher.

To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.

Continue reading →

SQL Server Error 7222: “Only a SQL Server provider is allowed on this instance”

Posted on by Ian

I was trying to set up a up a linked server from SQL Server to PostgreSQL when I got error Msg 7222, Level 16 “Only a SQL Server provider is allowed on this instance”.

The message is reasonably self explanatory, but it still didn’t tell me what it was about my instance that prevented it from being allowed.

It didn’t take long to find out.

Continue reading →

How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s)…” in SQL Server

Posted on by Ian

If you encounter error Msg 11535, Level 16 while trying to execute a stored procedure, it’s because you didn’t define enough result sets in the WITH RESULT SETS clause.

Some stored procedures return multiple result sets. When using the WITH RESULT SETS clause, you need to define each expected result set. You need to do this even if you only want to change the definition of one or some of the result sets.

To fix this error, simply add the additional result sets to the WITH RESULT SETS clause, each separated by a comma.

You could also fix it by removing the WITH RESULT SETS clause, but I’ll assume you’re using it for a reason (i.e. you need to redefine the result set returned by the procedure).

Continue reading →

How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set…” Msg 11537 in SQL Server

Posted on by Ian

If you encounter error Msg 11537, Level 16 in SQL Server, chances are that you’re trying to execute a stored procedure by using the WITH RESULT SETS clause, but you haven’t included all the columns in your definition.

When you use the WITH RESULT SETS clause in the EXECUTE/EXEC statement, you must provide a definition for all columns returned by the stored procedure. If you don’t, you’ll get this error.

Continue reading →

SQL Server Error 111: “…must be the first statement in a query batch”

Posted on by Ian

In SQL Server, a batch is a group of one or more T-SQL statements sent at the same time from an application to SQL Server for execution.

If you encounter an error like this:

Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

It’s probably because you’re combining the statement with other statements in the same batch, which is not allowed in batches.

The first part of the error message will depend on the actual statement that you’re using in your batch. In my case it’s CREATE VIEW, but it could just as easily be CREATE PROCEDURE, CREATE FUNCTION, etc if those are the statements you’re using.

Continue reading →

SQL Server Error 110: There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Posted on by Ian

Error message 110 is a commonly encountered error in SQL Server when inserting data into a table. The full error looks like this:

Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This happens when you specify less columns in the INSERT statement than the number of values that you’re trying to insert with the VALUES clause.

This will occur if you accidentally omit one or more columns from the INSERT statement.

You’d get a similar (but technically different) error if you tried to do the opposite – specify more columns in the INSERT statement than you try to insert.

Continue reading →

SQL Server Error 213: Column name or number of supplied values does not match table definition.

Posted on by Ian

Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Continue reading →

“Query not allowed in Waitfor” Error 101 in SQL Server

Posted on by Ian

If you’re using the WAITFOR statement in SQL Server, and you get the following error, it’s probably because you’re providing a query as its argument.

Msg 101, Level 15, State 1, Line 1
Query not allowed in Waitfor.

The WAITFOR statement doesn’t accept queries for its “wait for” period. It only accepts a specific time or an interval.

Actually, it does accept RECEIVE statements, but this is only applicable to Service Broker messages, so if you’re not using Service Broker messages, the above error is quite self-explanatory.

Continue reading →