February 27, 2009 at 2:31 pm
Okay this is odd. We are on SQL Server 2000. Not sure if I need to give you any further information than what is provided below.
We have a standard process that has been in use for many, many years. But(!), of course, now a bit of it needs to be changed.
Basically, all of our reports have a standard #TempTable we put the data into and pass that to a standard formatting procedure before display.
Now, a significant handful of reports need a new column to that #TempTable. The handful of new reports have that extra column being passed, but the existing procedures do not. To that end, the Procedure below [ Report_Processing ] will run an ALTER TABLE… ADD to get those existing procedures into synch.
Here is where this seems odd to me. I can run the new reports, [ Report_New ] without error. But, when I run the existing reports, the new column errors on the INSERT. Yet, when I simply do a straight SELECT * FROM… I can see the new column does exist in the passed #TempTable.
Compile the procedures below and run this:
EXECUTE Report
EXECUTE Report_New
You will see the data displayed.
Then, start playing around with the formatting procedure [ Report_Processing ] to see if you can SELECT the data by column name or if you can INSERT the data into the @TableVariable, ( I have tried this with a #TempTable as well ). I have tried using COMMIT statements and other attempts, but to no avail.
I will appreciate anyone’s help.
Thanks In Advance.
------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report_Processing]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report_Processing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report_Processing
( @proc_name varchar(80))
AS
-- New #Temp_Table to deal with changed reports.
DECLARE @rpt TABLE( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime,
New_Amt integer DEFAULT 0)
---- This new column must be added for those reports which are not NEW
IF @proc_name NOT LIKE '%_NEW%'
BEGIN
ALTER TABLE #rpt ADD New_Amt integer DEFAULT 0
END
--INSERT INTO @rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT *
--SELECT Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, ISNULL( New_Amt, 0)
FROM #rpt
SELECT * FROM @rpt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report
AS
DECLARE @proc_name varchar(80)
SET @proc_name = 'Report'
-- New #Temp_Table to deal with changed reports.
CREATE TABLE #rpt( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime)
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run)
SELECT 1 AS Main_ID,
1.2 AS Percentage,
'001' AS srt1,
'First Level Sort One' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run)
SELECT 2 AS Main_ID,
2.2 AS Percentage,
'002' AS srt1,
'First Level Sort Two' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run)
SELECT 3 AS Main_ID,
2.1 AS Percentage,
'003' AS srt1,
'First Level Sort Three' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run
EXEC Report_Processing
@proc_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report_New]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report_New]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report_New
AS
DECLARE @proc_name varchar(80)
SET @proc_name = 'Report_New'
-- New #Temp_Table to deal with changed reports.
CREATE TABLE #rpt( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime,
New_Amt integer DEFAULT 0)
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT 1 AS Main_ID,
1.2 AS Percentage,
'001' AS srt1,
'First Level Sort One' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run,
12345 AS New_Amt
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT 2 AS Main_ID,
2.2 AS Percentage,
'001' AS srt1,
'First Level Sort Two' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run,
67890 AS New_Amt
INSERT INTO #rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT 3 AS Main_ID,
2.1 AS Percentage,
'001' AS srt1,
'First Level Sort Three' AS srt1nm,
'001' AS srt2,
'Second Level Sort' AS srt2nm,
'001' AS srt3,
'Third Level Sort' AS srt3nm,
GETDATE() AS time_run,
45678 AS New_Amt
EXEC Report_Processing
@proc_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I wasn't born stupid - I had to study.
February 27, 2009 at 2:37 pm
It looks like your procedure Report_Processing is trying to alter a #rpt table but you are creating a table variable @rpt. Am I missing something or could that be the issue.
If you could also include the error that you are seeing that would be helpful as well.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 2:48 pm
DavidB (2/27/2009)
It looks like your procedure Report_Processing is trying to alter a #rpt table but you are creating a table variable @rpt. Am I missing something or could that be the issue.If you could also include the error that you are seeing that would be helpful as well.
Thanks.
The table @rpt is actually much larger in the real procedure. A small amount of information is passed to it and much more information is included in it for final display in the report.
This is just a simple display to show the problem.
---------------------------
When I use:
INSERT INTO @rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
--SELECT *
SELECT Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, ISNULL( New_Amt, 0)
FROM #rpt
in Report_Processing, ( i.e., it is commented out originally, when I put it back into use ), I get the following error:
Server: Msg 207, Level 16, State 3, Procedure Report_Processing, Line 20
Invalid column name 'New_Amt'.
I wasn't born stupid - I had to study.
February 27, 2009 at 2:54 pm
Unless I am mistaken, you can't "alter" a table variable but you can the #temp tables. So, if that is what you are trying to do it will not add the column, only error.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 3:02 pm
DavidB (2/27/2009)
Unless I am mistaken, you can't "alter" a table variable but you can the #temp tables. So, if that is what you are trying to do it will not add the column, only error.
I am altering the #TempTable.
If you want, make the @TableVariable into a #TempTable. I beleive you will still get the same error when you run the Report procedure.
I wasn't born stupid - I had to study.
February 27, 2009 at 3:30 pm
Try changing the procedure to the following;
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report_Processing]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report_Processing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report_Processing
( @proc_name varchar(80))
AS
-- New #Temp_Table to deal with changed reports.
CREATE TABLE #rpt( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime)
---- This new column must be added for those reports which are not NEW
IF @proc_name NOT LIKE '%_NEW%'
BEGIN
ALTER TABLE #rpt ADD New_Amt integer DEFAULT 0
END
--INSERT INTO @rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT *
--SELECT Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, ISNULL( New_Amt, 0)
FROM #rpt
SELECT * FROM #rpt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 3:42 pm
DavidB (2/27/2009)
Try changing the procedure to the following;IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report_Processing]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report_Processing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report_Processing
( @proc_name varchar(80))
AS
-- New #Temp_Table to deal with changed reports.
CREATE TABLE #rpt( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime)
---- This new column must be added for those reports which are not NEW
IF @proc_name NOT LIKE '%_NEW%'
BEGIN
ALTER TABLE #rpt ADD New_Amt integer DEFAULT 0
END
--INSERT INTO @rpt( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
SELECT *
--SELECT Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, ISNULL( New_Amt, 0)
FROM #rpt
SELECT * FROM #rpt
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I can't really do that since #rpt is the name of the #TempTable that is passed into Report_Processing.
I tried naming the @TableVariable ( @rpt ) to #rpt2, hence making it a #TempTable rather than a @TableVariable:
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Report_Processing]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Report_Processing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.Report_Processing
( @proc_name varchar(80))
AS
-- New #Temp_Table to deal with changed reports.
CREATE TABLE #rpt2( Main_ID integer,
Percentage decimal(5,1),
srt1 varchar(80) DEFAULT ' ',
srt1nm varchar(80) DEFAULT ' ',
srt2 varchar(80) DEFAULT ' ',
srt2nm varchar(80) DEFAULT ' ',
srt3 varchar(80) DEFAULT ' ',
srt3nm varchar(80) DEFAULT ' ',
Time_Run datetime,
New_Amt integer DEFAULT 0)
---- This new column must be added for those reports which are not NEW
IF @proc_name NOT LIKE '%_NEW%'
BEGIN
ALTER TABLE #rpt ADD New_Amt integer DEFAULT 0
END
INSERT INTO #rpt2( Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, New_Amt)
--SELECT *
SELECT Main_ID, Percentage, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run, ISNULL( New_Amt, 0)
FROM #rpt
SELECT * FROM #rpt2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXECUTE Report
...yields this error:
Server: Msg 2705, Level 16, State 4, Procedure Report_Processing, Line 18
Column names in each table must be unique. Column name 'New_Amt' in table '#rpt' is specified more than once.
EXECUTE Report_New
...works. But it did before changing the @TableVariable to a #TempTable
-------------------------------------
I think if you run what you suggested, you will see no data returned as the output.
I wasn't born stupid - I had to study.
February 27, 2009 at 3:47 pm
I think I understand what you are trying to do, but I am not sure it is needed. Can you just create a new formatter procedure with the new column available and use that for all new reports and leave the old stuff around?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2009 at 3:47 pm
Your create procedure script will work fine with the #rpt2 table but you need to take the last column out of the create statement as that is the column that you try to add if the @procedure_name is not like '%_NEW'.
Let me know if you don't see it and I will modify what I put up the last time so that you can use it.
I will say too that even though the temp table name #rpt looks the same, if you select out of sysobjects in tempdb you will see it appends a big long string on the end of the name to ensure uniqueness of names. I ran what I posted last time and it worked ok with both the report and report_new procedures.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 3:52 pm
Jeffrey Williams (2/27/2009)
I think I understand what you are trying to do, but I am not sure it is needed. Can you just create a new formatter procedure with the new column available and use that for all new reports and leave the old stuff around?
The problem is, the last column either exists or not. If it does exist, we need the data. If not, NULL is fine.
When you do the insert, testing for existence of this column is what becomes the issue. If it does exist, everything is dandy. If it does not, the procedure will error and abort.
I wasn't born stupid - I had to study.
February 27, 2009 at 3:55 pm
DavidB (2/27/2009)
Your create procedure script will work fine with the #rpt2 table but you need to take the last column out of the create statement as that is the column that you try to add if the @procedure_name is not like '%_NEW'.Let me know if you don't see it and I will modify what I put up the last time so that you can use it.
I will say too that even though the temp table name #rpt looks the same, if you select out of sysobjects in tempdb you will see it appends a big long string on the end of the name to ensure uniqueness of names. I ran what I posted last time and it worked ok with both the report and report_new procedures.
Hope this helps.
I don't see what you are trying to explain - not sure which SP you are referencing when you discuss the last column.
I realize they are technically two different tables, but the INSERT is using the latter, hence the data from the former, ( the #rpt which is passed into Report_Processing ) has no data any longer. If you run this from your first suggestion, you will see that Report_New does not return any data.
I wasn't born stupid - I had to study.
February 27, 2009 at 3:57 pm
Ok. I will have to play more later. They both ran ok for me so I left it at that. Wasn't looking for results.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 27, 2009 at 3:59 pm
DavidB (2/27/2009)
Ok. I will have to play more later. They both ran ok for me so I left it at that. Wasn't looking for results.
Thanks!
Have a great weekend!
Farrell
I wasn't born stupid - I had to study.
March 7, 2009 at 4:12 pm
I was hoping to bring this up again.
A friend looked into this and suggested doing the ALTER within the Report SP. Problem with that is, we a very large amount of these procedures and changing them all is not realisitc at this point.
Has anyone looked at this and found another idea?
tia
I wasn't born stupid - I had to study.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply