|
|
Posted by Ryan on 01/24/06 16:36
I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.
I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)
I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :
Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.
Also, why does the line get removed once I save the SP ?
If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.
SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)
Thanks in advance
Ryan
CREATE PROCEDURE [dbo].[JAG_Extract] (@ExtractYear INTEGER,
@ExtractMonth INTEGER) AS
BEGIN TRANSACTION
SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
UPDATE #Max SET MaxYear = @ExtractYear
UPDATE #Max SET MaxMonth = @ExtractMonth
PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
CREATE TABLE #Extract (
[DEALER_SOURCE_DATA_ID] INT,
[DSD_YEAR] INT NULL,
[DSD_MONTH] INT NULL,
[DEALER_CODE] VarChar(20),
[FranDealerCode] VarChar(20) NULL,
[Line_No] VarChar(75),
[Current] [numeric](15, 5) NULL,
[YTD] [numeric](15, 5) NULL,
[12Months] [numeric](15, 5) NULL,
[24Months] [numeric](15, 5) NULL,
[Average_YTD] [numeric](15, 5) NULL,
[Average12Months] [numeric](15, 5) NULL,
[Average24Months] [numeric](15, 5) NULL,
[Last_YTD] [numeric](15, 5) NULL,
[Current_STATUS] INT,
[PD1] [numeric](15, 5) NULL,
[PD2] [numeric](15, 5) NULL,
[PD3] [numeric](15, 5) NULL,
[PD4] [numeric](15, 5) NULL,
[PD5] [numeric](15, 5) NULL,
[PD6] [numeric](15, 5) NULL,
[PD7] [numeric](15, 5) NULL,
[PD8] [numeric](15, 5) NULL,
[PD9] [numeric](15, 5) NULL,
[PD10] [numeric](15, 5) NULL,
[PD11] [numeric](15, 5) NULL,
[PD12] [numeric](15, 5) NULL,
[PD13] [numeric](15, 5) NULL,
[PD14] [numeric](15, 5) NULL,
[PD15] [numeric](15, 5) NULL,
[PD16] [numeric](15, 5) NULL,
[PD17] [numeric](15, 5) NULL,
[PD18] [numeric](15, 5) NULL,
[PD19] [numeric](15, 5) NULL,
[PD20] [numeric](15, 5) NULL,
[PD21] [numeric](15, 5) NULL,
[PD22] [numeric](15, 5) NULL,
[PD23] [numeric](15, 5) NULL,
[PD24] [numeric](15, 5) NULL,
[PD25] [numeric](15, 5) NULL,
[PD26] [numeric](15, 5) NULL,
[PD27] [numeric](15, 5) NULL,
[PD28] [numeric](15, 5) NULL,
[PD29] [numeric](15, 5) NULL,
[PD30] [numeric](15, 5) NULL,
[PD31] [numeric](15, 5) NULL,
[PD32] [numeric](15, 5) NULL,
[PD33] [numeric](15, 5) NULL,
[PD34] [numeric](15, 5) NULL,
[PD35] [numeric](15, 5) NULL,
[PD36] [numeric](15, 5) NULL)
INSERT INTO #Extract
SELECT DISTINCT
SD.DEALER_SOURCE_DATA_ID,
SD.DSD_YEAR,
SD.DSD_MONTH,
DN.DEALER_CODE,
DN.FRAN_DEALER_CODE,
DV.FIELD_CODE,
0,
0,
0,
0,
0,
0,
0,
0,
SD.STATUS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,49,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,112,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTION
[Back to original message]
|