Drop Table

Support Forum for database administrators and web based access to important newsgroups related to databases
Register on Database Support Forum Edit your profileCalendarFind other Database Support forum membersFrequently Asked QuestionsSearch this forum -> 
For Database admins: Free Database-related Magazines Now Free shipping to Texas


Post New Thread










Thread
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

Report this thread to moderator Post Follow-up to this message
Old Post
Ryan
01-24-06 02:23 PM

Re: Commit Transaction Gets Deleted - Unable to save SP
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
>

Report this thread to moderator Post Follow-up to this message
Old Post
MC
01-24-06 02:23 PM

Re: Commit Transaction Gets Deleted - Unable to save SP
Sorted. Thanks for the pointer. I should have spotted that earlier.

Ryan

Report this thread to moderator Post Follow-up to this message
Old Post
Ryan
01-24-06 04:23 PM

Sponsored Links



Last Thread Next Thread
Post New Thread

Microsoft SQL Server forum archive

Show a Printable Version Email This Page to Someone! Receive updates to this thread
Microsoft SQL Server
Access database support
PostgreSQL Replication
SQL Server ODBC
FoxPro Support
PostgreSQL pgAdmin
SQL Server Clustering
MySQL ODBC
Web Applications with dBASE
SQL Server CE
MySQL++
Sybase Database Support
MS SQL Full Text Search
PostgreSQL Administration
SQL Anywhere support
DB2 UDB Database
Paradox Database Support
Filemaker Database
Berkley DB
SQL 2000/2000i database
ASE Database
Forum Jump:
All times are GMT. The time now is 04:15 AM.

 
Mobile devices forum | Database support forum archive



Copyrights DropTable.com Database Support Forum 2004 - 2006