|
Home > Archive > Microsoft SQL Server forum > January 2006 > Commit Transaction Gets Deleted - Unable to save SP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Commit Transaction Gets Deleted - Unable to save SP
|
|
|
| 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 (
& #91;DEALER_SOURCE_DA
TA_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,
& #91;Average12Months]
[numeric](15, 5) NULL,
& #91;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_I
D,
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_V
ALUES_Current DV WITH (NOLOCK)
ON SD. DEALER_SOURCE_DATA_I
D = DV. DEALER_SOURCE_DATA_I
D
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_R
ELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,1
7,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
| |
|
| You need to remove 'GO' keyword.
Its a batch separator so you basically have two independent parts. Second
part is : COMMIT TRANSACTION.
MC
"Ryan" <ryanofford@hotmail.com> wrote in message
news:1138113402.871088.270810@g43g2000cwa.googlegroups.com...
> 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 (
> & #91;DEALER_SOURCE_DA
TA_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,
> & #91;Average12Months]
[numeric](15, 5) NULL,
> & #91;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_I
D,
> 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_V
ALUES_Current DV WITH (NOLOCK)
> ON SD. DEALER_SOURCE_DATA_I
D = DV. DEALER_SOURCE_DATA_I
D
> AND SD.STATUS < 4096
> INNER JOIN DEALER_FIXED_GROUP_R
ELATION GR WITH (NOLOCK)
> ON DN.DEALER_CODE = GR.DEALER_CODE
> AND GR.FIXED_GROUP_ID IN
> (11,12,13,14,15,16,1
7,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
>
| |
|
| Sorted. Thanks for the pointer. I should have spotted that earlier.
Ryan
|
|
|
|
|