{"id":1100,"date":"2013-11-28T00:23:17","date_gmt":"2013-11-27T13:23:17","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=1100"},"modified":"2013-11-28T00:23:17","modified_gmt":"2013-11-27T13:23:17","slug":"exec-sp_executesql-nvarcharmax-truncating-4000-characters","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2013\/11\/28\/exec-sp_executesql-nvarcharmax-truncating-4000-characters\/","title":{"rendered":"exec sp_executesql nvarchar(max) truncating at 4000 characters"},"content":{"rendered":"<div id=\"ntsbl-3024876144\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><h2>The Problem<\/h2>\n<p>Sometimes you may need to write dynamic SQL. <\/p>\n<p>One example is writing query for running over linked server connections. You might write something like this<\/p>\n<p>[sql]<br \/>\nCreate Procedure prcTest<br \/>\n    @start datetime<br \/>\nas<br \/>\nBegin<br \/>\n    Declare @sql nvarchar(max)<\/p>\n<p>    set @sql = &#8216;Insert Into #tblTemp<br \/>\n    select * from OpenQuery(LINKSERVER, &#8221;Select * from dbo.tblRemote r<br \/>\n        inner join tblRemote2 r2 on r.Id = r2.fkId<br \/>\n    &#8230;. lots more sql x 4000+ characters<br \/>\n    Where r.DateCreated &gt; &#8221;&#8221;&#8217; + Convert(varchar, @start, 106) + &#8221;&#8221;&#8217;<br \/>\n    &#8221;)&#8217;;<\/p>\n<p>    exec sp_executesql @sql<\/p>\n<p>    select * from #tblTemp inner join tblOther on id = otherId<\/p>\n<p>End<br \/>\n[\/sql]<\/p>\n<p>Assuming this has over 4000 characters it will truncate at 4000 characters, your SQL will be malformed as it is missing the closing quote &#8216; and will crash.<\/p>\n<h2>The Resolution<\/h2>\n<p>So what is going on!<\/p>\n<p>I&#8217;ve read everywhere on the internet about nvarchar(max) having a 2GB limit etc, but it is clearly truncating at 4000 characters.<\/p>\n<p>Add in a<br \/>\n[sql]<br \/>\nselect Len(@sql)<br \/>\n[\/sql]<br \/>\nto confirm that it is definitely a 4000 character limit.<\/p>\n<p>So what is going on?<\/p>\n<h3>Implicit Type Conversion!!!<\/h3>\n<p>The innocent looking code adding a date to the query,<br \/>\n[sql]<br \/>\n &#8221;&#8221;&#8217; + Convert(varchar, @start, 106) + &#8221;&#8221;&#8217;<br \/>\n[\/sql]<\/p>\n<p>is causing SQL Server to perform an implicit type conversion and our NVarchar(MAX) is somehow becoming an NVarchar(4000).<\/p>\n<p>Change the addition of the date to the following<br \/>\n[sql]<br \/>\n&#8221;&#8221;&#8217; + Convert(nvarchar(max), @start, 106) + &#8221;&#8221;&#8217;<br \/>\n[\/sql]<\/p>\n<p>The problem is averted and the select Len(@sql) will happily count beyond 4000.<\/p>\n<p>(BTW convert(varchar, @date, 106), creates a &#8216;1-Jan-2000&#8217; formatted date which resolves any issues that can occur with different regional settings on your sql server causing problems)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Problem Sometimes you may need to write dynamic SQL. One example is writing query for running over linked server connections. You might write something [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13,28],"tags":[],"class_list":["post-1100","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=1100"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1100\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=1100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=1100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=1100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}