8000 character limit on OPENQUERY against a linked server

Question

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.

Answer 1

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:

enter image description here

• Mark Barnes: After making this change, I also had to set "Enable Promotion of Distributed Transaction" to false.
• John: This results in running out of memory for me. Msg 7399, Level 16, State 1, Line 25 The OLE DB provider "STREAM" for linked server "(null)" reported an error. The provider ran out of memory. Msg 7330, Level 16, State 2, Line 25 Cannot fetch a row from OLE DB provider "STREAM" for linked server "(null)".