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.

Paul White
  • 67,511
  • 25
  • 368
  • 572
Salmononius2
  • 441
  • 2
  • 5
  • 14

1 Answers1

18

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

Paul White
  • 67,511
  • 25
  • 368
  • 572
John Eisbrener
  • 8,970
  • 5
  • 23
  • 55
  • After making this change, I also had to [set "Enable Promotion of Distributed Transaction" to false](https://stackoverflow.com/a/50894789/1681788). – Mark Barnes Jun 30 '21 at 14:32