ease20022002
Technical User
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.
Thanks for any help provided.
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.
Code:
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.