CODEWITHSUNDEEP
sql-serverstored-proceduresbiztalk
xyz
xyz

Reputation: 549

Merge to Insert or update records in SQL Server database

I am having hard time with creating a stored procedure to insert/update the selected records from an Oracle database to a SQL Server database using BizTalk.

ALTER PROCEDURE [dbo].[uspInsertorUpdateINF]
    @dp_id                char(32),
    @dv_id                char(32),
    @em_number            char(12),
    @email                varchar(50),
    @emergency_relation   char(32),
    @option1              char(16),
    @status               char(20),
    @em_id                char(35),
    @em_title             varchar(64),
    @date_hired           datetime
AS
BEGIN
   SET NOCOUNT ON;

   MERGE [dbo].[em] AS [Targ]
   USING (VALUES (@dp_id, @dv_id , @em_number, @email, @emergency_relation, @option1, @status, @em_id, @em_title, @date_hired))
   AS [Sourc] (dp_id, dv_id, em_number, email, emergency_relation, option1, status, em_id, em_title, date_hired)  
      ON [Targ].em_id = [Sourc].em_id

   WHEN MATCHED THEN
      UPDATE 
         SET dp_id                 = [Sourc].dp_id,
             dv_id                 = [Sourc].dv_id,
             em_number             = [Sourc].em_number,
             email                 = [Sourc].email,
             emergency_relation    = [Sourc].emergency_relation,
             option1               = [Sourc].option1,
             status                = [Sourc].status,
             em_title              = [Sourc].em_title,
             date_hired            = [Sourc].date_hired

   WHEN NOT MATCHED BY TARGET THEN
      INSERT (dp_id, dv_id, em_number, email, emergency_relation, option1, status, em_id, em_title,date_hired)
      VALUES ([Sourc].dp_id, [Sourc].dv_id, [Sourc].em_number, [Sourc].email, [Sourc].emergency_relation, [Sourc].option1, [Sourc].status, [Sourc].em_id, [Sourc].em_title, [Sourc].date_hired);

 END;

I am getting an error:

WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://abc//def?". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'uspInsertorUpdateINF' expects parameter '@dp_id', which was not supplied

I am mapping the values from the Oracle DB to SQL Server stored procedures using BizTalk maps

enter image description here

I am not sure why I get this error.

Upvotes: 0

Views: 974

Answers (1)

Dan Field
Dan Field

Reputation: 21661

The procedure isn't getting the @dp_id parameter. The most likely cause of this is that the HOME_AU in the source message is empty/nil. You can check this several ways:

  1. The Group Hub tab, query Suspended Instances, check the suspended instance's message, look for the value of the dp_id node
  2. Turn on message tracking for the port(s)/orchestration(s) that process this message, and look in Tracked Message Events; this would show you successful messages (if there are any) as well, and may give you the source message.
  3. Use SQL Server Profiler to check the actual procedure call, and check the value of the parameter that's getting passed as @dp_id. This could be used to diagnose a problem if you're not seeing anything unusual about HOME_AU/dp_id in in the XML.

Your most likely cause of this is the source data having a null or empty value for whatever is producing HOME_AU. If that's the case, you can specify @dp_id char(30) = NULL, or whatever default value you want it to have in your stored procedure definition.

Also, FYI - you will generally get better performance (and greater flexibility) using Table Valued Parameters in your stored procedures from BizTalk. Especially since, in this case, you're trying to build a table out of the parameters (using a VALUES clause) in your procedure anyway.

Upvotes: 2

Related Questions