Thursday, March 8, 2012

Master Data Services (MDS) Moving Subscription Views


SET NOCOUNT ON
-------------------------------------------------------------------------------
-- Change the following variables
-------------------------------------------------------------------------------
DECLARE @Server VARCHAR(100) = 'SERVERNAME'
DECLARE @OrginalDatabase VARCHAR(100) = 'MDSDev'
DECLARE @DestinationDatabase VARCHAR(100) = 'MDSProd'
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DECLARE @execQuery VARCHAR(1000) = 'SELECT * INTO ##Views FROM OPENROWSET(''SQLNCLI'', ''server=' + @Server + ';trusted_connection=yes'', ''set fmtonly off exec ' + @OrginalDatabase + '.[mdm].[udpSubscriptionViewGet]'')'
EXEC(@execQuery)

DECLARE @View_Name NVARCHAR(200)
DECLARE @Model_Name NVARCHAR(200)
DECLARE @Entity_Name NVARCHAR(200)
DECLARE @query NVARCHAR(4000)
DECLARE db_Cursor CURSOR FOR

SELECT View_Name, Model_Name, Entity_Name FROM ##Views
OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = ''
SET @query = 'DECLARE @EntityName NVARCHAR(200) = ''' + @Entity_Name + '''' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelName NVARCHAR(200) = ''' + @Model_Name + '''' + CHAR(10)
SET @query = + @query + 'DECLARE @Entity_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Model_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelVersion_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @ModelVersionFlag_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Return_ID int' + CHAR(10)
SET @query = + @query + 'DECLARE @Return_MUID uniqueidentifier' + CHAR(10)
SET @query = + @query + CHAR(10)
SET @query = + @query + 'SELECT @Model_ID = m.id, @Entity_ID=e.ID, @ModelVersion_ID = mv.ID, @ModelVersionFlag_ID = mv.VersionFlag_ID FROM mdm.tblModel m' + CHAR(10)
SET @query = + @query + 'INNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID' + CHAR(10)
SET @query = + @query + 'INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID' + CHAR(10)
SET @query = + @query + 'WHERE mv.Name = ''VERSION_1''' + CHAR(10)
SET @query = + @query + 'AND m.NAME = @ModelName' + CHAR(10)
SET @query = + @query + 'AND e.Name = @EntityName' + CHAR(10)
SET @query = + @query + CHAR(10)
SET @query = + @query + 'EXECUTE [' + @DestinationDatabase + '].[mdm].[udpSubscriptionViewSave]' + CHAR(10)
SET @query = + @query + '@SubscriptionView_ID=NULL' + CHAR(10)
SET @query = + @query + ',@Entity_ID = @Entity_ID' + CHAR(10)
SET @query = + @query + ',@Model_ID = @Model_ID' + CHAR(10)
SET @query = + @query + ',@DerivedHierarchy_ID = NULL' + CHAR(10)
SET @query = + @query + ',@ModelVersion_ID = @ModelVersion_ID ' + CHAR(10)
SET @query = + @query + ',@ModelVersionFlag_ID = @ModelVersionFlag_ID' + CHAR(10)
SET @query = + @query + ',@ViewFormat_ID = 1' + CHAR(10)
SET @query = + @query + ',@Levels = NULL' + CHAR(10)
SET @query = + @query + ',@SubscriptionViewName = ''' + @View_Name + '''' + CHAR(10)
SET @query = + @query + ',@Return_ID = @Return_ID OUTPUT' + CHAR(10)
SET @query = + @query + ',@Return_MUID = @Return_MUID OUTPUT' + CHAR(10)
SET @query = + @query + 'GO' + CHAR(10)
      PRINT @query
      FETCH NEXT FROM db_Cursor INTO @View_Name, @Model_Name, @Entity_Name

END
CLOSE db_Cursor
DEALLOCATE db_Cursor
DROP TABLE ##Views
GO

1 comment:

  1. Asoka Packers and Movers Hyderabad is the most consistently ranked packers and Movers Company and has delivered over 56,000 homes all over India
    Packers and Movers Bangalore
    Packers and Movers Pune

    ReplyDelete