Any pre-req to use JSON.sql or steps to install JSON.sql?

Question

I wanted to parse a string in JSON on a SQL Server 2008 R2 instance.

I read this awesome article by Phil over here and also looked at this SO question.

I downloaded the JSON.sql from simpletalk site and tried running on my local SQL Server instance.

But I got errors - (pasting it as such)

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

Msg 102, Level 15, State 1, Procedure ToJSON, Line 6
Incorrect syntax near 'READONLY'.

Msg 1087, Level 15, State 2, Procedure ToJSON, Line 67
Must declare the table variable "@Hierarchy".

Msg 1087, Level 15, State 2, Procedure ToJSON, Line 90
Must declare the table variable "@Hierarchy".

Msg 178, Level 15, State 1, Procedure ToJSON, Line 98
A RETURN statement with a return value cannot be used in this context.

Msg 102, Level 15, State 1, Procedure ToXML, Line 6
Incorrect syntax near 'READONLY'.

Msg 1087, Level 15, State 2, Procedure ToXML, Line 29
Must declare the table variable "@hierarchy".

Msg 1087, Level 15, State 2, Procedure ToXML, Line 43
Must declare the table variable "@hierarchy".

Msg 1087, Level 15, State 2, Procedure ToXML, Line 47
Must declare the table variable "@hierarchy".

Msg 1087, Level 15, State 2, Procedure ToXML, Line 53
Must declare the table variable "@hierarchy".

Msg 178, Level 15, State 1, Procedure ToXML, Line 65
A RETURN statement with a return value cannot be used in this context.

Is there anything I need to do before running JSON.sql?

Edit:When I checked SQL Server version using SSMS....Help --> About, it showed the following SQL Server 2008 R2

After @marc_s suggestion checked the version using @@version command. Surprisingly this showed my instance to be thus SQL Server 2005.

Any idea what could be happening?

Are you **sure** your local SQL Server instance is at least version **2008** ? That's where the table-valued parameters have been introduced (which seems to cause these errors). Run `SELECT @@VERSION` on your instance to see what engine version you're working with — marc_s, Jul 04 '14 at 09:29
Try to execute JSON.sql in small batches and see where the first error occurs. That is: select SQL code before first "GO" command and execute it (press F5). — Kenan Zahirovic, Jul 04 '14 at 09:48
@marc_s Thanks. Can you write the comment which you wrote as answer so that I could accept it as the right answer? — shrivb, Jul 04 '14 at 13:58

score 0 · Accepted Answer · answered Jul 04 '14 at 15:03

Are you sure your local SQL Server instance is at least version 2008?

That's where the table-valued parameters have been introduced (which seems to cause these errors judging from the error mentioning the READONLY keyword).

Run SELECT @@VERSION on your instance to see what engine version you're working with. You might have the Management Tools 2008 or 2008 R2, but your engine might still be a SQL Server 2005 - and the features (like table-valued parameter) really depends more on the engine rather than the management tools (most of the time).

Any pre-req to use JSON.sql or steps to install JSON.sql?

1 Answers1