Scripts Folder Stored Procedure Identical Name issue

bhopenw

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

Michelle T

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.

bhopenw

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

Michelle T

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?

bhopenw

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