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
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
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.
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
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,
''''' + Convert(varchar, @start, 106) + '''''
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
''''' + Convert(nvarchar(max), @start, 106) + '''''
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)