Competition: What’s your favorite Redgate tool? Enter now.

Scripts Folder Stored Procedure Identical Name issue

bhopenwbhopenw Posts: 39
edited December 2, 2008 10:21AM in SQL Compare Previous Versions
Hello,

I am attempting to create a scripts folder of a DB that will be added to source control (TFVC). The Sync fails b/c there are several SP that SQL Compare is telling me have identical names. These SP are named the same but are appended with a number; for example sp_GetBillingInfo.sql, sp_GetBillingInfo1.sql and sp_GetBillingInfo2.sql. While versions one and two are very similar to GetBillingInfo they are used at different times and are unique.

With that said how to get SQL Compare to understand that these SP are different? I understand that could force the dev team rename the SP to a more appropriate name like sp_GetBillingInfo4Step1.sql instead of sp_GetBillingInfo1.sql. I may try this but I also want to know if there are any other options for me.

Thanks,

BJHop

Comments

  • Are the actual stored procedure names just appended with a number (e.g. CREATE PROC sp_GetBillingInfo, CREATE PROC sp_GetBillingInfo1 etc) or are they named as 'numbered stored procedures' (e.g. CREATE PROC sp_GetBillingInfo;1, CREATE PROC sp_GetBillingInfo;2 etc) or in fact all named the same thing inside the files?

    The filenames don't matter to SQL Compare, just the contents of the files.
    Software Developer
    Redgate Software
  • Example of names are:
    dbo.SDISP_HLIC_GET_INQUIRY.sql
    dbo.SDISP_HLIC_GET_INQUIRY1.sql
    dbo.SDISP_HLIC_GET_INQUIRY2.sql

    These two SP are different

    Error Message:
    "A duplicate stored procedure name ([DBO].SDISP_HLIC_GET_INQUIRY]) has been found. This may occur if the SQL Server that you are reqistering is case sendsitive but the case sensitive option is not set"

    I've seen this once before when I was using Sql Compare v7.0 and we had some Full Text search code (Constraints) that it didn't like. The compare would keep added a new SP with a number appended it like above to the scripts folder.

    Trouble here is the SP are in the Live DB

    Thanks,

    BJHop
  • The names of the files don't matter - what are the names of the actual stored procedures (the string just after the CREATE PROCEDURE or CREATE PROC)?

    Is this a script folder that you've just saved from a live database with SQL Compare, or a script folder you've made in some other way?
    Software Developer
    Redgate Software
  • Okay
    I forgot to have my V8 this morning

    just realized that real issue was not the fore mentioned SP but the one just below it

    dbo.SDISP_HLIC_GET_InquiryMaster.sql

    and the error message what telling me it all along

    I thought I had case sensitive option set

    Nevertheless, I am facing a new issue one that I have had before and just mentioned. SQL Compare keep considering one SP as different even I've synced multiple times.

    Issues is large block of Commented out code, as soon I ignore comments all is fine but I'd like to have comments synced

    Below is the code:
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO
    
    
    
    -- =============================================
    -- Author:		L&T Infotech
    -- Create date: 12th-Nov-2008
    -- Description:	To assign the products with the Waiver premium to a respective employee
    -- =============================================
    CREATE PROCEDURE [dbo].[SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium]
    	(	@p_employee_id bigint,
    		@p_user_ad_id varchar(30),
    		@p_user_role varchar(30),
    		@p_prod_waiverofpremium_xml xml	)
    AS
    BEGIN
    	SET NOCOUNT ON
    -- =============================================	
    BEGIN TRY
    	
    		CREATE TABLE #temp_PROD_DETAILS 
    			( ROWID INT IDENTITY(1,1),
    			  PLAN_TYPE VARCHAR(30),
    			  PRODUCT_NAME VARCHAR(30),
    			  EMP_PROD_RL_ID BIGINT,
    			  WAIVER_OF_PREM_EFFECTIVE_DATE DATETIME,
    			  WAIVER_OF_PREM_END_DATE DATETIME	)
    
    		DECLARE @v_xmlhandle int
    	
    		EXEC SP_XML_PREPAREDOCUMENT @v_xmlhandle output, @p_prod_waiverofpremium_xml 
    		
    		INSERT INTO #temp_PROD_DETAILS
    					(PLAN_TYPE,
    					PRODUCT_NAME,
    					EMP_PROD_RL_ID,
    					WAIVER_OF_PREM_EFFECTIVE_DATE,
    					WAIVER_OF_PREM_END_DATE	)
    		(SELECT * FROM OPENXML (@v_xmlhandle, '/root/prod_waiverofpremium', 2)
    		 WITH (plan_type varchar(30),
    				  product_name varchar(30),
    				  emp_prod_rl_id bigint,
    				  waiverofpremium_effective_date datetime,
    				  waiverofpremium_end_date datetime	) )
    
    select * from #temp_PROD_DETAILS
    
    		DECLARE @v_nextrowid INT	
    		SET @v_nextrowid = (SELECT COUNT(*) FROM #temp_PROD_DETAILS)
    
    WHILE @v_nextrowid > 0
    BEGIN 
    
    			UPDATE PCPRCE
    			SET PCPRCE.WAIVER_OF_PREM_EFFECTIVE_DATE = temp.WAIVER_OF_PREM_EFFECTIVE_DATE,
    				   PCPRCE.WAIVER_OF_PREM_END_DATE	= temp.WAIVER_OF_PREM_END_DATE,
    				   PCPRCE.MODIFIED_BY = @p_user_ad_id,
    				   PCPRCE.MODIFIED_TM = GETDATE()
    			FROM
    					PLAN_CLS_PROD_RL_CLS_EMPLOYEE as PCPRCE
    						INNER JOIN #temp_PROD_DETAILS as temp on PCPRCE.PLAN_CLS_PROD_RL_CLS_EMP_ID = temp.EMP_PROD_RL_ID
    			WHERE 
    					temp.rowid = @v_nextrowid
    			AND 	PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0
    
    SET @v_nextrowid = @v_nextrowid - 1
    CONTINUE
    
    END -- WHILE LOOP END
    
    END TRY
    -- =============================================
    
    	BEGIN CATCH
    		EXEC dbo.SDIsp_HLIC_Exception_Log 'Error while assigning the Waiver Products to the Employee'
    	END CATCH
    
    	SET NOCOUNT OFF
    END
    
    
    
    -- =================
    
    /*
    
    EXEC SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium
    @p_employee_id = 860,
    @p_user_ad_id = 'EMPLOYER13',
    @p_user_role = 'EMPLOYER',
    @p_prod_waiverofpremium_xml =
    '<root>
    	<product_details>
    		<emp_prod_rl_id>535</emp_prod_rl_id>				
    		<plan_type>TRUE GROUP</plan_type>
    		<product_name>BASELIFE</product_name>
    		<waiverofpremium_effective_date>2008/12/02</waiverofpremium_effective_date>
    		<waiverofpremium_end_date>2009/05/30</waiverofpremium_end_date>
    	</product_details>
    </root>'
    
    
    
    select * from PLAN_CLS_PROD_RL_CLS_EMPLOYEE
    where PLAN_CLS_PROD_RL_CLS_EMP_ID = 535
    
    */
    
    
    /*
    		SELECT 
    				PC.CLASS_ID,
    				CER.CLS_EMP_ID,
    				PCP.CLASS_PRODUCT_ID,
    --				PCPRCE.PLAN_CLS_PROD_RL_CLS_EMP_ID,
    				PCPD.PROD_LIFE_INS_DETAIL_ID,
    				PM.PRODUCT_NAME
    		INTO #temp_1
    		FROM
    				EMPLOYEE E
    							INNER JOIN CLASS_EMPLOYEE_RL AS  CER ON E.EMPLOYEE_ID = CER.EMPLOYEE_ID
    							INNER JOIN PLAN_CLASS AS PC ON CER.CLASS_ID = PC.CLASS_ID
    							INNER JOIN PLANS AS P ON PC.PLAN_ID = P.PLAN_ID 
    							INNER JOIN PLAN_TYPE_MASTER AS PTM ON P.PLAN_TYPE_ID = PTM.PLAN_TYPE_ID
    							INNER JOIN PLAN_CLASS_PRODUCTS AS PCP ON PC.CLASS_ID = PCP.CLASS_ID
    							INNER JOIN PLAN_CLASS_PRODUCT_DETAIL AS PCPD ON PCP.CLASS_PRODUCT_ID = PCPD.CLASS_PRODUCT_ID
    							INNER JOIN PRODUCT_MASTER AS  PM ON PCPD.PRODUCT_ID = PM.PRODUCT_ID
    							INNER JOIN #temp_PROD_DETAILS AS temp ON PTM.PLAN_NAME = temp.PLAN_TYPE AND PM.PRODUCT_NAME = temp.PRODUCT_NAME
    --							INNER JOIN PLAN_CLS_PROD_RL_CLS_EMPLOYEE AS PCPRCE ON CER.CLS_EMP_ID = PCPRCE.CLS_EMP_ID AND PCP.CLASS_PRODUCT_ID = PCPRCE.CLASS_PRODUCT_ID
    		WHERE
    				temp.ROWID = @v_nextrowid
    		AND E.EMPLOYEE_ID = @p_employee_id	
    		AND P.CLIENT_ID = @v_client_id
    		AND E.ACTIVE_IND = 'Y' AND E.VERSION_NO = 0		
    --		AND CER.ACTIVE_IND = 'Y' AND CER.VERSION_NO = 0		
    		AND P.ACTIVE_IND = 'Y' AND P.VERSION_NO = 0		
    		AND PC.ACTIVE_IND = 'Y' AND PC.VERSION_NO = 0		
    		AND PCP.ACTIVE_IND = 'Y' AND PCP.VERSION_NO = 0		
    		AND PCPD.ACTIVE_IND = 'Y' AND PCPD.VERSION_NO = 0		
    --		AND PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0		
    
    select * from #temp_1
    
    SELECT 
    PLAN_CLS_PROD_RL_CLS_EMP_ID
    FROM 
    PLAN_CLS_PROD_RL_CLS_EMPLOYEE AS PCPRCE
    		INNER JOIN #temp_1 AS T ON PCPRCE.CLS_EMP_ID = T.CLS_EMP_ID	AND PCPRCE.CLASS_PRODUCT_ID = T.CLASS_PRODUCT_ID
    --WHERE
    --		PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0
    
    */
    
    
    GO
    
    
    GRANT EXECUTE ON  [dbo].[SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium] TO [HLIC_READ]
    GO
    
Sign In or Register to comment.