Post reply

How much data can you get in a VARBINAY (MAX)?

  • April 21, 2020 at 7:47 am

    #3744894

    BOL says it's "2 ^ 31 - 1" which is good old familiar 2,147,483,647. That looks pretty clear and unequivocable.

    So when we recently had this code

    Send
    On Conversation @ConversationHandle
    Message Type @MessageType (@Message)

    throw this error message

    Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes

    where @Message is a VARBINARY (MAX), we initially thought that something about "Message Type @MessageType (@Message)" might have in some way been adding something to @Message which just took it over the 2,147,483,647 limit.

    So we added some debugging error handling

    Begin Try
    Send
    On Conversation @ConversationHandle
    Message Type @MessageType (@Message)
    End Try
    Begin Catch
    Declare @ErrorMessage NVarchar (4000) = Error_Message ()
    Declare @DataLength BigInt = DataLength (@Message)
    Declare @Len BigInt = Len (@Message)
    Declare @FirstPart Varchar (500) = Cast (Cast (@Message As XML) As varchar (max))
    RaisError ('There was an error processing a message. The error was "%s". The DATALENGTH of the message was %I64d bytes. The LEN of the message was %I64d bytes. Its first 500 characters were: %s', 15, 1, @ErrorMessage, @DataLength, @Len, @FirstPart)
    End Catch

    That gave us the following

    Msg 50000, procedure Controcc_Charging_OvernightJob line 179: ''There was an error processing a message. The error was "Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.".

    The DATALENGTH of the message was 2193920948 bytes.

    The LEN of the message was 2193920948 bytes.

    Its first 500 characters were: <Root Table="T_Actual" User="OCCLAN\ContrOCCTests-User" Date="2020-04-20T08:52:03.747"><Inserted><row AbortedQuantity="0.00000000" ActualID="18093969" ActualStateID="2" CarePackageLineItemID="848630" CreatedBy="OCCLAN\ContrOCCTests-User" CreatedDate="2020-04-20T07:31:49.157" Deleted="0" EndDate="2020-04-18" ExtraQuantity="0.00000000" ...

    But 2193920948 is clearly more than the maximum permitted 2147483647!

    Does anybody know what's going on here? (We're going to make other changes to stop sending such ridiculously large amounts of data on SSB, so it's more a case of wanting to understand an error message that has confused us all.)

  • April 22, 2020 at 8:10 am

    #3745229

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply