title: Open JSON usage in SQL Server 2008
body: <p>I have developed some SQL scripts that are run in batch mode. They work perfectly in SQL Server 2017 (my test/development environment). My customer now wants me to port this to SQL Server 2008 (I know should never go backwards but the customer does not want to pay for 2017).</p>
  
  <p>My script looks like:</p>
  
  <pre><code>DECLARE @CJSON nvarchar(max)
  
  SELECT @CJSON = BulkColumn
  FROM OPENROWSET (BULK 'C:\Users\PaulB\desktop\Sched_Data\schedam.txt', SINGLE_CLOB) as j
  
  INSERT INTO Schedules (SchedID, Type, JobNo, TotalHrs, EmployeeID, SchedDate, StartTime, FinishTime)
      SELECT
          SchedID, Type, JobNo, TotalHrs, EmployeeID, SchedDate, StartTime, FinishTime
      FROM
          OPENJSON(@CJSON)
          WITH (SchedID NUMERIC N'$.ID', 
                Type CHAR(15) N'$.Type', 
                JobNo NVARCHAR(5) N'$.Reference', 
                TotalHrs DEC(4,2) N'$.TotalHours', 
                SchedDate DATE N'$.Date',  
                EmployeeID NUMERIC(6) N'$.Staff.ID', 
                Blocks NVARCHAR(MAX) N'$.Blocks' as JSON)
      OUTER APPLY
          OPENJSON(Blocks); 
          WITH (StartTime DATETIMEOFFSET N'$.ISO8601StartTime', 
                FinishTime DATETIMEOFFSET N'$.ISO8601EndTime' )
  go
  </code></pre>
  
  <p>The script reads a text file with JSON data and inserts into a table called schedules in this instance. When running the script it gets hung up on the <code>WITH OPENJSON</code> statement in SQL Server 2008 but perfect in SQL Server 2017.</p>
  
  <p>What suggestions do you have to make this work (besides upgrading to SQL Server 2017)?</p>
  
  <p>Thanks
  Paul</p>