August 23, 2011 at 6:09 am
Hi
I've created a store procedure and while executing within Reporting Services 2008R2 I get the error message saying
'' An error occurred while executing the query. There is already an object named ' SystemDateTable' in the database. Additional Information - There is already an object named 'SystemDateTable' in the database. (Microsoft SQL Server, Error 2714)
Can someone have a look at the procedure that i have created and tell me where exactly I'm going wrong and what i need to do to change it.
[Please note if you need the tables and data let me know and I'll post that as well]
Thanks in advice
USE [DSReports]
GO
/****** Object: StoredProcedure [dbo].[Waterfall_New] Script Date: 08/23/2011 10:56:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Waterfall_New]
-- declaring the variables to be used in the procedure
(
@FieldDate VARCHAR(20) = null,
@Item varchar(50) = null ,
@Company varchar(50) = null,
@Division varchar(50) = null,
@Corporation varchar(50) = null,
@SoldTo varchar(50) = null,
@Department varchar(50) = null,
@ShipTo varchar(50) = null
)
AS
-- STAGE 1
select Item as SysDate
into SystemDateTable
from January
where item = '01 2011'
select Item as sysDate
into systemDateTable2
from February
where item = '02 2011'
-- STAGE 2
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,Class1,ShipTo,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into HorizontalTable
from January Cross Join SystemDateTable
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,ShipTo,Class1,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into HorizontalTable2
from February Cross Join systemDateTable2
--STAGE 3
select V.DataType, V.CaptureDate, V.FieldDate,V.Item,V.Company,V.Division,Corporation,V.SoldTo,V.Department,V.ShipTo
,V.Class1, V.Class2, V.Class3, V.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from VerticalTable V
union all
select V2.DataType, V2.CaptureDate, V2.FieldDate,V2.Item,V2.Company,V2.Division, V2.Corporation,V2.SoldTo,V2.Department,V2.ShipTo
,V2.Class1, V2.Class2, V2.Class3, V2.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from VerticalTable2 V2
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
August 23, 2011 at 6:34 am
the code you posted is not creating the table SystemDateTable
i assume what you psoted is just a portion of what you are doing, right?
in what you did paste, you are creating two permanant tables...look at the code that has these to snippets:
...
into HorizontalTable
...
into HorizontalTable2
...
that is creating two tables...unless you explicitly delete them whent he code is done, you'll get the error you are seeing table already exists.
if the tables are jsut being used inside the procedure, make them a temp table instead. otherwise, the procedure will fail every time it is called, becuase it cannot create the table(since it already exists)
...
INTO #SystemDateTable
...
into #HorizontalTable
...
into #HorizontalTable2
...
this creates a table unique to the session/procedure, that is automatically destroyed when it goes out of scope when the procedure ends.
Lowell
August 23, 2011 at 6:53 am
Try this.
USE [DSReports]
GO
/****** Object: StoredProcedure [dbo].[Waterfall_New] Script Date: 08/23/2011 10:56:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Waterfall_New]
-- declaring the variables to be used in the procedure
(
@FieldDate VARCHAR(20) = null,
@Item varchar(50) = null ,
@Company varchar(50) = null,
@Division varchar(50) = null,
@Corporation varchar(50) = null,
@SoldTo varchar(50) = null,
@Department varchar(50) = null,
@ShipTo varchar(50) = null
)
AS
-- STAGE 1
Insert into SystemDateTable
select Item as SysDate
from January
where item = '01 2011'
Insert into into systemDateTable2
select Item as sysDate
from February
where item = '02 2011'
-- STAGE 2
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,Class1,ShipTo,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into HorizontalTable
from January Cross Join SystemDateTable
select SysDate as CaptureDate,item,Company,Division,Corporation,SoldTo,Department,ShipTo,Class1,Class2,Class3,Class4,SysFcst#1,SysFcst#2
,SysFcst#3,SysFcst#4,SysFcst#5,SysFcst#6,SysFcst#7,SysFcst#8,SysFcst#9,SysFcst#10,SysFcst#11
,SysFcst#12,AdjFcst#1 as Fcst01,AdjFcst#2 as Fcst02,AdjFcst#3 as Fcst03,AdjFcst#4 as Fcst04,AdjFcst#5 as Fcst05,AdjFcst#6 as Fcst06,AdjFcst#7 as Fcst07,AdjFcst#8 as Fcst08
,AdjFcst#9 as Fcst09,AdjFcst#10 as Fcst10,AdjFcst#11 as Fcst11,AdjFcst#12 as Fcst12
into HorizontalTable2
from February Cross Join systemDateTable2
--STAGE 3
select V.DataType, V.CaptureDate, V.FieldDate,V.Item,V.Company,V.Division,Corporation,V.SoldTo,V.Department,V.ShipTo
,V.Class1, V.Class2, V.Class3, V.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from VerticalTable V
union all
select V2.DataType, V2.CaptureDate, V2.FieldDate,V2.Item,V2.Company,V2.Division, V2.Corporation,V2.SoldTo,V2.Department,V2.ShipTo
,V2.Class1, V2.Class2, V2.Class3, V2.Class4
,Fcst01 ,Fcst02 ,Fcst03 ,Fcst04,Fcst05,Fcst06,Fcst07,Fcst08,Fcst09,Fcst10,Fcst11,Fcst12
from VerticalTable2 V2
August 23, 2011 at 7:57 am
Thanks for your reply Lowell and kvishu83
@lowell its just a portion of the SP I've created. You are completely right with your advise it has solved the problem:-) i don't know why i didn't realize that before.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
August 23, 2011 at 8:03 am
Jnrstevej (8/23/2011)
Thanks for your reply Lowell and kvishu83@lowell its just a portion of the SP I've created. You are completely right with your advise it has solved the problem:-) i don't know why i didn't realize that before.
the proverbial cannot see the forest through the trees...
someone else's point of view gets you a different perspective! glad I could help!
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply