Master Data Services

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  March 16, 2018 at 11:31 am

  #343973

  Hi,

  I have a solution on visual studio with a project that is a mirror copy of the MDS DB.
  When I try to build it, I get the following error.
  [mdm].[udfScriptExists].[@SchemaName]contains an unresolved reference to an object. Either the object does not existor the reference is ambiguous because it could refer to any of the followingobjects: [dbo].[SYSNAME] or [mdm].[SYSNAME].
  I am trying to build a continuous build and deploy using VSTS, but I am not sure what to do, and those are procedures that came with the MDS.

  Here is a sample of a procedure

  
/*   
==============================================================================   
 Copyright (c) Microsoft Corporation. All Rights Reserved.   
==============================================================================   
*/   
CREATE FUNCTION [mdm].[udfScriptExists]   
(   
@ScriptName     NVARCHAR(250),           
    @ScriptType     INT, -- 1 function, 2 sproc         
    @SchemaName     SYSNAME         
)            
RETURNS BIT           
AS BEGIN           
    DECLARE @Result BIT = 0        
        ,@ScriptObjectId INT = NULL   
        ,@MaxParameterId INT = 0;       
            
    IF SCHEMA_ID(@SchemaName) IS NOT NULL         
    BEGIN         
        IF @ScriptType = 2 -- sproc         
        BEGIN         
            SELECT @ScriptObjectId = object_id          
            FROM sys.objects         
            WHERE type ='P' AND schema_id = SCHEMA_ID(@SchemaName) AND name = @ScriptName       
               
            IF @ScriptObjectId IS NOT NULL   
            BEGIN   
                SELECT @Result = (CASE COUNT(*) WHEN 5 THEN 1 ELSE 0 END)   
                    FROM sys.parameters WHERE object_id = @ScriptObjectId   
                IF @Result = 1   
                BEGIN   
                    SELECT @Result = 0   
                    FROM sys.parameters WHERE object_id = @ScriptObjectId   
                    AND ( 
         (parameter_id = 1 AND (TYPE_NAME(user_type_id) <> 'MemberId' OR name <>N'@MemberIdList' COLLATE DATABASE_DEFAULT))   
                        OR(parameter_id = 2 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@ModelName' COLLATE DATABASE_DEFAULT)) 
      OR(parameter_id = 3 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@VersionName' COLLATE DATABASE_DEFAULT)) 
      OR(parameter_id = 4 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@EntityName' COLLATE DATABASE_DEFAULT)) 
      OR(parameter_id = 5 AND (TYPE_NAME(user_type_id) <>'nvarchar' OR max_length <> -1 OR name <>N'@BusinessRuleName' COLLATE DATABASE_DEFAULT)) 
                    )    
                END               
            END                 
               
        END         
        ELSE IF @ScriptType = 1 -- function         
        BEGIN          
            SELECT @ScriptObjectId = object_id          
            FROM sys.objects         
            WHERE type ='FN' AND schema_id = SCHEMA_ID(@SchemaName) AND name = @ScriptName       
       
            IF @ScriptObjectId IS NOT NULL       
            BEGIN       
                SELECT @Result = 1;       
                SELECT @Result = 0       
                FROM sys.parameters       
                WHERE object_id = @ScriptObjectId       
                AND ((is_output = 1 AND user_type_id <> TYPE_ID('bit'))       
                   OR (is_output = 0 AND      
                        ( user_type_id NOT IN (TYPE_ID('nvarchar'),TYPE_ID('datetime2'), TYPE_ID('decimal')) -- ONLY SUPPORT TYPE NVARCHAR, DATETIME2, AND DECIMAL     
                        OR (user_type_id = TYPE_ID('decimal') AND ([precision] <>38 OR scale>7)))))  -- IF TYPE IS DECIMAL, PRECISION SHOULD BE 38 WHICH IS THE DEFAULT ONE ,AND SCALE SHOULD BE IN THE ARRANGE OF 0 TO 7     
            END       
                   
                       
        END             
    END         
    RETURN @Result         
             
END


  What should I do?

  Thanks