0

I want to get 1000 rows from a table, but the result should be in in JSON format and then I need to export that file. But I am getting data in clipped/truncated format.

Command I used:

SELECT TOP (1000) [ID]
      ,[Request]
      ,[Response]
  FROM [AuditLogs] for JSON AUTO

Options I tried:

  1. I changed the default query result to exported to .rpt file. But when I open the rpt file in notepad++ the result are not completely exported.
  2. I changed the default behavior to give result in .txt format, but again the result is truncated.

Question: All I want is the JSON result to be exported from SSMS. Please guide me.

P.S: The response column data might be long strings.

Unbreakable
  • 7,776
  • 24
  • 90
  • 171

1 Answers1

1

Use below query - This will not truncate any data and the complete result will be stored in the @data variable.

  declare @data varchar(max) = (
  SELECT TOP (1000) [ID]
      ,[Request]
      ,[Response]
  FROM [AuditLogs] for JSON AUTO
  )   
print @data
Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19
  • Hi Mukesh, Can you see this link https://stackoverflow.com/questions/43951119/for-json-path-returns-less-number-of-rows-on-azure-sql – Unbreakable Feb 18 '19 at 05:23
  • In the Answer's `COMMENT` Section, they are telling to use `@declare` but how do I get result set. Please guide me – Unbreakable Feb 18 '19 at 05:24
  • use above mentioned query, It will print all the data in the SQL result window, you can copy and paste in the nodepad++ or any other editor. It will not truncate the data. – Mukesh Arora Feb 18 '19 at 05:54
  • To get the resultset use `SELECT @data as AColumnName` instead of `print` – Nick.Mc Feb 19 '19 at 04:20