title: PYODBC truncates the response of a SQL Server FOR JSON query
body: <p>I'm having issues trying to get a SQL Server JSON result set to play nicely with PYODBC. My Python/Flask skills aren't the best, so I'm not entirely sure if this is something stupid I'm doing, or that the driver doesn't work with JSON.</p>
  
  <p>The failing process is calling a Stored Procedure that returns the results of a <code>SELECT</code> statement using <code>FOR JSON PATH</code>. The procedure itself is fine and the result set looks correct in SSMS.</p>
  
  <p>However, within the following block of code, the line that assigns a value to <code>search_results</code> is throwing an error.</p>
  
  <pre><code>with DB() as cnxn:
      results = cnxn.query('dbo.getLocationByTrain', params)
      search_results = json.loads(results.fetchone()[0]);
  </code></pre>
  
  <p>The error is:</p>
  
  <pre><code>Traceback (most recent call last):
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1982, in wsgi_app
      response = self.full_dispatch_request()
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1614, in full_dispatch_request
      rv = self.handle_user_exception(e)
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1517, in handle_user_exception
      reraise(exc_type, exc_value, tb)
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\_compat.py", line 33, in reraise
      raise value
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
      rv = self.dispatch_request()
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\env\lib\site-packages\flask\app.py", line 1598, in dispatch_request
      return self.view_functions[rule.endpoint](**req.view_args)
    File "C:\Users\Mark\source\repos\CommuteTo\CommuteTo\CommuteTo\views.py", line 81, in search
      search_results = json.loads(results.fetchone()[0]);
    File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\__init__.py", line 354, in loads
      return _default_decoder.decode(s)
    File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\decoder.py", line 339, in decode
      obj, end = self.raw_decode(s, idx=_w(s, 0).end())
    File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\json\decoder.py", line 355, in raw_decode
      obj, end = self.scan_once(s, idx)
  json.decoder.JSONDecodeError: Unterminated string starting at: line 1 column 2028 (char 2027)
  </code></pre>
  
  <p>If I remove the <code>json.loads</code> and instead <code>jsonify</code> the result, the error obviously goes away, but I can see the result set returned to Flask is incomplete:</p>
  
  <pre><code>with DB() as cnxn:
      results = cnxn.query('dbo.getLocationByTrain', params)
      search_results = results.fetchone()[0];
  return jsonify(search_results) 
  </code></pre>
  
  <p>Result in Flask:</p>
  
  <pre><code>"[{\"Name\":\"ABBEY WOOD\",\"Latitude\":\"51.490772\",\"Longitude\":\"0.120326\",\"LatestDepartureTime\":\"08:18:00\",\"MinJourneyTime\":14,\"MaxJourneyTime\":17,\"schools\":[{\"Name\":\"Abbey Wood Nursery School\",\"Address1\":\"Dahlia Road\",\"PostCode\":\"SE2 0SX\",\"OverallEffectiveNess\":2},{\"Name\":\"Bannockburn Primary School\",\"Address1\":\"Plumstead High Street\",\"PostCode\":\"SE18 1HE\",\"OverallEffectiveNess\":1},{\"Name\":\"Gallions Mount Primary School\",\"Address1\":\"Purrett Road\",\"PostCode\":\"SE18 1JR\",\"OverallEffectiveNess\":2},{\"Name\":\"Boxgrove Primary School\",\"Address1\":\"Boxgrove Road\",\"PostCode\":\"SE2 9JP\",\"OverallEffectiveNess\":2},{\"Name\":\"De Lucy Primary School\",\"Address1\":\"Cookhill Road\",\"PostCode\":\"SE2 9PD\",\"OverallEffectiveNess\":2},{\"Name\":\"Heronsgate Primary School\",\"Address1\":\"Whinchat Road\",\"PostCode\":\"SE28 0EA\",\"OverallEffectiveNess\":1},{\"Name\":\"Linton Mead Primary School\",\"Address1\":\"Central Way\",\"PostCode\":\"SE28 8DT\",\"OverallEffectiveNess\":2},{\"Name\":\"Greenslade Primary School\",\"Address1\":\"Erindale\",\"PostCode\":\"SE18 2QQ\",\"OverallEffectiveNess\":2},{\"Name\":\"St Patrick's Catholic Primary School\",\"Address1\":\"Griffin Road\",\"PostCode\":\"SE18 7QG\",\"OverallEffectiveNess\":2},{\"Name\":\"St Thomas A Becket Roman Catholic Primary School\",\"Address1\":\"Mottisfont Road\",\"PostCode\":\"SE2 9LY\",\"OverallEffectiveNess\":2},{\"Name\":\"St Margaret Clitherow Catholic Primary School\",\"Address1\":\"Cole Close\",\"PostCode\":\"SE28 8GB\",\"OverallEffectiveNess\":2},{\"Name\":\"Bishop John Robinson Church of England Primary School\",\"Address1\":\"Hoveton Road\",\"PostCode\":\"SE28 8LW\",\"OverallEffectiveNess\":2},{\"Name\":\"Plumstead Manor School\",\"Address1\":\"Old Mill Road\",\"PostCode\":\"SE18 1QF\",\"OverallEffectiveNess\":3},{\"Name\":\"Hawksmoor School\",\"Address1\":\"Bentham Road\",\"PostCode\":\"SE28 8AS\",\"OverallEffectiveNess\":1},{\"Name\":\"Belmont Primary School\",\"Address1\":\"Belmont Road\",\"PostCode\":\"DA8 1LE\",\"OverallEffectiveNess\":2},{\"Name\":\"Parkway Primary School\",\"Address1\":\"Alsike Road\",\"PostCode\":\"DA18 4DP\",\"OverallEffectiveNess\":2},{\"Name\":\"Belvedere Infant School\",\"Address1\":\"Mitchell"
  </code></pre>
  
  <p>SQL Server Result:</p>
  
  <pre><code>[{"Name":"ABBEY WOOD","Latitude":"51.490772","Longitude":"0.120326","LatestDepartureTime":"08:18:00","MinJourneyTime":14,"MaxJourneyTime":17,"schools":[{"Name":"Abbey Wood Nursery School","Address1":"Dahlia Road","PostCode":"SE2 0SX","OverallEffectiveNess":2},{"Name":"Bannockburn Primary School","Address1":"Plumstead High Street","PostCode":"SE18 1HE","OverallEffectiveNess":1},{"Name":"Gallions Mount Primary School","Address1":"Purrett Road","PostCode":"SE18 1JR","OverallEffectiveNess":2},{"Name":"Boxgrove Primary School","Address1":"Boxgrove Road","PostCode":"SE2 9JP","OverallEffectiveNess":2},{"Name":"De Lucy Primary School","Address1":"Cookhill Road","PostCode":"SE2 9PD","OverallEffectiveNess":2},{"Name":"Heronsgate Primary School","Address1":"Whinchat Road","PostCode":"SE28 0EA","OverallEffectiveNess":1},{"Name":"Linton Mead Primary School","Address1":"Central Way","PostCode":"SE28 8DT","OverallEffectiveNess":2},{"Name":"Greenslade Primary School","Address1":"Erindale","PostCode":"SE18 2QQ","OverallEffectiveNess":2},{"Name":"St Patrick's Catholic Primary School","Address1":"Griffin Road","PostCode":"SE18 7QG","OverallEffectiveNess":2},{"Name":"St Thomas A Becket Roman Catholic Primary School","Address1":"Mottisfont Road","PostCode":"SE2 9LY","OverallEffectiveNess":2},{"Name":"St Margaret Clitherow Catholic Primary School","Address1":"Cole Close","PostCode":"SE28 8GB","OverallEffectiveNess":2},{"Name":"Bishop John Robinson Church of England Primary School","Address1":"Hoveton Road","PostCode":"SE28 8LW","OverallEffectiveNess":2},{"Name":"Plumstead Manor School","Address1":"Old Mill Road","PostCode":"SE18 1QF","OverallEffectiveNess":3},{"Name":"Hawksmoor School","Address1":"Bentham Road","PostCode":"SE28 8AS","OverallEffectiveNess":1},{"Name":"Belmont Primary School","Address1":"Belmont Road","PostCode":"DA8 1LE","OverallEffectiveNess":2},{"Name":"Parkway Primary School","Address1":"Alsike Road","PostCode":"DA18 4DP","OverallEffectiveNess":2},{"Name":"Belvedere Infant School","Address1":"Mitchell Close","PostCode":"DA17 6AA","OverallEffectiveNess":2},{"Name":"Castilion Primary School","Address1":"Copperfield Road","PostCode":"SE28 8QA","OverallEffectiveNess":1},{"Name":"St Thomas More Catholic Primary School","Address1":"Sheldon Road","PostCode":"DA7 4PH","OverallEffectiveNess":1},{"Name":"St John Fisher Catholic Primary School","Address1":"Kale Road","PostCode":"DA18 4BA","OverallEffectiveNess":2},{"Name":"St Paul's Academy","Address1":"Finchale Road","PostCode":"SE2 9PX","OverallEffectiveNess":2},{"Name":"Discovery Primary School","Address1":"Battery Road","PostCode":"SE28 0JN","OverallEffectiveNess":2},{"Name":"Alexander McLeod Primary School","Address1":"Fuchsia Street","PostCode":"SE2 0QS","OverallEffectiveNess":2},{"Name":"Conway Primary School","Address1":"Gallosson Road","PostCode":"SE18 1QY","OverallEffectiveNess":2},{"Name":"Waterside School","Address1":"Robert Street","PostCode":"SE18 7NB","OverallEffectiveNess":2},{"Name":"Trinity Church of England School, Belvedere","Address1":"Erith Road","PostCode":"DA17 6HT","OverallEffectiveNess":2},{"Name":"East Wickham Primary Academy","Address1":"Wickham Street","PostCode":"DA16 3BP","OverallEffectiveNess":2},{"Name":"Welling School","Address1":"Elsa Road","PostCode":"DA16 1LB","OverallEffectiveNess":2},{"Name":"Willow Bank Primary School","Address1":"Seacourt Road","PostCode":"SE2 9XB","OverallEffectiveNess":2},{"Name":"Belvedere Junior School","Address1":"Mitchell Close","PostCode":"DA17 6AA","OverallEffectiveNess":2},{"Name":"St Augustine of Canterbury CofE Primary School","Address1":"St Augustine's Road","PostCode":"DA17 5HP","OverallEffectiveNess":2},{"Name":"Brampton Primary Academy","Address1":"Brampton Road","PostCode":"DA7 4SL","OverallEffectiveNess":4},{"Name":"Woolwich Polytechnic School","Address1":"Hutchins Road","PostCode":"SE28 8AT","OverallEffectiveNess":1},{"Name":"Hillsgrove Primary School","Address1":"Sidmouth Road","PostCode":"DA16 1DR","OverallEffectiveNess":3},{"Name":"Windrush Primary School","Address1":"2 Bentham Road","PostCode":"SE28 8AR","OverallEffectiveNess":1},{"Name":"Timbercroft Primary School","Address1":"Timbercroft Lane","PostCode":"SE18 2SG","OverallEffectiveNess":2},{"Name":"Rockliffe Manor Primary School","Address1":"Bassant Road","PostCode":"SE18 2NP","OverallEffectiveNess":2},{"Name":"Willow Dene School","Address1":"Swingate Lane","PostCode":"SE18 2JD","OverallEffectiveNess":2},{"Name":"South Rise Primary School","Address1":"Brewery Road","PostCode":"SE18 7PX","OverallEffectiveNess":2},{"Name":"Bedonwell Infant and Nursery School","Address1":"Bedonwell Road","PostCode":"DA17 5PF","OverallEffectiveNess":1},{"Name":"Bedonwell Junior School","Address1":"Bedonwell Road","PostCode":"DA17 5PF","OverallEffectiveNess":2},{"Name":"Pathways Short Stay School","Address1":"Pathways Short Stay School","PostCode":"SE2 9TA","OverallEffectiveNess":2},{"Name":"Northwood Primary School","Address1":"Northwood Place","PostCode":"DA18 4HN","OverallEffectiveNess":2},{"Name":"St Michael's East Wickham Church of England Voluntary Aided Primary School","Address1":"Wrotham Road","PostCode":"DA16 1LS","OverallEffectiveNess":1},{"Name":"Jubilee Primary School","Address1":"Crowden Way","PostCode":"SE28 8JB","OverallEffectiveNess":3}]}]
  </code></pre>
  
  <p>Somewhere it would appear as though PYODBC is truncating to around 2050 characters? </p>
  
  <p>I would guess I'm not the first person to try to use <code>FOR JSON</code> and PYODBC, so what am I doing wrong?</p>
  
  <p>My connection string (if it's important):</p>
  
  <pre><code>cnxn = r'Driver={SQL Server};Server=MARKSLAPTOP\MSSQLSERVER17;Database=MyDBName;Trusted_Connection=yes;'
  </code></pre>
  
  <p>And <code>cnxn.query</code> just calls <code>cursor.execute(sql, params)</code>.</p>
  
  <p><strong>Edit</strong>
  The issue sort of sounds similar to <a href="https://stackoverflow.com/questions/33878291/how-to-get-entire-varcharmax-column-with-python-pypyodbc">this</a> query, but I’m already using the standard SQL Server driver... </p>