Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

nvarchar(max) - 4000 limit overcome

Hi 

 

I have to execute @sql with sp_executesql. This is defined as NVARCHAR(MAX). However the nvarchar is limited to 4000 characters and the query is getting truncated. I cannot split the query as it is automatically generated. How can I run queries which are more than 4000 characters and need to be in a variable.

 

DECLARE @sql as NVARCHAR(MAX);

EXEC sp_executesql @sql

2 ACCEPTED SOLUTIONS

Hi @Anonymous,

Thanks for your update.

What you're experiencing is expected behaviour in SQL Server:

  • PRINT @sql truncates output at 4000 characters for NVARCHAR(MAX).
  • SELECT @sql displays the full query, but since it's a result set, it affects the flow of the stored procedure.

Here are the few steps you can consider:

  • Use RAISEERROR to print the full query in chunks this breaks the query into 4000-character chunks, ensuring it prints fully.
  • If you need to debug long queries, storing them in a table can be helpful.
  • Use SELECT @sql only for debugging and remove it before final execution. If SELECT @sql stops the stored procedure, try running it separately to inspect the output.

If you find this information useful, please accept it as a solution and give it a 'Kudos' to assist others in locating it easily.
Thank you.

View solution in original post

Andreas_Sorgatz
New Member

Try:

 

DECLARE @sql as NVARCHAR(MAX);

SET @sql = CONCAT (CAST('' AS NVARCHAR(MAX)), 'SELECT    ...    ');

EXEC sp_executesql @sql

 

=>

 

Microsoft documents this behavior with regard to the use of nvarchar(max) with sp_executesql. The official documentation notes that sp_executesql accepts a parameter of type nvarchar(max), which theoretically allows processing of strings up to 2^31-1 characters. However, there are practical limitations, especially when strings are passed directly as literals or concatenated.

A common problem arises when a long SQL string is passed directly as a literal to sp_executesql. In such cases, SQL Server treats the literal as nvarchar(4000) by default, which results in truncation of the string if it exceeds 4000 characters. This behavior is described in various community discussions and technical articles.

To work around this problem, it is recommended to store the SQL string in a variable of type nvarchar(max) and pass this variable to sp_executesql. This ensures that the entire string is processed without truncation. This practice is recommended in Microsoft documentation and various technical articles.

In summary, correct handling of nvarchar(max) in conjunction with sp_executesql is critical to avoiding string truncation issues. It is important to be aware of the default limits and take appropriate measures to ensure that long SQL statements are processed completely.

View solution in original post

9 REPLIES 9
Andreas_Sorgatz
New Member

Try:

 

DECLARE @sql as NVARCHAR(MAX);

SET @sql = CONCAT (CAST('' AS NVARCHAR(MAX)), 'SELECT    ...    ');

EXEC sp_executesql @sql

 

=>

 

Microsoft documents this behavior with regard to the use of nvarchar(max) with sp_executesql. The official documentation notes that sp_executesql accepts a parameter of type nvarchar(max), which theoretically allows processing of strings up to 2^31-1 characters. However, there are practical limitations, especially when strings are passed directly as literals or concatenated.

A common problem arises when a long SQL string is passed directly as a literal to sp_executesql. In such cases, SQL Server treats the literal as nvarchar(4000) by default, which results in truncation of the string if it exceeds 4000 characters. This behavior is described in various community discussions and technical articles.

To work around this problem, it is recommended to store the SQL string in a variable of type nvarchar(max) and pass this variable to sp_executesql. This ensures that the entire string is processed without truncation. This practice is recommended in Microsoft documentation and various technical articles.

In summary, correct handling of nvarchar(max) in conjunction with sp_executesql is critical to avoiding string truncation issues. It is important to be aware of the default limits and take appropriate measures to ensure that long SQL statements are processed completely.

v-ssriganesh
Community Support
Community Support

Hi @Anonymous,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

v-ssriganesh
Community Support
Community Support

Hi @Anonymous ,

Could you kindly assist me with the details I mentioned in my previous response? Additionally, I would appreciate it if you could provide a description.

Thank you.

v-ssriganesh
Community Support
Community Support

Hi @Anonymous,

Thanks for reaching out to the Microsoft fabric community forum.

We understand that your query is automatically generated, and you’re facing an issue where NVARCHAR(MAX) seems to be limited to 4000 characters, causing truncation when executing with sp_executesql.

To better assist you, could you clarify the following?

Where are you executing the query?

  • Fabric Data Warehouse, Lakehouse, Notebooks or pipelines

How is your SQL query being generated and assigned to @sql?

  • Is it coming from a Fabric Notebook, Power BI, or another process?
  • Are you fetching it from a table, a stored procedure, or an external source?

Are you seeing the truncation before or after executing sp_executesql?

Once we have these details, we’ll be able to provide a better solution.
Thank you.

Anonymous
Not applicable

Hi,

 

a) We are executing the query from Stored Procedures in Warehouse.

b) Two ways we are creating SQL.

i) SET @sql=' sql statements ' + @tablename +'where ' +@condition

ii) SET @sql = 'select column from information_server where tablename='+@tablename;

Both cases when I print(@sql), the query is getting truncated.

 

Hi @Anonymous,

Thanks for providing the details. Since you are executing the query from Stored Procedures in a Fabric Data Warehouse and the truncation happens before execution, the issue is likely due to implicit conversion to NVARCHAR(4000) when assigning the query.

  • Ensure @sql is explicitly declared as NVARCHAR(MAX) before assigning values.
  • Use CAST() or CONVERT() to force NVARCHAR(MAX) assignment this ensures that SQL Server does not truncate the string to NVARCHAR(4000).
  • If using multiple concatenations, use += to append dynamically this prevents intermediate truncation.

If this information is helpful, please Accept it as a solution and give a "kudos" to assist other community members in resolving similar issues more efficiently.

Thank you.

Anonymous
Not applicable

When we print the result (PRINT @sql) it still truncates the sql. If we write the statement select @sql, it displays the entire sql, but then the stored proc stops at that point and doesn't move ahead.

Hi @Anonymous,

Thanks for your update.

What you're experiencing is expected behaviour in SQL Server:

  • PRINT @sql truncates output at 4000 characters for NVARCHAR(MAX).
  • SELECT @sql displays the full query, but since it's a result set, it affects the flow of the stored procedure.

Here are the few steps you can consider:

  • Use RAISEERROR to print the full query in chunks this breaks the query into 4000-character chunks, ensuring it prints fully.
  • If you need to debug long queries, storing them in a table can be helpful.
  • Use SELECT @sql only for debugging and remove it before final execution. If SELECT @sql stops the stored procedure, try running it separately to inspect the output.

If you find this information useful, please accept it as a solution and give it a 'Kudos' to assist others in locating it easily.
Thank you.

Hi @Anonymous,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

Helpful resources

Announcements
Fabric July 2025 Monthly Update Carousel

Fabric Monthly Update - July 2025

Check out the July 2025 Fabric update to learn about new features.

July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.