CODEWITHSUNDEEP
sql-serversql-server-2008
whoisearth
whoisearth

Reputation: 4160

sql query - how to remove character limit on result

I'm running the following query and it's hitting a character limit which I don't want.

SELECT
    '{"data":[', 
    (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' 
                   FROM master..sysdatabases 
                   ORDER BY [Name] 
                   FOR XML PATH('')), 1, 1, '')) ,']}' 
FOR XML PATH('')

Essentially we have more databases than the query wants to return which in turn breaks the json return.

The issue was not around the character limit imposed by the database but in the value returned from the query. as already stated it was a matter of enforcing a larger limit than the default when returning the results.

Upvotes: 0

Views: 1620

Answers (1)

vamsi
vamsi

Reputation: 352

Try the following by casting into ntext. This worked for over 200 databases. By the way http://jsonprettyprint.com/ seems to have a character limit itself

  SELECT
   '{"data":[', 
    (SELECT  convert(ntext,STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' 
                   FROM master..sysdatabases 
                   ORDER BY [Name] 
                   FOR XML PATH('')), 1, 1, ''))) ,']}' 
FOR XML PATH('')

Upvotes: 1

Related Questions