I have a query that I'm trying to run through OPENQUERY on SSRS/SQL Server 2014, but I keep getting an error of:
The character string that starts with [...] is too long. Maximum length is 8000.
Is there any way to work around this limitation?
For reference, I'm trying to run a query from SSRS through a linked MySQL Server.
You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows:
DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine that's longer than 8000 characters
EXECUTE (@myStatement) AT LinkedServerName
In order to make sure this doesn't throw an error, you need to enable the RPC OUT option on the linked server, by issuing the following command:
EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'rpc out', @optvalue=N'true'
Or enabling it within the GUI:
