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 like this

[sql]
Create Procedure prcTest
@start datetime
as
Begin
Declare @sql nvarchar(max)

set @sql = ‘Insert Into #tblTemp
select * from OpenQuery(LINKSERVER, ”Select * from dbo.tblRemote r
inner join tblRemote2 r2 on r.Id = r2.fkId
…. lots more sql x 4000+ characters
Where r.DateCreated > ””’ + Convert(varchar, @start, 106) + ””’
”)’;

exec sp_executesql @sql

select * from #tblTemp inner join tblOther on id = otherId

End
[/sql]

Assuming this has over 4000 characters it will truncate at 4000 characters, your SQL will be malformed as it is missing the closing quote ‘ and will crash.

The Resolution

So what is going on!

I’ve read everywhere on the internet about nvarchar(max) having a 2GB limit etc, but it is clearly truncating at 4000 characters.

Add in a
[sql]
select Len(@sql)
[/sql]
to confirm that it is definitely a 4000 character limit.

So what is going on?

Implicit Type Conversion!!!

The innocent looking code adding a date to the query,
[sql]
””’ + Convert(varchar, @start, 106) + ””’
[/sql]

is causing SQL Server to perform an implicit type conversion and our NVarchar(MAX) is somehow becoming an NVarchar(4000).

Change the addition of the date to the following
[sql]
””’ + Convert(nvarchar(max), @start, 106) + ””’
[/sql]

The problem is averted and the select Len(@sql) will happily count beyond 4000.

(BTW convert(varchar, @date, 106), creates a ‘1-Jan-2000’ formatted date which resolves any issues that can occur with different regional settings on your sql server causing problems)