If you’re using VARCHAR(MAX) columns in SQL then you may occasionally want to view the stored values in SQL Server Management Studio (SSMS) as part of the debugging process.

Because a VARCHAR(MAX) field can store up to 2GB of data SSMS truncates the data returned to 8200 characters in the results tab in order to avoid hogging too much memory.

If you want to view this data anyway then you can cast it as XML which is not truncated, you can then copy the returned XML out, remove the tags and view your data.

select
	convert(xml,'<xml><![CDATA[' + jsonObject + ']]></xml>')
from
	[live].[detail]
where
	id = @resourceId

It’s not elegant but it gets the job done!


Leave a Reply

Your email address will not be published. Required fields are marked *


Fatal error: Uncaught GuzzleHttp\Exception\ClientException: Client error: `POST https://dc.services.visualstudio.com/v2/track` resulted in a `400 Invalid instrumentation key` response: {"itemsReceived":1,"itemsAccepted":0,"errors":[{"index":0,"statusCode":400,"message":"Invalid instrumentation key"}]} in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php:113 Stack trace: #0 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Middleware.php(66): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(203): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(156): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\P in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 113