Install MDS on the Extranet

Hi,
I had to install MDS portal on the extranet (DMZ) and MDS database on the intranet. Here are some of the issues that I encountered.

Setup

DomainA (Intranet/Internal) ServerA SqlServer 2008 R2/2012
Firewall Firewall rule to let sql connection through. No Federated Domain
DomainB (Extranet/DMZ) ServerB IIS 7.5 make sure that IIS and WCF is installed correctly

Creating the database

Since I can't use Current User - Integrated Security I have to switch to SQL Server Account. Make sure that you create a sql login user with sufficient rights to create the database.

Creating Website

Everything works fine until you try to connect the database to the portal. You will get the following error:
Service Account Failure User or Role '(null)' does not exist in this database.
No problem you have to go the web.config file and add the settings manually. In this example I have added 2 sites dev and test

...
...    <masterDataServices>
        <instance virtualPath="" siteName="MDS_MTN_DEV" connectionName="MDS1" serviceName="MDS1" />
        <instance virtualPath="" siteName="MDS_MTN_TEST" connectionName="MDS2" serviceName="MDS2" />
    </masterDataServices>
    <connectionStrings>
        <add name="MDS1" connectionString="Data Source=[ServerNameOrServerIP];Initial Catalog=mds;User Id=[username];Password=[password];Connect Timeout=60" />
        <add name="MDS2" connectionString="Data Source=[ServerNameOrServerIP];Initial Catalog=mds_test;User Id=[username];Password=[password];Connect Timeout=60" />
    </connectionStrings>
...
...    
    <service behaviorConfiguration="mdsWsHttpBehavior" name="MDS1">
        <endpoint binding="wsHttpBinding" bindingConfiguration="mdsWsHttpBinding"
         bindingNamespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"
         contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" />
        <endpoint address="bhb" binding="basicHttpBinding" bindingConfiguration="mdsBasicHttpBinding"
         bindingNamespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"
         contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" />
       </service>
       <service behaviorConfiguration="mdsWsHttpBehavior" name="MDS2">
        <endpoint binding="wsHttpBinding" bindingConfiguration="mdsWsHttpBinding"
         bindingNamespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"
         contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" />
        <endpoint address="bhb" binding="basicHttpBinding" bindingConfiguration="mdsBasicHttpBinding"
         bindingNamespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"
         contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" />
       </service>
...
...

If you close and reopen Master Data Services Configuration Manager and go the website you will see that the website is now connected to the database.

Testing the MDS Portal

When viewing an entity in explorer you may get the following errors:
IE8 Error Message [HttpWebRequest_WebException_RemoteServer] Arguments: NotFound Debugging resource strings are unavaialble. Often the key and argumetns provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/linkid=1066638&Version5.0.61118.08&File=System.Windows.dll&Key=HttpWebRequest_WebException_RemoteServer
IE9 The remote server returned an error: NotFound.

Testing the MDS Service

Check Authorization on the Portal and Service in IIS Portal – Windows Authentication Enabled Service – Anonymous Authentication Enabled and Windows Authentication Enabled You may need to do an IISRESET. http://localhost/service/service.svc
Error Message for Anonymous Authentication not enabled Security settings for this service require 'Anonymous' Authentication but it is not enabled for the IIS application that hosts this service.  Error Message for Windows Authentication not enabled Security settings for this service require Windows Authentication but it is not enabled for the IIS application that hosts this service.
Error Message: The HttpsGetEnabled property of ServiceMetadataBehavior is set to true and the HttpsGetUrl property is a relative address, but there is no https base address.  Either supply an https base address or set HttpsGetUrl to an absolute address.
Change the web.config file to the following if only using http httpsGetEnabled="false"
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="false"/>
After all that it works!!!

MDSModelDeploy.exe Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I was trying to automate the creation and deployment of my MDS model and came across the following issue.

If you try to run the following powershell command

invoke-command -computername Servername -scriptblock {& 'C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSModelDeploy.exe' createpackage -model TestModel  -package c:\Data\LocalBackup\TestModelackage.pkg -version VERSION_1 -service MDS2 -includedata}

you get the following error

Creating a package for model MTN

MDSModelDeploy operation failed. Elapsed time: 00:00:06.4551044

Error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I enabled trace on MDSModelDeploy

Error Log

MDS Information: 0 : Attempting to execute command: MDSModelDeploy createpackage -model TestModel -package c:\Data\LocalBackup\TestModelackage.pkg -version VERSION_1 -service MDS2 -includedata

    DateTime=2012-03-31T10:35:02.3896659Z

MDS Error: 0 : Service started successfully,  Assembly version: 11.0.0.0, file version: 11.0.2100.60 ((SQL11_RTM).120210-1917 )

    DateTime=2012-03-31T10:35:10.6863285Z

MDS Error: 0 : ApiContractVersion: 5102

    DateTime=2012-03-31T10:35:10.6863285Z

MDS Start: 1 : Service.InitializeExpirationStatus

    DateTime=2012-03-31T10:35:10.6863285Z

MDS Information: 0 : Evaluation period days remaining code: 0

    DateTime=2012-03-31T10:35:10.7488269Z

MDS Stop: 2 : Service.InitializeExpirationStatus

    DateTime=2012-03-31T10:35:10.7488269Z

MDS Start: 1 : Deployment: getting list of models

    DateTime=2012-03-31T10:35:10.7644515Z

MDS Start: 1 :

                  Begin operation: ExecuteRequest

                  Host info:       Microsoft.MasterDataServices.Core.ExplicitHostContext

                  User Name:       Domain\username

                  Time:            03/31/2012 21:35:10

    DateTime=2012-03-31T10:35:10.9050729Z

MDS Verbose: 0 : Request message:

<MetadataGetRequest xmlns="http://schemas.datacontract.org/2004/07/Microsoft.MasterDataServices.Services.MessageContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <International i:nil="true" xmlns:a="http://www.w3.org/2005/09/ws-i18n" />

  <ResultOptions xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">

    <a:Models>Identifiers</a:Models>

  </ResultOptions>

  <SearchCriteria xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">

    <a:AttributeGroups />

    <a:Attributes />

    <a:DerivedHierarchies />

    <a:DerivedHierarchyLevels />

    <a:Entities />

    <a:ExplicitHierarchies />

    <a:MemberTypes />

    <a:Models />

    <a:VersionFlags />

    <a:Versions />

  </SearchCriteria>

</MetadataGetRequest>

    DateTime=2012-03-31T10:35:11.0613189Z

MDS Start: 1 : RequestContext.InitializeRequestContext

    DateTime=2012-03-31T10:35:11.0613189Z

MDS Start: 1 : RequestContext.InitializeDatabaseContext

    DateTime=2012-03-31T10:35:11.0613189Z

MDS Stop: 2 :

                  End  operation: MetadataGet

                  Host info:       Microsoft.MasterDataServices.Core.ExplicitHostContext

                  User Name:       [Domain]\[Username]

                  Time:            03/31/2012 21:35:11

    DateTime=2012-03-31T10:35:11.2956879Z

MDS Verbose: 0 : Response message:

<MetadataGetResponse xmlns="http://schemas.datacontract.org/2004/07/Microsoft.MasterDataServices.Services.MessageContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <OperationResult xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">

    <a:Errors />

    <a:RequestId>d8cedd0f-34cd-4b43-b43b-e0fd860bb44d</a:RequestId>

  </OperationResult>

  <Metadata xmlns:a="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09">

    <a:AttributeGroups />

    <a:Attributes />

    <a:DerivedHierarchies />

    <a:DerivedHierarchyLevels />

    <a:Entities />

    <a:ExplicitHierarchies />

    <a:MemberTypes />

    <a:Models />

    <a:VersionFlags />

    <a:Versions />

  </Metadata>

</MetadataGetResponse>

    DateTime=2012-03-31T10:35:11.4206847Z

MDS Error: 0 :

    DateTime=2012-03-31T10:35:11.4206847Z

MDS Error: 0 : MDSModelDeploy operation failed. Elapsed time: 00:00:09.1523012

    DateTime=2012-03-31T10:35:11.4206847Z

MDS Error: 0 : Error:

    DateTime=2012-03-31T10:35:11.4206847Z

MDS Error: 0 : System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.ExecuteNonQueryHandlingTransportError(SqlCommand sqlCommand)

   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.GetTimeout(String settingName)

   at Microsoft.MasterDataServices.Core.DataAccess.DbHelper.SetConnection(String connectionString)

   at Microsoft.MasterDataServices.Core.BusinessEntities.RequestContext.InitializeDatabaseContext()

   at Microsoft.MasterDataServices.Core.BusinessEntities.RequestContext.InitializeRequestContext(HostContext hostContext)

   at Microsoft.MasterDataServices.Services.Service.MetadataGet(MetadataGetRequest request)

   at Microsoft.MasterDataServices.WebUI.ServiceAdapter.ExecuteRequest[TRequestType,TResponseType](MdmServiceOperation`2 operation, TRequestType request)

   at Microsoft.MasterDataServices.WebUI.ServiceAdapter.DoMetadataGet(MetadataSearchCriteria searchCriteria, MetadataResultOptions resultOptions)

   at Microsoft.MasterDataServices.Deployment.ModelReader.GetModels(Boolean isAdminOnly)

   at Microsoft.MasterDataServices.Deployment.Utility.ModelDeploy.CreatePackage(String serviceName, String packageFile, String modelName, String versionName, Boolean includeData)

   at Microsoft.MasterDataServices.Deployment.Utility.ModelDeploy.Main(String[] args)

    DateTime=2012-03-31T10:35:11.4206847Z

After using reflector I managed to find the code that is causing the issue

private void InitializeRequestContext(HostContext hostContext)

{

    Log.WriteStart("RequestContext.InitializeRequestContext");

    if (hostContext == null)

    {

        throw new ArgumentNullException("hostContext");

    }

    this.HostContext = hostContext;

    this.InitializeDatabaseContext();

    this.InitializeCurrentUser();

    Log.WriteStop("RequestContext.InitializeRequestContext");

}

Not sure if calling InitializeCurrentUser before InitializeDatabaseContext will fix the problem. As I didn’t have to time to decompile all the MDS code and try to get it to compile.

Work around

Is to set the connection string to sql login instead of Integrated Security

from        <add name="MDS2" connectionString="Data Source=servername;Initial Catalog=database;Integrated Security=True;Connect Timeout=60" />

to            <add name="MDS2" connectionString="Data Source=servername;Initial Catalog=database;User Id=sqlloginuser;Password=password;Connect Timeout=60" />

To test that winrm is working correctly run the following command

invoke-command -computername yourserver -scriptblock {& 'C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration\MDSModelDeploy.exe' listservices}

Result

MDS services (Service, Website, Virtual Path):

MDS1, Website1_DEV,

MDS2, Website2_DEV,

MDSModelDeploy operation completed successfully. Elapsed time: 00:00:06.7047133

MDS (Master Data Services) SQL 2012 installing on SQL 2008 R2

MDS (Master Data Services) SQL 2012 is out with new enhancements to make development a lot easier. The issue is, is business ready to move to SQL 2012... So I took a shot and tried installing MDS SQL 2012 on SQL 2008 R2 instance.
Steps
1. install MDS SQL 2012 from ISO
2.Run Microsoft SQL Server 2012\Master Data Services\Master Data Services Configuration Manager.exe
3. Create new database point to SQL 2008 R2 instance.
4. Create new Web page

Everything run smoothly and portal came up.

http://msdn.microsoft.com/en-us/library/gg488708(v=sql.110).aspx#noengine

Master Data Services (MDS) Clean Model of Carriage Return and New line

DECLARE @sqlquery NVARCHAR(4000)

DECLARE RemoveCr CURSOR FOR

SELECT DISTINCT --m.Name, e.Name, e.EntityTable, a.DisplayName,a.TableColumn, e.*,

'UPDATE mdm.' + e.EntityTable + ' SET ' + a.TableColumn + ' = SUBSTRING(' + a.TableColumn + ', 0, CHARINDEX ( CHAR(13), ' + a.TableColumn + ' ) ) WHERE ' + a.TableColumn + ' LIKE ''%'' + CHAR(13) + CHAR(10)+ ''%'''

--'SELECT ' + a.TableColumn + ' FROM mdm.' + e.EntityTable + ' WHERE ' + a.TableColumn + ' LIKE ''%'' + CHAR(13) + CHAR(10)+ ''%'''

FROM mdm.tblEntity e

INNER JOIN mdm.tblModel m ON e.Model_ID = m.ID

INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID

INNER JOIN mdm.tblAttribute a ON a.Entity_ID = e.ID

INNER JOIN mdm.tblList lAtt ON a.AttributeType_ID = lAtt.OptionID AND lAtt.ListCode = 'lstAttributeType'

INNER JOIN mdm.tblList lDat ON a.DataType_ID = lAtt.OptionID AND lDat.ListCode = 'lstDataType'

WHERE m.Name <> 'Metadata'

AND mv.Name = 'VERSION_1'

AND lAtt.ListOption <> 'System'

AND lDat.ListOption = 'Text'

AND IsBase = 0

OPEN RemoveCr

FETCH NEXT FROM RemoveCr INTO @sqlquery

WHILE (@@FETCH_STATUS = 0)

BEGIN

 EXEC(@sqlquery)

 FETCH NEXT FROM RemoveCr INTO @sqlquery

END

CLOSE RemoveCr

DEALLOCATE RemoveCr

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

Master Data Services (MDS) Rebuild Subscription Views

--Build Delete Statements

SELECT 'EXEC mdm.udpSubscriptionViewDeleteByID @ID=' + CONVERT(VARCHAR(10), ID) + ' ,@DeleteView=1' FROM mdm.tblSubscriptionView  WHERE IsDirty = 1

--Build Create Statements

SELECT 'EXEC mdm.udpSubscriptionViewSave @SubscriptionView_ID=NULL,@Entity_ID='  + CONVERT(VARCHAR(10), Entity_ID) + ',@Model_ID='  + CONVERT(VARCHAR(10), Model_ID) + ',@DerivedHierarchy_ID=NULL,@ModelVersion_ID='  + CONVERT(VARCHAR(10), ModelVersion_ID) + ',@ModelVersionFlag_ID=NULL,@ViewFormat_ID='  + CONVERT(VARCHAR(10), ViewFormat_ID) + ',@Levels=NULL,@SubscriptionViewName=N''' + Name + '''' FROM mdm.tblSubscriptionView  WHERE IsDirty = 1

Master Data Services (MDS) error ERR210055 and how to solve it

If you are me you try to do things differently, most of the time they work out on this occasion something went wrong.

MDS Interface does not let you change the length of an member (Free Text) once you have created. If you know the structure of MDS and how it works you can go to the table in this case tbl_3_38 indentify the column name by using the following query

SELECT a.Name, a.TableColumn, a.DataTypeInformation,*

FROM [mdm].[tblAttribute] a

INNER JOIN mdm.tblEntity e ON a.Entity_ID = e.ID

WHERE e.Name = 'Product'

AND e.Model_ID = 3

Make the changes to the table and the [mdm].[tblAttribute] row and rebuild all your views including viw_SYSTEM_3_38_CHILDATTRIBUTES

Here is where the fun starts when I tried to stage the data I get the following error

Error: ERR210055

Description: An unknown error occurred when staging member

record.

Tips for fixing the issue: If an unhandled exception occurs during the staging

process, all records will be marked with this error.

This error may have nothing to do with records that

display this error.

Against each row, nice. Problem solving time what went wrong.

Find the stored proc that MDS is using that generated the error by doing a text search against all the stored procs in MDS database and the result is udpStagingMemberSave.

Next I had to add the following to code

SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;

to the mdm.udpStagingMemberSave to display the error.

2601       Cannot insert duplicate key row in object 'mdm.tbl_3_38_EN' with unique index 'ix_tbl_3_38_EN_Version_ID_VersionMember_ID'. The duplicate key value is (3, ).

You will have to run mdm.udpStagingMemberSave manually otherwise you will not see the result.

Helpful sql

UPDATE [RegDistMDS].[mdm].[tblStgMember] SET Status_ID = 0, ErrorCode =''

EXEC mdm.udpStagingMemberSave 1, --@User_ID admin user

                              3, --@Version_ID,

                              2, --@LogFlag Defualt

                              39 --@Batch_ID the last one processed

Eventually you find this dynamic sql is causing the error. I’ve highlighted what is causing the issue.

                     INSERT INTO mdm.' + quotename(@Entity_Table) + N'  

                        ( 

                             Version_ID  

                            ,VersionMember_ID 

                            ,AsOf_ID --Use this column to map between new @@IDENTITY and Stage_ID within OUTPUT clause 

  

                            ,Status_ID 

                            ,ValidationStatus_ID 

                            ,Name 

                            ,Code 

                            ,EnterDTM 

                            ,EnterUserID 

                            ,EnterVersionID 

                            ,LastChgDTM 

                            ,LastChgUserID 

                            ,LastChgVersionID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeCons THEN N',Hierarchy_ID'  

                                    WHEN @MemberTypeColl THEN N',Owner_ID' 

                                    ELSE N'' 

                                END + N' 

                        ) 

                        OUTPUT inserted.AsOf_ID, inserted.ID INTO #tblMemberID(Stage_ID, ID) 

                        SELECT 

                             ' + @strVersion_ID + N' --Version_ID                          

                            ,NULL -- Set the default Member_ID 

                            ,Stage_ID --AsOf_ID  

                            ,1 --Status_ID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeColl THEN N',3 --Set ValidationStatus_ID TO 3 (validation succeeded) for collection members since business rules does not apply ' 

                                    ELSE N',0 --Set ValidationStatus_ID to New AwaitingValidation' 

                                 END + N'     

                            ,Member_Name 

                            ,Member_Code 

                            ,GETUTCDATE() --EnterDTM 

                            ,' + @strUser_ID + N' --EnterUserID 

                            ,' + @strVersion_ID + N' --EnterVersionID 

                            ,GETUTCDATE() --LastChgDTM 

                            ,' + @strUser_ID + N' --LastChgUserID 

                            ,' + @strVersion_ID + N' --LastChgVersionID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeCons THEN N',' + ISNULL(@strHierarchy_ID, N'NULL --Hierarchy_ID') 

                                    WHEN @MemberTypeColl THEN N',' + @strUser_ID + N' --Owner_ID' 

                                    ELSE N'' 

                                END + N' 

                        FROM #tblStage  

                        WHERE Status_ID = @StatusOK;

So eventually you will find that that the following index is to blame.

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mdm].[tbl_3_38_EN]') AND name = N'ix_tbl_3_38_EN_Version_ID_VersionMember_ID')

CREATE UNIQUE NONCLUSTERED INDEX [ix_tbl_3_38_EN_Version_ID_VersionMember_ID] ON [mdm].[tbl_3_38_EN]

(

      [Version_ID] ASC,

      [VersionMember_ID] ASC

)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]

GO

Somehow when I used the SSMS in design mode to change the table it overwrote the index incorrectly. Yeah so much joy

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mdm].[tbl_3_38_EN]') AND name = N'ix_tbl_3_38_EN_Version_ID_VersionMember_ID')

CREATE UNIQUE NONCLUSTERED INDEX [ix_tbl_3_38_EN_Version_ID_VersionMember_ID] ON [mdm].[tbl_3_38_EN]

(

      [Version_ID] ASC,

      [VersionMember_ID] ASC

)

WHERE ([VersionMember_ID] IS NOT NULL)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]

GO