Error Message - There is already an object named 'SystemDateTable' in the database

  • 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)

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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)

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply