Dynamic SQL query string truncated to 256 characters

Question

I am trying to run dynamic SQL in SQL Server 2016, like this:

 declare @SQL varchar(MAX);

 set @SQL='SELECT top 1 * INTO Table 1 FROM 
 OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', etc... (string aprox. 450 char)

 EXECUTE sp_executesql @SQL;

For some reason, the variable @SQL is truncated to 256 characters. I already followed instructions in the article below and modify some SQL Server query settings but no result. See:

https://www.mssqltips.com/sqlservertip/2795/prevent-truncation-of-dynamically-generated-results-in-sql-server-management-studio/

Can you please advise, not sure what I am missing, it didn't happen in SQL Server 2008 that I used before. is there any additional setting in SQL Server 2016?

Answer 1

The problem is the sp_executesql uses nvarchar, so you should declare @sql as such, and also SET @sql = N'....:

 DECLARE @SQL nvarchar(MAX);

 SET @SQL = N'SELECT top 1 * INTO Table 1 FROM 
 OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', etc... '

 EXECUTE sp_executesql @SQL;