Posted by MC on 01/24/06 17:05

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 (
> [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]