12

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.

Improve this question
0

1 Answer 1

Reset to default
20

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

Improve this answer
2
  • 1
    After making this change, I also had to set "Enable Promotion of Distributed Transaction" to false.
    – Mark Barnes
    Commented Jun 30, 2021 at 14:32
  • 1
    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)".
    – John
    Commented Jul 26, 2023 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.