Scripts Folder Stored Procedure Identical Name issue

• 
  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.

  Michelle T December 02, 2008 06:58

  - 0 +
• 
  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

  bhopenw December 02, 2008 09:24

  - 0 +
• 
  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?

  Michelle T December 02, 2008 09:34

  - 0 +
• 
  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
  

  bhopenw December 02, 2008 10:21

  - 0 +

Add comment

Please sign in to leave a comment.