CODEWITHSUNDEEP
biztalkbiztalk-2016
Jason Hoyle
Jason Hoyle

Reputation: 11

BizTalk WCF-CUSTOM Sqlbinding generates a type conversion error from string to byte[]

I am migrating a BizTalk 2010 solution to BizTalk 2016. Everything has been largely copied & pasted so no changes there (and the 2010 version works) but when I send a message to a WCF-CUSTOM port - sqlbinding to stored procedures - it stops and logs a type conversion error from string to byte[].

I have tried putting the same message through the 2010 deployment and that works fine but not the 2016. I have tried creating another simple orchestration with the schema generated from the stored procedure and it also produces the error (see below).

The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://*****". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.InvalidCastException: Failed to convert parameter value from a String to a Byte[]. ---> System.InvalidCastException: Invalid cast from 'System.String' to 'System.Byte[]'. at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace ---

Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

The message it should be sending is

store proc message

Here is the stored proc

    CREATE PROCEDURE [dbo].[***]
    (
        @BatchId BIGINT OUTPUT,
        @BatchGuid VARCHAR(50) = NULL,
        @Contract VARCHAR(50) = NULL,
        @Initiating***Id BIGINT = NULL,
        @LinesExpected INT = NULL,
        @LinesProcessed INT = NULL,
        @SourceSystemMessageId VARCHAR(50) = NULL,
        @SourceSystemName VARCHAR(50) = NULL,
        @CreatedBy NVARCHAR(255) = NULL,
        @RowId  TIMESTAMP  = 0x00000000000007D9 OUTPUT 
    )
    AS

        SET NOCOUNT ON


        if @BatchGuid  is NULL
        set @BatchGuid = ''
        if @Contract  is NULL
        set @Contract  = ''
        if @Initiating***Id is NULL
        set @Initiating***Id = 0
        if @LinesExpected  is NULL
        set @LinesExpected  = 0
        if @LinesProcessed is NULL
        set @LinesProcessed = 0
        if @SourceSystemMessageId  is  NULL
        set @SourceSystemMessageId  = ''
        if @SourceSystemName is NULL
        set  @SourceSystemName  = ''
        if @CreatedBy is NULL
        set @CreatedBy  = ''



        INSERT INTO dbo.[***]
        (
            [BatchGuid],
            [Contract],
            [Initiating***Id],
            [LinesExpected],
            [LinesProcessed],
            [SourceSystemMessageId],
            [SourceSystemName],
            [CreatedBy],
            [CreatedDate]
        )
        VALUES
        (
            @BatchGuid,
            @Contract,
            @Initiating***Id,
            @LinesExpected,
            @LinesProcessed,
            @SourceSystemMessageId,
            @SourceSystemName,
            @CreatedBy,
            getdate()

        )

        SELECT @BatchId = Scope_Identity()

        SELECT @RowId = RowId FROM [***]
        WHERE  [BatchId] = @BatchId

        SET NOCOUNT OFF
        RETURN

GO

Please note the *** are just covering up sensitive information

Upvotes: 0

Views: 390

Answers (1)

Jason Hoyle
Jason Hoyle

Reputation: 11

It turns out the issue was due to the null value in rowId parameter. It would seem that BizTalk 10 handled null values differently.

Upvotes: 1

Related Questions