0

I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql.

One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable!

I read online documentation and many of them suggests we can store upto 2 GB of data into NVARCHAR(MAX) variable but I don't know why it is not storing more than 4000 characters into the variable.

Any help will be appreciated.

EDIT - Added Sample Code

DECLARE @qry NVARCHAR(MAX);

SET @qry = N'SELECT * FROM Table1 t1' + N'INNER JOIN Table2 t2 ON t1.Col1 = t2.Col2' + N'INNER JOIN Table3 t3 ON t1.Col1 = t2.Col3'

EXEC sp_executeSQL @qry

Above is just a sample query with fewer characters, my actual query build-up is containing around 6000 characters.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Naim Halai
  • 355
  • 1
  • 8
  • 27
  • 2
    NVARCHAR(MAX) is not limited to 4000 characters, its how your using whatever is NVARCHAR(MAX) that is the problem, you need to show us that. – Alex K. Jun 13 '18 at 12:04
  • 3
    Show us your code. The likely cause is concatenation of unlike types resulting in a data type other than `nvarchar(MAX)`. – Dan Guzman Jun 13 '18 at 12:04
  • 1
    Are you printing out your query to view it? Is that where you are seeing the 4000 character limit of your query? – Sage Jun 13 '18 at 12:06
  • 1
    Have you tried using `EXEC (@qry)` instead of `sp_executesql`? – CynicalSection Jun 13 '18 at 12:26
  • @Sage I tried to print the variable after assigning it a string more than 4000 characters and it is showing only first 4000 characters. – Naim Halai Jun 13 '18 at 13:01
  • 1
    @CynicalSection I tried both but as query in `@qry` gets truncated after 4000 characters it doesn't work. – Naim Halai Jun 13 '18 at 13:01

0 Answers0