none
Selecting, Updating sql query in BizTalk Orchestration ? RRS feed

  • Question

  • Hi - I have to first insert the data into SQL db, I have done that through Orch1 (where Status ='RECEIVED')

    Now is Orch2, I have to update the Status ='InTransit' and select all the rows depending on this status process it and send data out to a folder, then finally update the Status = 'Sent'.

    I have written query for Select as below. How can I write update query ?

    strAssgn = "<ns0:Select xmlns:ns0=\"http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment\">"+
    "<ns0:Columns>*</ns0:Columns><ns0:Query> where Status = 'RECEIVED' </ns0:Query></ns0:Select>"; 
    
    xmlDoc.LoadXml(strAssgn);



    MBH

    Thursday, January 7, 2016 3:50 PM

Answers

  • GetPaid_IntraPayment : this is the name of the table

    spUpdate_Intransit_New : this is name of sproc


    MBH

    Try below script, I have tested and this will work surly,

    --Step 1
    Create Table GetPaid_IntraPayments
    (
    	[custNo] [Int] NULL,
    	[invNo] [NVARCHAR](200) NULL,
    	[amount] [decimal](11, 0) NULL,
    	[depositID] [Int] NULL,
    	[depositDate] [DATETIME] NULL,
    	[Status] [NVARCHAR](200) NULL,
    	[SentTime] [DATETIME] NULL,
    	[ReceivedTime] [DATETIME] NULL
    )
    GO
    --Step 2
    Insert into GetPaid_IntraPayments
    Values (1,'Test','101.1',101,GETDATE(),'RECEIVED',GETDATE(),GETDATE())
    
    --Step 3
    Select * from GetPaid_IntraPayments
    GO
    
    --Step 4
    CREATE PROCEDURE [dbo].[spUpdate_Intransit] 
    (    
     @Status VARCHAR(50)
    )      
    AS      
    BEGIN   
    	SET NOCOUNT ON;  
    	UPDATE [GetPaid_IntraPayments] SET [Status] = 'INTRANSIT' WHERE Status =@Status
    END
    
    --Step 5
    EXEC [spUpdate_Intransit] 'RECEIVED'
    

    And I would suggest please have a look into my previous post.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by JaguarsJag Monday, January 11, 2016 7:36 PM
    Monday, January 11, 2016 7:11 PM
    Moderator

All replies

  • Can you use Stored Procedures?  SP's are nearly always better.
    Thursday, January 7, 2016 4:28 PM
    Moderator
  • Hi John, I am looking into this below link and trying to do the same for Update, Insert, delete, select (taking ther instance and writing in query, scroll to bottom of the page in the link)

    Write the query for each in Expression shape. Can anyone suggest is this good approach and will it work ?

    How can Stored Proc be effective ?


    MBH

    Thursday, January 7, 2016 4:42 PM
  • How to update this query ? 

    strAssgn = "<ns0:Update xmlns:ns0=\"http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment\">
    <ns0:Rows>
    <ns0:RowPair xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment">
      <ns0:After>
        <ns1:custNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">21</ns1:amount>
        <ns1:depositID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">60</ns1:amount>
        <ns1:depositID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows></ns0:Update>";

    I am receiving following error :


    MBH

    Thursday, January 7, 2016 5:12 PM
  • Hi MBH,

    Thank you for posting in MSDN forum.

    As per Johns suggestion, why you are not using SP to achieve your scenario, using SP is more easy and better for this type of scenario.

    strAssgn = "<ns0:Update xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment">
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:custNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">21</ns1:amount>
        <ns1:depositID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">60</ns1:amount>
        <ns1:depositID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>"

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, January 7, 2016 5:29 PM
    Moderator
  • Kamlesh, please double check before posting. That query provided has same errors.

    MBH

    Thursday, January 7, 2016 5:42 PM
  • What error are you getting, Have you checked below article,

    http://btsguru.blogspot.in/2011/10/wcf-sql-adapter-table-operations.html


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, January 7, 2016 5:51 PM
    Moderator
  • This should work

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>21</ns1:amount>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>60</ns1:amount>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>StatusStatusStatusStatusStatusStatusStatusStatusSt</ns1:Status>
        <ns1:SentTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");


    Please mark as answer or vote as helpful if my reply does

    Thursday, January 7, 2016 6:07 PM
    Moderator
  • Thanks Rachit....here the Update condition is, all the sql fields in the table should remain same like when Inserted, but only the Status field should be changing like Status = RECEIVED to Status= INTRANSIT. What do I need to do with custNo, invNo,amount, depositID....fields ? can I leave them defaulted or empty ?
    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>21</ns1:amount>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
        <ns1:SentTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>60</ns1:amount>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>RECEIVED</ns1:Status>
        <ns1:SentTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");


    MBH

    Thursday, January 7, 2016 6:13 PM
  • Because doing SQL operations in T-SQL in Stored Procedures is much easier then trying to use updategrams.

    Please trust us on this.

    You're asking for a good approach that will work, Stored Procedures.

    Thursday, January 7, 2016 6:16 PM
    Moderator
  • For the customer number if you just need to update the status then do this way:

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
       </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
       </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");

    So this will be like

    Update TableName
    
    Set Status=‘INTRANSIT’
    
    Where custNo= 1


    Please mark as answer or vote as helpful if my reply does

    Thursday, January 7, 2016 6:47 PM
    Moderator
  • All sql fields like custNo, invNo,invDate,custID all are random fields with data coming based from the client....so there is no fixed field to set where condition to set Status....

    Only thing that should change here is Status = Insert, To....Status= INTRANSIT.....others all should remain as it is

    the table looks like below :


    MBH

    Thursday, January 7, 2016 6:58 PM
  • Do you think below query will work ?

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
       </ns0:After>
      <ns0:Before>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>RECEIVED</ns1:Status>
       </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");


    MBH

    Thursday, January 7, 2016 7:02 PM
  • That updategram will update ALL records to Status=Received.

    You have to have some key to use as a filter.

    Why can you use a Stored Procedure?

    Thursday, January 7, 2016 7:09 PM
    Moderator
  • Do you think below query will work ?

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
       </ns0:After>
      <ns0:Before>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>RECEIVED</ns1:Status>
       </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");


    MBH

    NO, this will not work!

    You have to decide what your where clause will be, based on that you can select the fields to be mapped in Before section.

    Refer the articles:

    http://southworks.com/blog/2005/10/21/biztalk-server-sql-adapter-understanding-updategrams/

    http://romikoderbynew.com/2010/07/06/biztalk-2006-default-sql-adapter-updategrams-part-1/

    Try:

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
        </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");



    Please mark as answer or vote as helpful if my reply does

    Thursday, January 7, 2016 7:10 PM
    Moderator
  • Hello Rachit, I am getting error in the shape where I wrote below query : Inner exception: Illegal characters in path

    xmlDoc.Load(@"<ns0:Update xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/GetPaid_IntraPayment'>
    <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>INTRANSIT</ns1:Status>
        
      </ns0:After>
      <ns0:Before>
        <ns1:custNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>custNocustNocustNocustNocustNocustNocustNocustNocu</ns1:custNo>
        <ns1:invNo xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>invNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNoinvNo</ns1:invNo>
        <ns1:amount xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>60</ns1:amount>
        <ns1:depositID xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositIDdepositIDdepositIDdepositIDdepositIDdepos</ns1:depositID>
        <ns1:depositDate xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>depositDatedepositDatedepositDatedepositDatedeposi</ns1:depositDate>
        <ns1:Status xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>Insert</ns1:Status>
        <ns1:SentTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:SentTime>
        <ns1:ReceivedTime xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>1999-05-31T13:20:00.000-05:00</ns1:ReceivedTime>
      </ns0:Before>
    </ns0:RowPair>
    </ns0:Rows>
    </ns0:Update>");


    MBH

    Thursday, January 7, 2016 8:17 PM
  • Rachit I used above query exactly in my Expression shape in Orch Shape :Status_InTransit.     Below is pic of the Error description


    MBH

    Thursday, January 7, 2016 10:55 PM
  • You can see now why Table Operations are actually harder to do than Stored Procedures.

    Such an operation is pretty trivial when using an SP.

    Friday, January 8, 2016 2:48 PM
    Moderator
  • so Iam trying using stored procedure.... when I execute the proc, it shows command successful but when I refresh the table, nothing is getting updated ? 

    ALTER PROCEDURE [dbo].[spUpdate_Intransit]
    
    @custNo nvarchar(50),
    @invNo nvarchar(50),
    @amount decimal(18, 0),
    @depositID nvarchar(50),
    @depositDate nvarchar(50),
    @Status nvarchar(50),
    @SentTime datetime,
    @ReceivedTime datetime
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        UPDATE [IMS].[dbo].[GetPaid_IntraPayment]
    
    SET @Status = 'INTRANSIT' WHERE @custNo ='Jonny'
    END


    MBH

    Friday, January 8, 2016 3:53 PM
  • SET @Status = 'INTRANSIT' WHERE @custNo ='Jonny'

    This statement is updating the parameter variables, not any table columns.

    There has been no change in the table to show.

    Friday, January 8, 2016 4:14 PM
    Moderator
  • so how to update my sp to update the coloumns ?

    MBH

    Friday, January 8, 2016 4:15 PM
  • By updating the table columns.  Sorry, I don't get the question.  It's pretty basic SQL.
    Friday, January 8, 2016 5:01 PM
    Moderator
  • so how to update my sp to update the coloumns ?

    MBH

    Where and how you are using this SP, and as per your sql script it will update only parameter variable, so try something like below,

    UPDATE [IMS].[dbo].[GetPaid_IntraPayment] SET [ColumnName] = 'INTRANSIT' WHERE [ColumnName1]= @custNo

    *[ColumnName] should be your required column that you need to update,

    But, before changing anything please explain how are you using SP? 

    And Please first try to execute SP in SQL server with some test records and see the result.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Friday, January 8, 2016 5:34 PM
    Moderator
  • below is the table which is not getting updated after writing the sp as below


    ALTER PROCEDURE [dbo].[spUpdate_Intransit] @custNo nvarchar(50), @invNo nvarchar(50), @amount decimal(18, 0), @depositID nvarchar(50), @depositDate nvarchar(50), @Status nvarchar(50), @SentTime datetime, @ReceivedTime datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. --SET NOCOUNT ON; UPDATE [IMS].[dbo].[GetPaid_IntraPayment] SET Status = 'INTRANSIT' WHERE @Status ='RECEIVED' END



    MBH

    Friday, January 8, 2016 5:47 PM
  • NO, Why @Status='RECEIVED' ??

    Simple SP like below, Try first in SQL,

    CREATE procedure spUpdate_Intransit  
    @Status nvarchar(50)
    
    AS
    BEGIN
    	UPDATE [GetPaid_IntraPayment] SET Status = 'INTRANSIT' WHERE Status =@Status
    	
    END
    EXEC spUpdate_Intransit 'RECEIVED'

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, January 8, 2016 5:58 PM
    Moderator
  • @jaguarjags, please stop and take some time to think through these issues yourself.

    Asking seemingly random questions is confusing for the thread and is not helping us giver you the best advice.

    For example, we cannot help you write the update since we don't know anything about the incoming data, the target table or the exact trigger for an INTRANSIT record.  We're just guessing.

    This is very basic SQL and you absolutely can write it yourself, please just try.  It's not that hard and you'll need to know how to do this on your own.


    Friday, January 8, 2016 6:32 PM
    Moderator
  • Hey MBH,

    I have gone through the entire thread and I see all the comments from others. Could you please provide your requirement in simple terms. For one moment, leave out what you have done and all. Just tell us what your requirement is.

    For now, I am putting my understanding below.

    1. Get records and stack in table with status = "Received"
    2. Pick up those records and mark them as "In Transit"
    3. Send the records out and mark them as "Sent"

    Please note that there will be variations to the way in which you implement depending on the requirement.

    For eg: If you say, I want to mark the records as "Sent", pick up records and try sending them. If that fails, I will come back and revert them as "Received".

    In either case, I see the complications of using update datagrams. 


    Praveen Behara
    MCST : BizTalk Server 2006 R2, 2010

    Monday, January 11, 2016 6:09 AM
  • I am using in my Orchestration to set this query dynamically in expression shape, am I doing anything wrong calling this way and updating ? Any suggestions please ?

    tempXML.LoadXml("<ns0:ExecuteNonQuery xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/'><ns0:Query> update dbo.tbl_IntraPaymnt set Status = 'INTRANSIT' where Status = 'RECEIVED'</ns0:Query></ns0:ExecuteNonQuery>"); 
    


    MBH

    Monday, January 11, 2016 2:47 PM
  • HOLD ON!

    Why are you doing that?  We've already covered that a Stored Procedure is the best way to interact with SQL Server and it seemed you at least had the close to complete.

    Executing direct SQL commands is always a last resort option.  Basically, don't do that.

    Let us help you achieve the most correct solution, constantly changing directions is costing everyone valuable time.

    Monday, January 11, 2016 3:01 PM
    Moderator
  • here the team who have worked in the past have worked on executing queries dynamically using expression shape in Orchestration.So, they want me to follow that approach.

    Why does executing directly query from expression shape has any negative impacts ?


    MBH

    Monday, January 11, 2016 3:13 PM
  • On the spectrum of Correct to, well, not Correct, this ranks just above using ADO.Net in an Expression Shape.

    In a BizTalk app, the correct pattern for interacting with SQL Server is through the WCF-SQL Adapter.  In SQL Server apps (which is really what the database is) the most correct pattern for operations is Stored Procedures/Functions.

    So, WCF SQL Adapter + Stored Procedure = most correct solution.

    Think of it this way, executing SQL Commands directly is like using Reflection to execute arbitrary C# code.  Would you ever consider actually doing that?  Probably not.

    Will that work, sure, but should get a big red Must Refactor stamp in code review :).

    Monday, January 11, 2016 3:57 PM
    Moderator
  • Kamlesh, I tried the above query as suggested but there is nothing changing in the table record ? why ?

    Iam using this procedure, to update first in BizTalk

    ALTER PROCEDURE [dbo].[spUpdate_Intransit]
    
    @custNo nvarchar(50),
    @invNo nvarchar(50),
    @amount decimal(18, 0),
    @depositID nvarchar(50),
    @depositDate nvarchar(50),
    @Status nvarchar(50),
    @SentTime datetime,
    @ReceivedTime datetime
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        UPDATE [IMS].[dbo].[GetPaid_IntraPayment] SET Status = 'INTRANSIT' WHERE Status=@Status
    END


    MBH

    Monday, January 11, 2016 4:05 PM
  • How you are trying to execute this.. Please provide me all steps.

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 6:11 PM
    Moderator
  • I have written the above query (stored procedure) ,  in the drop down on top choose database name and selecting all hitting execute button. It shows command(s) completed successfully but when I refresh table no change in the coulumn happens. What is the reason ? 

    MBH

    Monday, January 11, 2016 6:15 PM
  • NO, this is not a process. Seems you are not more familiar/comfortable with SQL.

    If Yes, Then I would suggest please sit with some db guys Or read some SQL online forum.

    Let me explain you the process in Steps,

    Step1: Create simple SP and execute it on proper database. Use below script to create SP, and Press F5

    CREATE procedure spUpdate_Intransit  
    @Status nvarchar(50)
    
    AS
    BEGIN
    	UPDATE [GetPaid_IntraPayment] SET Status = 'INTRANSIT' WHERE Status =@Status
    END

    Once SP create and executed you need to pass the parameter, use below script,

    EXEC spUpdate_Intransit 'RECEIVED'

    Once command successfully executed please check in your table, and column should be updated, Use below script,

    Select * from spUpdate_Intransit

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 6:23 PM
    Moderator
  • when I hit this below command receiving error :

    EXEC spUpdate_Intransit 'RECEIVED'

    Msg 201, Level 16, State 4, Procedure spUpdate_Intransit, Line 0
    Procedure or function 'spUpdate_Intransit' expects parameter '@invNo', which was not supplied.


    MBH

    Monday, January 11, 2016 6:28 PM
  • when I hit this below command receiving error :

    EXEC spUpdate_Intransit 'RECEIVED'

    Msg 201, Level 16, State 4, Procedure spUpdate_Intransit, Line 0
    Procedure or function 'spUpdate_Intransit' expects parameter '@invNo', which was not supplied.


    MBH

    Ok, So seems you have already created this SP 'spUpdate_Intransit' before. 

    Then Created with new SP with new name or Alter this SP.

    Step1:

    CREATE procedure spUpdate_Intransit_New
    @Status nvarchar(50)
    
    AS
    BEGIN
    	UPDATE [GetPaid_IntraPayment] SET Status = 'INTRANSIT' WHERE Status =@Status
    END

    Step2:

    EXEC spUpdate_Intransit_New 'RECEIVED'

    Step3:

    Select * from spUpdate_Intransit


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 6:31 PM
    Moderator
  • @jaguarjags If you want to learn SQL I would suggest to start with basic and there are a lot of online websites and articles are available.

    Please look into below a very nice and basic SQL tutorial,

    http://www.w3schools.com/sql/

    My personal suggestion, please read above tutorial and learn some basic thing of SQL.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 6:33 PM
    Moderator
  • I updated with new SP name as provided above and executed.

    when I execute step 2 is throwing error :

    Msg 201, Level 16, State 4, Procedure spUpdate_Intransit_New, Line 0
    Procedure or function 'spUpdate_Intransit_New' expects parameter '@invNo', which was not supplied.


    MBH

    Monday, January 11, 2016 6:51 PM
  • I updated with new SP name as provided above and executed.

    when I execute step 2 is throwing error :

    Msg 201, Level 16, State 4, Procedure spUpdate_Intransit_New, Line 0
    Procedure or function 'spUpdate_Intransit_New' expects parameter '@invNo', which was not supplied.


    MBH

    Is GetPaid_IntraPayment a table or table type ??

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 6:56 PM
    Moderator
  • GetPaid_IntraPayment : this is the name of the table

    spUpdate_Intransit_New : this is name of sproc


    MBH

    Monday, January 11, 2016 7:02 PM
  • GetPaid_IntraPayment : this is the name of the table

    spUpdate_Intransit_New : this is name of sproc


    MBH

    Try below script, I have tested and this will work surly,

    --Step 1
    Create Table GetPaid_IntraPayments
    (
    	[custNo] [Int] NULL,
    	[invNo] [NVARCHAR](200) NULL,
    	[amount] [decimal](11, 0) NULL,
    	[depositID] [Int] NULL,
    	[depositDate] [DATETIME] NULL,
    	[Status] [NVARCHAR](200) NULL,
    	[SentTime] [DATETIME] NULL,
    	[ReceivedTime] [DATETIME] NULL
    )
    GO
    --Step 2
    Insert into GetPaid_IntraPayments
    Values (1,'Test','101.1',101,GETDATE(),'RECEIVED',GETDATE(),GETDATE())
    
    --Step 3
    Select * from GetPaid_IntraPayments
    GO
    
    --Step 4
    CREATE PROCEDURE [dbo].[spUpdate_Intransit] 
    (    
     @Status VARCHAR(50)
    )      
    AS      
    BEGIN   
    	SET NOCOUNT ON;  
    	UPDATE [GetPaid_IntraPayments] SET [Status] = 'INTRANSIT' WHERE Status =@Status
    END
    
    --Step 5
    EXEC [spUpdate_Intransit] 'RECEIVED'
    

    And I would suggest please have a look into my previous post.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by JaguarsJag Monday, January 11, 2016 7:36 PM
    Monday, January 11, 2016 7:11 PM
    Moderator
  • And Please close this thread and open new thread with fresh question, this thread is going out of subject...

    And first try to create some SP with parameter and try to execute it with some value. try it yourself only. If found any issue please post it in MSDN SQL forum(if not related to BizTalk).


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Monday, January 11, 2016 7:14 PM
    Moderator
  • hi MBH,

    This exactly happens when you keep following what others have done. There were many projects that we joined and were told - it always is done like in some way. That doesn't mean it is correct. Now, Johns has been trying to tell you a pattern that is a best practice across Microsoft BizTalk. I suggest you listen to him.

    Justification: It is not recommended (not be mention - an unnecessary tedious way). Had it been a best practice, folks would have jumped in to help rather than advise otherwise.

    If you still want to hit a screw into the wall with a pliers, let us know. 


    Praveen Behara
    MCST : BizTalk Server 2006 R2, 2010

    Monday, January 11, 2016 7:29 PM
  • HOLD ON!

    The marked answer is still not correct.  I don't believe the OP really understands the requirements or situation.

    That code takes many unused parameters and performs an unfiltered update.

    Monday, January 11, 2016 8:45 PM
    Moderator