0 Replies Latest reply: Aug 14, 2007 10:15 AM by 592970 RSS

    Error in Converting mssql server 2K procedures/triggers to Oracle 10.2.0.2

    592970
      I have tried to convert but most all procedures and triggers fail
      Here is the ms sql procedure and a trigger :

      Can someone help please?

      ---- ms sql server 2000 procedure

      Create procedure dbo.[sp_sel_04E7D882BE41457F6C8112E546A647BA_pal]
      (@type int output, @rowguid uniqueidentifier) AS
      declare @retcode int

      set nocount on

           -- role based security check
           if ({ fn ISPALUSER('6C8112E5-46A6-47BA-9089-881597C4D532') } <> 1)
           begin
                RAISERROR (14126, 11, -1)
                return (1)
           end
      if @type = 1
      begin
      select t.* from [dbo].[MITSTagPrinter] t where rowguidcol = @rowguid
      if @@ERROR<>0 return(1)
      end

      else if @type < 4
      begin
      select c.tablenick, c.rowguid, c.generation, c.lineage, c.colv1
      , t.* from [dbo].[MITSTagPrinter] t, #cont c where
      t.rowguidcol = c.rowguid
      order by t.rowguidcol
      if @@ERROR<>0
      return(1)
      end
      else if @type = 4
      begin
      set @type = 0
      if exists (select * from [dbo].[MITSTagPrinter] where rowguidcol = @rowguid)
      set @type = 3
      if @@ERROR<>0 return(1)
      end

      else if @type = 5
      begin
      delete [dbo].[MITSTagPrinter] where rowguidcol = @rowguid
      if @@ERROR<>0 return(1)
      end

      else if @type = 6 -- sp_MSenumcolumns
      begin
      select t.* from [dbo].[MITSTagPrinter] t where 1=2
      if @@ERROR<>0 return(1)
      end

      else if @type = 7 -- sp_MSlocktable
      begin
      select 1 from [dbo].[MITSTagPrinter](tablock holdlock) where 1 = 2
      if @@ERROR<>0 return(1)
      end

      else if @type = 8 -- put update lock
      begin
      if not exists (select * from [dbo].[MITSTagPrinter](UPDLOCK HOLDLOCK) where rowguidcol = @rowguid)
      begin
      RAISERROR(20031 , 16, -1)
      return(1)
      end
      end

      return(0)

      --------- ms sql server trigger ------------
      create trigger del_04E7D882BE41457FBAC2789DFC0CA86C on [dbo].[MITSTagPrinter] FOR DELETE AS
           if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1
                return
           /* Declare variables */
           declare @tablenick int, @retcode smallint, @reason nvarchar(255), @nickname int, @lineage varbinary(255), @oldmaxversion int
           
           set nocount on
           select @tablenick = 2052001
           select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
           
           select @reason = 'user delete'
           execute dbo.sp_MSgetreplnick @nickname = @nickname output
           if (@@error <> 0)
                begin
                goto FAILURE
                end
           
           set @lineage = { fn UPDATELINEAGE(0x0, @nickname, @oldmaxversion) }

           insert into tsvw_04E7D882BE41457FBAC2789DFC0CA86C (rowguid, tablenick, type, lineage, generation, reason)
                select rowguidcol, @tablenick, 1, { fn UPDATELINEAGE(COALESCE(c.lineage, @lineage), @nickname, @oldmaxversion+1) }, a.gen_cur, @reason from
                     deleted d left outer join ctsv_04E7D882BE41457FBAC2789DFC0CA86C c on c.tablenick = @tablenick and c.rowguid = d.rowguidcol
                     join (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as a on a.nickname = @tablenick
           if @@error <> 0
                GOTO FAILURE

           delete from ctsv_04E7D882BE41457FBAC2789DFC0CA86C where tablenick = @tablenick and rowguid in
                (select rowguidcol from deleted)
           if @@error <> 0
                GOTO FAILURE

           
           return
      FAILURE:
                          if @@trancount > 0
                               rollback tran
                          raiserror (20041, 16, -1)
                          return