I have also found that I receive this error when SQL Server (and presumably any other ODBC provider) returns an overly-long error message. In my own testing I found that the error is generated when an ODBC error message is more than 511 characters long.
I have not found any documentation listing 511 characters as the max length for these messages but it does correspond to a round binary number (29 = 512), so it makes sense as a limit in that regard.
Stock error messages are unlikely to be that long, but if you are returning a custom error message it is entirely possible. The workaround I went with was to catch the error and trim it before rethrowing it.
There is some default text that is always returned (
[Microsoft][ODBC SQL Server Driver][SQL Server]) at the start of the message. That string may be slightly different depending on how the table has been linked, so you may need to adjust the trim length to suit.
511 - Len("[Microsoft][ODBC SQL Server Driver][SQL Server]") = 464
So we need to trim the original error message to 464 characters. I do this as follows:
-- The T-SQL to execute goes here
IF @@TRANCOUNT > 0
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = LEFT(ERROR_MESSAGE(),464), -- adjust 464 based on above formula
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)