none
Return values from MDS staging stored procedure

    Question

  • I am learning about MDS 2012 and how to load data from stg table to mdm entities. I loaded data in stg table with appropriate values and executed code below

    USE [MDS]
    GO

    DECLARE @return_value int

    EXEC @return_value = [stg].[udp_TestTable_Leaf]
      @VersionName = N'VERSION_1',
      @LogFlag = 0,
      @BatchTag = N'test'

    SELECT 'Return Value' = @return_value

    GO

    This returns value 6 and does nothing. I am unable to load this data. The only way I can start batch from UI. This does not give me any error codes that are listed at http://msdn.microsoft.com/en-us/library/ff487022.aspx

    When I add BatchID to above code, it gives me return value of 7 and message that I cannot provide BatchTag and BatchID at the same time. I am wondering what are the return values?


    Rajendra

    Tuesday, November 12, 2013 1:10 AM

All replies

  • Hi Rajendra

    I had a similar problem and posted a similiar question on this forum but was able to work through the issue and answer it.

    If you search on "staging table update stored proc not working" in this forum you will see the thread I created.

    Essentially if you are using the stored proc to update the stg tables then use BATCH_ID, BATCH_TAG is used when you update from the MDS Internet app.

    I commented out the "RETURN_VALUE" code as per below:

    USE [MDS]
    GO
    ---DECLARE @return_value int
    EXEC comment out the @return_value param = [stg].[udp_CATEGORIES_Leaf]
    [stg].[udp_CATEGORIES_Leaf]
    @VersionName = N'VERSION_1',
    @LogFlag = 1,
    @Batch_ID = 1
    ---SELECT 'Return Value' = @return_value
    GO

    I recall the stored proc also asks for the update type which you don't see here - refer to the MDS documentation on the Microsoft site but I think 0 is the best value to use intiailly.

    Check the current Batch_id in your table via the MDS schema in SQL Server and ensure you have the correct Batch_ID in your stored proc.

    Hope this helps

    Friday, November 22, 2013 4:27 AM