Inserting into entities with FK Relationship & transactions

Oct 31, 2012 at 4:09 PM

Hi David

I am having issues while inserting members in entities with FK Relationship (Domain Based) using CRUD Web Services. Is there any way which takes care of that or do we have to handle it based on whihc entity goes forst and then which entity after that.

Secondly i wanted to ask you if i am handling the inserts into entities on my own, i want to use Transactions. I tried using TransactionScope in C# but thats not working. Do you have any recommendations for this or any other way i can achieve this.

Please let me know

Thanks

Sachin

Coordinator
Oct 31, 2012 at 6:01 PM

MDS will not allow an entity to be loaded with an invalid domain-based attribute.  So you generally need to load the entities in the correct order.

Also the MDS web services do not support transactions.  Each call to the web services is atomic.  Your options are

 

1) Use a TransactionScope for your _other_ transactional resources and commit or roll them back based on whether the MDS operations succeed.

2) Use the MDS transaction tracking function to implement compensating transactions in case of failure.

3) load data through Entity-Based Staging, which is transactional, instead of the Web Services

David

Oct 31, 2012 at 6:23 PM

Hi David

Thanks for your reply, i will have to do it through web services as i need to create members in entities as soon as they are creted in our CRM system in Dynamics.

Can i do Entity Based Staging Live? I mean not though SSIS. The only way i am aware of doing Entity Based Staging is though SSIS which will run like a job once, twice or n number of times daily but it will not be live.

If you have any suggestions please let me know.

Thanks

Sachin

Coordinator
Oct 31, 2012 at 7:28 PM

You can hit Entity Based Staging from your app.  You may not get great concurrency when hitting the staging tables from your code.

For instance you can put a stored procedure in another database on the same instance (or run a TSQL batch), something like:

 

create procedure [dbo].[insertSTATUS] (@code nvarchar(255), @name nvarchar(255), @description nvarchar(255) )
as
/*
BEGIN TRANSACTION
delete from mds2012.stg.STATUS_Leaf
exec [insertSTATUS] 'asdfadsf','abcdef', 'dkfjdkj'
select *
from mds2012.stg.viw_STATUS_MemberErrorDetails
select * from mds2012.stg.STATUS_Leaf 
ROLLBACK
*/
begin
    set nocount on
    declare @batchTag nvarchar(50) = cast(newid() as nvarchar(50))
    INSERT INTO mds2012.[stg].STATUS_Leaf  ( ImportType, BatchTag, Code,  Name, Description  )
    SELECT 2, @batchTag, @code, @name, @description

    EXEC mds2012.stg.udp_STATUS_Leaf  'VERSION_1', @BatchTag=@batchTag
    declare @ed nvarchar(255)
    declare @ec int;
    select @ed = errorDescription , @ec = UniqueErrorCode
    from mds2012.stg.viw_STATUS_MemberErrorDetails
    where batchTag = @batchtag
    delete from mds2012.stg.STATUS_Leaf where BatchTag = @batchTag
    if @ec < 50000 
        set @ec = 50001
    if (@ed is not null)
    begin
      throw  @ec, @ed, 1;
    end

end
Oct 31, 2012 at 7:39 PM

Hi David

Thanks for your suggestion.

Thanks

Sachin