?
Solved

SQL Server 2005: WITH INDEX... (Yardi Enterprise 46.8.25)

Posted on 2006-05-03
3
Medium Priority
?
1,241 Views
Last Modified: 2008-01-09
I'm trying to load a package via the Yardi Enterprise Utility program and get the below in my package log.  Below the log entry is the actual SQL from the package file. (Meant for SQL Server 2000 but I'm trying it on SQL Server 2005)

----- Starting Import Package Function 5/3/2006 2:43:42 PM Filename: C:\Program Files\Yardi46\Library\SQL_Server\DBA_Tools\ss_Rebuild.pkg -----

----- UserName: ysi; Database Host: dallas

Successfully executed sql statement: /*
 Revision History
 MODIFIED   (MM/DD/YY)
     JayS   09/15/99 - Was only creating totals for maximum post date per prop.
     Remon  11/18/99 - sbegin should not be populated in future months--...

Successfully executed sql statement:
 /* Procedure :       Add Recs
    Function  :       Adds records into the total table for all accounts starting
                 from the min date for that property/acct/book to the maximum
              ...

WARNING: Update previously done for statement:
 
 DROP PROCEDURE rebuild_tot_per_prop
 
      Return 3701 Cannot drop the procedure 'rebuild_tot_per_prop', because it does not exist or you do not have permission.

ERROR: Unable to execute sql statement:
 /*
    Procedure :       Rebuild_tot_per_prop
    Function  :       Rebuild the total table for a single property given a date from which to
                         rebuild      retained earings A/c no and the operating month fo...
      Return 1018 Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Successfully executed sql statement:
 
 DROP PROCEDURE rebuild_totals
 

ERROR: Unable to execute sql statement:
 /*
    Procedure :       Rebuild_totals
    Function  :       Rebuild the total table for a single/all properties given a date from which to
                         rebuild      and the operating month
    Date           :  02/03/99...
      Return 2007 Cannot add rows to sysdepends for the current object because it depends on the missing object 'rebuild_tot_per_prop'. The object will still be created.

Results: 6 SQL statements executed.  (2 sql errors reported.)  2 total errors encountered

----- Ending Import Package Function 5/3/2006 2:43:44 PM Filename: C:\Program Files\Yardi46\Library\SQL_Server\DBA_Tools\ss_Rebuild.pkg -----

SQL from SS_Rebuild.pkg

//notes

 Copyright (c) 2001 by Yardi Systems
   NAME
     Utilities Rebuild Totals
   DESCRIPTION
     Creates Stored Procedures used by Rebuild Totals function in Utilities.
   NOTES
     This script is for SQL Server only.
   MODIFIED   (MM/DD/YY)
   
//end notes
//sql
/*
Revision History
MODIFIED   (MM/DD/YY)
    JayS   09/15/99 - Was only creating totals for maximum post date per prop.
    Remon  11/18/99 - sbegin should not be populated in future months-- sw/mh
    JayS   04/14/00 - calc max_date from detail/trans, not totals
    Mark   05/10/00 - Added trans.itype for 4.2 new batch
    Mark   01/18/01 - Added code for MCA
    SumanM 07/02/01 - Added isnull on max cash and accrual dates, otherwise stored procedure might hang.
    Mark   08/21/01 - Added isnull to all creation of amounts
    Mark   09/21/01 - Update retaned earnings to max of (current month, cashpost, accrualpost, or charges)

*/



DROP PROCEDURE add_recs
GO

/* Procedure :       Add Recs
   Function  :       Adds records into the total table for all accounts starting
                from the min date for that property/acct/book to the maximum
                date for that property in the totals table.
   Date           :  02/03/99
*/
create procedure dbo.add_recs(
   @p_hppty numeric,
   @p_from_dt datetime,
   @p_maxdt datetime,
   @v_acct numeric )
AS

      declare @c_tot_hPpty numeric(18,0)
      declare @c_tot_hacct numeric(18,0)
      declare @c_tot_iBook int
      declare @c_tot_min_month datetime
      declare @c_tot_irpttype numeric
      declare @c_tot_last_mon datetime
      declare @t_dt datetime
      declare @istatus int
      declare @dummy varchar(1)

      /* Cursor gets distinct rows for a prop/acct/ibook and min date */

      declare c1_add_recs cursor for
      select t.hppty,
            t.hacct,
            t.ibook,
            min(t.umonth),
            a.irpttype,
            @p_maxdt
      from
            total t, acct a
      where
            t.hppty = @p_hppty
            and a.hmy = t.hacct
            and t.umonth >= @p_from_dt
            group by
                  t.hppty,
                  t.hacct ,
                  a.irpttype,
                  t.ibook

      BEGIN

      open c1_add_recs
      fetch next from c1_add_recs into @c_tot_hPpty, @c_tot_hacct, @c_tot_iBook, @c_tot_min_month, @c_tot_irpttype, @c_tot_last_mon
      while @@fetch_status = 0
      begin
            select @t_dt = @c_tot_min_month
            while (@t_dt <= @c_tot_last_mon)
            begin
                  /* Cursor selecting rows from total to check for existence of rows for all umonths */

            declare c2_add_recs cursor for
            select 'x' from total
            where hppty = @c_tot_hppty
            and   hacct = @c_tot_hacct
            and   ibook = @c_tot_iBook
            and   umonth = @t_dt

                  Open c2_add_recs
                  fetch next from c2_add_recs into @dummy
                  if @@fetch_status <> 0           -- If the row does not exist insert a row
                  begin
                        insert into
                              Total  (UMONTH                  ,
                                    IBOOK                  ,
                                    HACCT                  ,
                                    HPPTY                  ,
                                    SBEGIN                  ,
                                    SMTD                  ,
                                    SBEGINBUDGET      ,
                                    SBUDGET)
                              values (@t_dt                  ,
                                    @C_TOT_IBOOK      ,
                                    @C_TOT_HACCT      ,
                                    @C_TOT_HPPTY      ,
                                    0,
                                    0,
                                    0,
                                    0)
                  End
                  close c2_add_recs
                  select @t_dt = dateadd(mm, 1, @t_dt)
                  deallocate c2_add_recs
            end
            fetch next from c1_add_recs into @c_tot_hPpty, @c_tot_hacct, @c_tot_iBook, @c_tot_min_month, @c_tot_irpttype, @c_tot_last_mon
      End
      close c1_add_recs
      deallocate c1_add_recs

      /* Cursor to create the rows for retained earnings */

      declare c3_add_recs cursor for
      select  t.hppty hppty,
            @v_acct hacct,
            t.ibook ibook,
            min(t.umonth) min_month,
            @p_maxdt last_mon
      from
            total t
      where
            t.hppty = @p_hppty
            and t.umonth >= @p_from_dt
      group by
            t.hppty, t.ibook

      open c3_add_recs
      fetch next from c3_add_recs into @c_tot_hPpty, @c_tot_hacct, @c_tot_iBook, @c_tot_min_month, @c_tot_last_mon
      while @@fetch_status = 0
      begin
            select @t_dt = @c_tot_min_month
            while @t_dt <= @c_tot_last_mon
            begin
                  declare c2_add_recs cursor for
                          select 'x' from total
                          where hppty = @c_tot_hppty
                            and   hacct = @c_tot_hacct
                            and   ibook = @c_tot_iBook
                            and   umonth = @t_dt
                  Open c2_add_recs
                  fetch next from c2_add_recs into @dummy
                  if @@fetch_status <> 0 -- If the row does not exist insert a row
                  begin
                        insert into Total (      UMONTH         ,
                                          IBOOK          ,
                                          HACCT          ,
                                          HPPTY          ,
                                          SBEGIN         ,
                                          SMTD           ,
                                          SBEGINBUDGET,
                                          SBUDGET      )
                              values (      @t_dt         ,
                                          @C_TOT_IBOOK,
                                          @C_TOT_HACCT,
                                          @C_TOT_HPPTY,
                                          0         ,
                                          0           ,
                                          0,
                                          0)
                  End
                  close c2_add_recs
                  select @t_dt = dateadd(mm, 1, @t_dt)
                  deallocate c2_add_recs
            End
            fetch next from c3_add_recs into @c_tot_hPpty, @c_tot_hacct, @c_tot_iBook, @c_tot_min_month, @c_tot_last_mon
      End
      close c3_add_recs
      deallocate c3_add_recs


      --print 'end of add_recs'

      return

END

GO


DROP PROCEDURE rebuild_tot_per_prop
GO

/*
   Procedure :       Rebuild_tot_per_prop
   Function  :       Rebuild the total table for a single property given a date from which to
                        rebuild      retained earings A/c no and the operating month for calculation of
                        beginning balances
   Date           :  02/03/99
   Modified  :  JayS   09/15/99 - Was only creating totals for maximum post date per prop.
             :  MarkPo 01/18/01 - Modified to work with MCA
*/
create procedure dbo.rebuild_tot_per_prop(
      @p_hppty numeric,                  -- Property Handle (Null for all props)
      @p_from_dt datetime,            -- From Date
      @p_op_month datetime )            -- operating month
AS

      declare @v_from_dt datetime
      declare @v_EndOfYear Numeric(18,0)
      declare @vs_EndOfYear char(2)
      declare @v_acct numeric(18,0)
      declare @v_max_date datetime
      declare @v_max_date_cash datetime
      declare @v_max_date_accrual datetime
      declare @istatus int
      declare @num char(18)
      declare @msg varchar(60)

BEGIN

      /* Gets retained earings a/c handle */
      select @v_acct = hretain from param where hChart = isnull((select hChart from lockout where hprop = @p_hppty), 0)

      /* Assumes a date '01-jan-1951' as the start date for rebuilding totals if
         it is not passed through the procedure parameters */

      if (@p_from_dt is null)
      begin
            select @v_from_dt = convert(datetime, '01/01/1951', 101)
      end
      else
      begin
            select @v_from_dt = convert(datetime, @p_from_dt, 101)
      end


      /* Delete from retain Earnings */

      delete from total
            where total.hPpty = @p_hppty
                  and total.hAcct = @v_acct
                  and total.uMonth >= @v_from_dt

      /* Initialize Totals Table */

      update total
            set smtd = 0, sBegin = 0
            where hPpty =  @p_hppty
            and uMonth >= @v_from_dt

      /* Creates rows in a temporary table based on the transactions in Trans and detail table */

      INSERT INTO TEMP_TOT (HACCT, UMONTH, AMT, IBOOK, HPPTY)
      (
      SELECT       d.hacct acct, d.CashPost pdate, sum(-d.samount) sm, 0 book, d.hProp ppty
      FROM       trans t, detail d (INDEX=I_DETAIL_8)
      WHERE       d.hInvOrRec BETWEEN 600000000 AND 699999999
    AND     d.hInvOrRec = t.hMy
      AND     d.hProp =  @p_hppty
      AND     d.cashPost >= @v_from_dt
    AND     t.itype = 6
      GROUP BY d.hacct, d.CashPost, d.hProp
      UNION ALL
      SELECT       t1.hoffsetacct, d.CashPost, sum(d.samount) , 0 book, d.hProp
      FROM       trans t1, detail d (INDEX=I_DETAIL_8)
      WHERE       t1.hmy = d.hInvOrRec
      AND       d.hInvOrRec BETWEEN 600000000 AND 699999999
      AND          d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t1.itype = 6
      GROUP BY t1.hoffsetacct, d.CashPost, d.hProp
      UNION ALL
      SELECT       isnull(d.hAccrualAcct, d.hacct) acct, d.CashPost pdate, sum(-d.samount) sm, 1 book, d.hProp
      FROM       trans t, detail d (INDEX=I_DETAIL_8)
      WHERE       d.hInvOrRec BETWEEN 600000000 AND 699999999
    AND     d.hInvOrRec = t.hMy
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t.itype = 6
      GROUP BY isnull(d.hAccrualAcct, d.hacct), d.CashPost, d.hProp
      UNION ALL
      SELECT       t1.hOffsetAcct acct, d.CashPost pdate, sum(d.samount) sm, 1 book, d.hProp
      FROM       trans t1, detail d (INDEX=I_DETAIL_8)
      WHERE       t1.hMy = d.hInvOrRec
      AND       d.hInvOrRec BETWEEN 600000000 AND 699999999
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t1.itype = 6
      GROUP BY t1.hOffsetAcct, d.CashPost, d.hProp
      UNION ALL
      SELECT       d.hAcct, d.CashPost, sum(d.sAmount), 0, d.hProp
      FROM       trans t, detail d (INDEX=I_DETAIL_8)
      WHERE       d.hChkOrChg BETWEEN 200000000 AND 299999999
      AND       d.hInvOrRec BETWEEN 300000000 AND 399999999
    AND     d.hInvOrRec = t.hMy
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t.itype = 3
      GROUP BY d.hAcct, d.CashPost, d.hProp
      UNION ALL
      SELECT  t2.hOffsetAcct, d.CashPost, sum(-d.sAmount), 0, d.hProp
      FROM       TRANS t2, detail d (INDEX=I_DETAIL_8)
      WHERE       t2.hMy = d.hInvOrRec
      AND       d.hChkOrChg BETWEEN 200000000 AND 299999999
      AND       d.hInvOrRec BETWEEN 300000000 AND 399999999
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t2.itype = 3
      GROUP BY t2.hOffsetAcct, d.CashPost, d.hProp
      UNION ALL
      SELECT       isnull(d.hAccrualAcct,d.hAcct), d.CashPost, sum(d.sAmount), 1, d.hProp
      FROM       trans t, detail d (INDEX=I_DETAIL_8)
      WHERE       d.hChkOrChg BETWEEN 200000000 AND 299999999
      AND       d.hInvOrrec BETWEEN 300000000 AND 399999999
    AND     d.hInvOrRec = t.hMy
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t.itype = 3
      GROUP BY isnull(d.hAccrualAcct,d.hAcct), d.CashPost, d.hProp
      UNION ALL
      sELECT       t2.hOffsetAcct, d.CashPost, sum(-d.sAmount), 1, d.hProp
      FROM       TRANS t2, detail d (INDEX=I_DETAIL_8)
      WHERE       t2.hMy = d.hInvOrRec
      AND       d.hChkOrChg BETWEEN 200000000 AND 299999999
      AND       d.hInvOrrec BETWEEN 300000000 AND 399999999
      AND       d.hProp =  @p_hppty
      AND     d.CashPost >= @v_from_dt
    AND     t2.itype = 3
      GROUP BY t2.hOffsetAcct, d.CashPost, d.hProp
      UNION ALL
      SELECT      t1.hOffsetAcct, t1.uPostDate, sum(-t1.sTotalAmount), 1, t1.hProp
      FROM TRANS t1
      WHERE   t1.iType = 7
      AND     t1.hAccrualAcct is Not Null
      AND     t1.hProp = @p_hppty
      AND     t1.uPostDate >= @v_from_dt
      GROUP BY t1.hOffsetAcct, t1.uPostDate, t1.hProp
      UNION ALL
      SELECT      t1.hAccrualAcct, t1.uPostDate, sum(t1.sTotalAmount), 1, t1.hProp
      FROM TRANS t1
      WHERE   t1.iType = 7
      AND     t1.hAccrualAcct is Not Null
      AND     t1.hProp = @p_hppty
      AND     t1.uPostDate >= @v_from_dt
      GROUP BY t1.hAccrualAcct, t1.uPostDate, t1.hProp
      UNION ALL
      SELECT       d.hAcct, d.AccrualPost, sum(d.sAmount), 1, d.hProp
      FROM TRANS t, detail d (INDEX=I_DETAIL_9)
      WHERE   d.hAccrualAcct > 0
      AND     d.hInvOrRec BETWEEN 300000000 AND 399999999
    AND     d.hInvOrRec = t.hMy
      AND     d.hProp =  @p_hppty
      AND     d.AccrualPost >= @v_from_dt
    AND     t.itype = 3
      GROUP BY d.hAcct, d.AccrualPost, d.hProp
      UNION ALL
      SELECT t1.hAccrualAcct, d.AccrualPost, sum(-d.sAmount), 1, d.hProp
      FROM TRANS t1, detail d (INDEX=I_DETAIL_9)
      WHERE   t1.hMy = d.hInvOrRec
      AND     d.hAccrualAcct > 0
      AND     t1.iType = 3
      AND     d.hInvOrRec BETWEEN 300000000 AND 399999999
      AND     d.hProp =  @p_hppty
      AND     d.AccrualPost >= @v_from_dt
      GROUP BY t1.hAccrualAcct, d.AccrualPost, d.hProp
      UNION ALL
      SELECT d.hAcct, d.CashPost, sum(d.sAmount), case when t.iType2 = 1000 then 0 else iType2 end, d.hProp
      FROM detail d (INDEX=I_DETAIL_8), trans t
      WHERE d.hInvOrRec = t.hMy
      AND d.hInvOrRec BETWEEN 1000000000 AND 1099999999
      AND d.hProp =  @p_hppty
      AND d.CashPost >= @v_from_dt
    AND t.itype = 10
      GROUP BY d.hAcct, d.CashPost , d.hProp, case when t.iType2 = 1000 then 0 else  iType2 end
      UNION ALL
      SELECT d.hAcct, d.CashPost , sum(d.sAmount), 1, d.hProp
      FROM detail d (INDEX=I_DETAIL_8), trans t
      WHERE   d.hInvOrRec = t.hMy
      AND     t.iType2 = 1000
      AND     d.hInvOrRec BETWEEN 1000000000 AND 1099999999
      AND     d.hProp =  @p_hppty
      AND     d.CashPost  >= @v_from_dt
    AND     t.itype = 10
      GROUP BY d.hAcct, d.CashPost, d.hProp)


      /* Update the total table from the temporary table */

      UPDATE total
            set smtd =
            isnull((
            select SUM(amt)
                  from TEMP_TOT
                  where hppty = total.hppty
                  and hacct = total.hacct
                  and ibook = total.ibook
                  and umonth = total.umonth
            group by hppty, hacct, umonth, ibook
            ),0)
      where total.hppty = @p_hppty
      and total.umonth  >= @v_from_dt

      /* Insert into Total table , rows from temp table which didn't exist in the total
         table */

      INSERT INTO total
            (
            UMONTH,
            IBOOK,
            HACCT,
            HPPTY,
            SBEGIN,
            SMTD,
            SBEGINBUDGET,
            SBUDGET
            )
            (
            SELECT
                  UMONTH,
                  IBOOK,
                  HACCT,
                  HPPTY,
                  0,
                  isnull(SUM(AMT),0),
                  0,
                  0
            FROM TEMP_TOT S
            WHERE NOT EXISTS
                  (
                  SELECT 'X'
                   FROM total
                   WHERE HACCT = S.HACCT
                   AND UMONTH = S.UMONTH
                   AND HPPTY = S.HPPTY
                   AND IBOOK = S.IBOOK
                  )
            GROUP BY UMONTH, IBOOK, HACCT, HPPTY
            )

      /* Gets the maximum date for a property entry in the total table */
      /* select @v_max_date = max(uMonth) from total where hppty = @p_hppty */
      /* js 000414 - get max date from detail/trans, not total*/
      select @v_max_date_cash = max(CashPost) from detail where hProp = @p_hppty
      select @v_max_date_accrual = max(AccrualPost) from detail where hProp = @p_hppty

      if (isnull(@v_max_date_cash, '01-mar-50') >  isnull(@v_max_date_accrual, '01-mar-50') )
            set @v_max_date = isnull(@v_max_date_cash, @p_op_month)
      Else
            set @v_max_date = isnull(@v_max_date_accrual, @p_op_month)

      select @v_max_date_accrual = max(uPostDate) from trans where itype = 7 and hProp = @p_hppty
      if (isnull(@v_max_date_accrual, '01-mar-50') >  @v_max_date )
            set @v_max_date = @v_max_date_accrual

       /* js 000815 tr#22600 mp092101 move this above call to add recs */
        if @v_max_date < @p_op_month
                set @v_max_date = @p_op_month

      /* Creates rows for non existing umonths for a prop/acct/ibook */
      exec add_recs @p_hppty, @v_from_dt, @v_max_date, @v_acct


      /* Updates retain Earnings */

      update total set total.sMTD =
            isnull((select            isnull(total.sMTD,0) + isnull(sum(case when a.iAcctType = 1 then isnull(-t.sMTD,0) else isnull(t.sMTD,0) end),0)
                  from            total t (INDEX=PK_TOTAL), acct a
                  where            t.hPpty =  total.hPpty
                  and                  t.uMonth = total.uMonth
                  and                  total.iBook = t.iBook
                  and                  a.hMy = t.hAcct
                  and                  a.iRptType = 0),0)
      where            total.hPpty =  @p_hppty
      and                  total.hAcct = @v_acct
      and                  total.uMonth >= @v_from_dt

      /* Update Beginning Balances */

      begin

            /* Gets the month determining the end of year for a property */
            select @v_EndOfYear = iEndOfYear from property where hmy = @p_hppty


            /* Updating beginning balances for accounts non income/exp accounts carrying
               forward balances accross fiscal years */
            update total set sbegin =
            isnull((select       isnull(sum(isnull(t.sMTD, 0) ),0)
                  from             total t (INDEX=PK_TOTAL), acct a
                  where            t.hAcct = a.hMy
                  and             t.hPpty = total.hPpty
                  and             t.hAcct = total.hAcct
                  and                  t.iBook = total.iBook
                  and                  t.uMonth < total.uMonth
                  and                  a.iRptType = 1
            ),0)
            where            total.hPpty =  @p_hppty
            and                  total.uMonth between @v_from_dt and @p_op_month
            and             exists (select 'x' from acct where hmy = total.hacct and irpttype = 1)

            /* Updating beginning balances for accounts income/exp accounts carrying
               forward balances within the fiscal years and not accross */

            If (@v_EndOfYear = 12)
            begin

                  update total set sbegin =
                  isnull((select            sum(isnull(t.sMTD, 0) )
                        from            total t (INDEX=PK_TOTAL), acct a
                        where            t.hAcct = a.hMy
                        and             t.hPpty = total.hPpty
                        and             t.hAcct = total.hAcct
                        and                  t.iBook = total.iBook
                        and                  t.uMonth < total.uMonth
                        and                  a.iRptType != 1
                        and                  t.uMonth >= convert(datetime, '01/01/' + substring(convert(char(10), total.uMonth, 101), 7, 4), 101)
                  ),0)
                  where total.hPpty =  @p_hppty
                  and   uMonth >= @v_from_dt
                  AND uMonth <= @p_op_month --sw/mh 11-4/99
                  and exists (select 'x' from acct where hmy = total.hacct and irpttype != 1)

            end
            else
            begin

                  if (@v_EndOfYear < 10)
                  begin
                        select @vs_EndOfYear = '0' + convert(char(1), @v_EndOfYear)
                  end
                  else
                  begin
                        select @vs_EndOfYear = convert(char(2), @v_EndOfYear)
                  end

                  update total set sbegin =
                  isnull((select sum(isnull(t.sMTD, 0) )
                        from      total t (INDEX=PK_TOTAL), acct a
                        where      t.hAcct = a.hMy
                        and       t.hPpty = total.hPpty
                        and       t.hAcct = total.hAcct
                        and            t.iBook = total.iBook
                        and            t.uMonth < total.uMonth
                        and            a.iRptType != 1
                        and            t.uMonth >=
                              dateadd(mm, 1, convert(datetime, @vs_EndOfYear + '/01/' +
                              case
                                    when convert(numeric, substring(convert(char(10), total.uMonth, 101), 1, 2)) > @v_EndofYear
                                          then substring(convert(char(10), total.uMonth, 101), 7, 4)
                                    else
                                          convert(char(4), convert(numeric, substring(convert(char(10), total.uMonth, 101), 7, 4)) - 1)
                              end))
                  ),0)
                  where total.hPpty =  @p_hppty
                  and   total.uMonth between @v_from_dt and @p_op_month
                  and exists (select 'x' from acct where hmy = total.hacct and irpttype != 1)
            end
      end

/*      update total set
            sMTD = round(isnull(smtd,0), 2) ,
            sBegin = round(isnull(sBegin,0), 2)
      where
            hPpty =  @p_hppty
      and
            uMonth >= @v_from_dt

*/
      /* Deleting rows created in the temp table */
      delete from temp_tot

      return

END
GO


DROP PROCEDURE rebuild_totals
GO

/*
   Procedure :       Rebuild_totals
   Function  :       Rebuild the total table for a single/all properties given a date from which to
                        rebuild      and the operating month
   Date           :  02/03/99
   Modified  :  MarkPo 01/18/01 - Modified to work with MCA
*/
create procedure dbo.rebuild_totals(
   @p_hppty numeric,
   @p_from_dt char(10),
   @p_op_month char(10))
AS

      declare c1_rebuild_totals cursor for select hmy from property

      declare @v_hMy numeric(18,0)

      declare @d_from_dt datetime
      declare @d_op_month datetime

      declare @num char(18)
      declare @msg varchar(60)

BEGIN

      select @d_from_dt = convert(datetime, @p_from_dt, 101)
      select @d_op_month = convert(datetime, @p_op_month, 101)

      /* Calls rebuild_tot_per_prop for every /single hmy as per parameter */
      if (@p_hppty is Not null)
      begin
            begin tran
            exec rebuild_tot_per_prop @p_hppty, @d_from_dt, @d_op_month
            commit tran
      end
      else
      begin
            Open c1_rebuild_totals
            fetch next from c1_rebuild_totals into @v_hMy
            while @@fetch_status = 0
            begin
                  begin tran
                  exec rebuild_tot_per_prop @v_hMy, @p_from_dt, @p_op_month
                  commit tran
                  fetch next from c1_rebuild_totals into @v_hMy
            end
            close c1_rebuild_totals

      end

      deallocate c1_rebuild_totals

END
GO

//end sql

Any help would be appreciated.
-Mike
0
Comment
Question by:codemonkey21
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16766763
Return 3701 Cannot drop the procedure 'rebuild_tot_per_prop', because it does not exist or you do not have permission.
well; this error should be "clear". when you run this script the first time, the procedure does not exist yet and cannot be dropped hence.
you can ignore that one.

Return 2007 Cannot add rows to sysdepends for the current object because it depends on the missing object 'rebuild_tot_per_prop'. The object will still be created.
Although one could ignore this message most of the times, it might be important.
In your case it IS important, as the procedure referred to is created BEFORE it is used, hence this is a problem.

please change ALL the lines reading like this:
 exec rebuild_tot_per_prop ...

into this:
 exec dbo.rebuild_tot_per_prop ...

Hope this helps

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question