Menu
I have large queries
so i cant use linked server in production by rules.
i pass a varchar(max) which this has more than 8000 characters.
but sp_executesql does not support more than 8000 characters then how can i execute my string?
994
Error "Maximum length is 8000" occurs when querying an OpenEdge database via a Microsoft SQL Server Linked Server. SQL query being executed is longer than 8K. Using the Microsoft T-SQL OPENQUERY syntax to execute the SQL query. The character string that starts with '...' is too long.
Exec vs sp_executesql The main difference between the EXEC or EXECUTE operators and the sp_executesql built-in stored procedure is that the EXEC operator is used to execute a stored procedure or a SQL command passed as a string or stored within a variable.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
nvarchar(max) should work on SQL Server 2008 or later.
Does this work?:
declare @sql nvarchar(max)
set @sql = N'select' + CONVERT(NVARCHAR(MAX),REPLICATE(' ', 8000)) + ' ''Above 8000 character limit test'''
exec sp_executesql @sql
If you're using a version before that, you may need to split the query into multiple variables:
How to use SQL string variable larger than 4000 character in SQL server 2005 Stored Procedure?
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
