Possibly doing to much in Stored Proc

[ease20022002]

Hi,

I have a stored procedure in SQL 2005 that creates a table at the beginning of the SP, inserts into the newly created table and then from that newly created table I insert into a Fact table joining with all of the Dimension tables to create my foreign keys in the fact table.

The reason I create a table in this stored proc is because there is an unavoidable WHERE clause that has to search through text fields with wildcares like '%Value%'. So if I have the WHERE clause in the insert portion with all of the Dimension joins, it takes forever, so I can't do that.

The SP ran fine last week during the first insert, but now it doesn't run. If I run the stored proc, piece by piece by highlighting each section and running it, all of the SQL works fine. I am wondering if I need to insert GO references or Begin/End references to break up the processing. Below is the Stored Proc. I do not believe any of the issues deal the Transaction processing. Any help is appreciated.

USE [FinancialDB]
GO
/****** Object:  StoredProcedure [dbo].[uspTimeDetailInsertExtractCognos]    Script Date: 10/22/2009 14:14:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspTimeDetailInsertExtractCognos]
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	CREATE TABLE [dbo].[ExtractCognos2](
		[Year] [varchar](4) NULL,
		[Month] [varchar](2) NULL,
		[Week] [datetime] NULL,
		[Program] [varchar](20) NULL,
		[Project] [varchar](20) NULL,
		[ProjectDescription] [varchar](255) NULL,
		[UserRespCode] [varchar](10) NULL,
		[Resource] [varchar](50) NULL,
		[ShortName] [varchar](10) NULL,
		[System] [varchar](10) NULL,
		[CompCode] [varchar](20) NULL,
		[ResourceSource] [varchar](20) NULL,
		[ResourceType] [varchar](20) NULL,
		[Chargeable] [bit] NULL,
		[ResourceRole] [varchar](100) NULL,
		[Activity] [varchar](255) NULL,
		[StdHrs] [numeric](18, 4) NULL,
		[OTHrs] [numeric](18, 4) NULL,
		[DblOTHrs] [numeric](18, 4) NULL,
		[ActualFTEs] [numeric](18, 4) NULL,
		[Cost] [numeric](18, 4) NULL
	) ON [PRIMARY]
	
	SET ANSI_PADDING OFF
		
	
    DECLARE @v_trans_started BIT

	BEGIN TRY
		PRINT 'Executing uspTimeDetailInsertExtractCognos.'

		SET @v_trans_started = 0
		IF @@TRANCOUNT = 0
		  BEGIN
		     BEGIN TRANSACTION;
		     SET @v_trans_started = 1
		  END
		ELSE
			
			SET @v_trans_started = 0
			
			INSERT INTO [FinancialDB].[dbo].[ExtractCognos2]
			   ([Year],[Month],[Week],[Program],[Project],[ProjectDescription]
			   ,[UserRespCode],[Resource],[ShortName],[System],[CompCode],[ResourceSource]
			   ,[ResourceType],[Chargeable],[ResourceRole],[Activity]
			   ,[StdHrs]
			   ,[OTHrs]
			   ,[DblOTHrs]
			   ,[ActualFTEs]
			   ,[Cost])
    
			SELECT [Year],[Month],[Week],[Program],[Project],[ProjectDescription]
				  ,[UserRespCode],[Resource],[ShortName],[System],[CompCode],[ResourceSource]
				  ,[ResourceType],[Chargeable],[ResourceRole],[Activity]
				  ,Sum([StdHrs]) as StdHrs
				  ,Sum([OTHrs]) as OTHrs
				  ,Sum([DblOTHrs]) as DblOTHrs
				  ,Sum([ActualFTEs]) as ActualFTEs
				  ,Sum([Cost]) as Cost
			FROM [FinancialDB].[dbo].[ExtractCognos]
			WHERE ((Project Like 'CLPRJ%' Or Project Like 'CLENH%' Or Project Like 'CLPRD%') 
					Or (UserRespCode In (Select ReportRespCode From DimReportRespCodes)) 
					Or (ProjectDescription Like '%CLPRJ%' Or ProjectDescription Like '%CLENH%' Or ProjectDescription Like '%CLPRD%'))
			Group By 	 [Year],[Month],[Week],[Program],[Project],[ProjectDescription] ,[UserRespCode]
				  ,[Resource],[ShortName],[System],[CompCode],[ResourceSource],[ResourceType],[Chargeable]
				  ,[ResourceRole],[Activity]
						

		    INSERT INTO [FinancialDB].[dbo].[FactTimeDetail]
           ([YearID]
           ,[MonthID]
           ,[WeekID]
           ,[ProgramID]
		   ,[ProjectID]
           ,[ProjectDescriptionID]
           ,[UserRespCodeID]
           ,[ResourceID]
           ,[ShortNameID]
           ,[ResourceTypeID]
           ,[Chargeable]
           ,[ResourcesourceID]
           ,[ResourceRoleID]
           ,[ActivityID]
           ,[CompCodeID]
           ,[STDHrs]
           ,[OTHrs]
           ,[ActualFTEs]
           ,[Cost])
    
			SELECT     DimYears.YearID, DimMonths.MonthID, DimWeeks.WeekID, DimPrograms.ProgramID, DimProjects.ProjectID, DimProjectDescriptions.ProjectDescriptionID, DimUserRespCodes.UserRespCodeID, DimResources.ResourceID, 
								  DimShortNames.ShortNameID, DimResourceTypes.ResourceTypeID, ExtractCognos2.Chargeable, DimResourceSources.ResourceSourceID, DimResourceRoles.ResourceRoleID, DimActivities.ActivityID, 
								  DimCompCodes.CompCodeID, 
								  ExtractCognos2.STDHrs, 
								  ExtractCognos2.OTHrs, 
								  ExtractCognos2.ActualFTEs, 
								  ExtractCognos2.Cost
			               
			FROM       ExtractCognos2 INNER JOIN DimYears
								  ON DimYears.Year = ExtractCognos2.Year INNER JOIN
								  DimMonths ON ExtractCognos2.Month = DimMonths.Month INNER JOIN
								  DimWeeks ON ExtractCognos2.Week = DimWeeks.Week INNER JOIN
								  DimPrograms On ExtractCognos2.Program = DimPrograms.Program INNER JOIN
								  DimProjects ON ExtractCognos2.Project = DimProjects.Project INNER JOIN
								  DimResources ON ExtractCognos2.Resource = DimResources.Resource INNER JOIN
								  DimResourceTypes ON ExtractCognos2.ResourceType = DimResourceTypes.ResourceType INNER JOIN
								  DimResourceSources ON ExtractCognos2.ResourceSource = DimResourceSources.ResourceSource INNER JOIN
								  DimResourceRoles ON ExtractCognos2.ResourceRole = DimResourceRoles.ResourceRole INNER JOIN
								  DimActivities ON DimActivities.Activity = ExtractCognos2.Activity INNER JOIN
								  DimCompCodes ON DimCompCodes.CompCode = ExtractCognos2.CompCode INNER JOIN
								  DimShortNames ON ExtractCognos2.ShortName = DimShortNames.ShortName INNER JOIN
								  DimUserRespCodes ON ExtractCognos2.UserRespCode = DimUserRespCodes.UserRespCode INNER JOIN
								  DimProjectDescriptions ON ExtractCognos2.ProjectDescription = DimProjectDescriptions.ProjectDescription
		
			Drop Table ExtractCognos2						
		
		IF @v_trans_started = 1
			BEGIN
				SET @v_trans_started = 0
				COMMIT TRANSACTION;
			END

   END TRY

   BEGIN CATCH

      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            ROLLBACK TRANSACTION;
         END
      RAISERROR ('Error in uspTimeDetailInsertExtractCognos!',16,1)

   END CATCH
END

Thanks for any help provided.

 

[markros]

From the quick glance I see nothing wrong in this SP

 

[ease20022002]

Found the answer.

"SET ANSI_PADDING OFF"

When I comment that out the sp works b.c there are schema bound views that pull all of the true data values back together for quering and I have a unique index and nonclustered indexes on my views. I believe the ANSI PADDING OFF was affecting the already set schema for the views and created the error.

Comments and corrections welcome.

 

[genomon]

Just curious - why the semicolon here: SET NOCOUNT ON; ?

Anyway,

I do not believe any of the issues deal the Transaction processing

I could be wrong here, but I think by enclosing the entire
ALTER PROCEDURE [dbo].[uspTimeDetailInsertExtractCognos]
AS
BEGIN
...
END
In a BEGIN/END block, it is treated as one big implicit transaction. Your transaction and TRY/Catch are not coded until after the table ExtractCognos2 is created. What would happen if you tried to create the table and it already exists? Maybe an unhandled error that would fail silently?

Cogito eggo sum – I think, therefore I am a waffle.

 

[genomon]

Looks like I took too long writing a reply. Glad you got it working!

Cogito eggo sum – I think, therefore I am a waffle.

 

[ease20022002]

Genomon, thanks for the post. I did place the Create table in the transaction handling portion. I must've forgot it was there when I was redoing my stored procs for transactions.

 

[Emtucifor]

You do NOT have to put stored procedures in a big BEGIN/END block. And I personally don't like to do it because then every line takes an extra, unnecessary tab.