Tag: t-sql

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 →

How to Use the BETWEEN Operator in SQL Server

Posted on by Ian

The SQL Server BETWEEN operator allows you to test between a range of values in your queries. For example, you could test that a value is between two numbers.

The BETWEEN operator is inclusive, meaning that it includes the values that you specify within the range. That is, it includes values that are greater than or equal to the lower value, and values that are less than or equal to the higher value. Therefore it’s like using >= and <=.

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 Redefine the Columns Returned by a Stored Procedure in SQL Server

Posted on by Ian

When you execute a stored procedure that returns a result set in SQL Server, the columns returned are defined in the stored procedure.

But did you know that you can redefine those columns?

What I mean is, you can change the names and/or the data type of the columns returned in the result set.

This could save you from having to fiddle with the column headers and data formats in the event you needed to use that result set in another setting.

For example, if a stored procedure returns a datetime2 column, but you only need the date part, you could specify date for that column, and your result set will only include the date part.

And the best part is that you can do it as part of the EXECUTE statement. No need to massage the data after executing the procedure. way to do this is by using the WITH RESULT SETS clause of the EXECUTE statement.

Continue reading →