Loading ...
Sorry, an error occurred while loading the content.

SQL Server Express 2005 disappear triggers ??

Expand Messages
  • John Warner
    I m supporting a SQL Server Express 2005 database that is actually house on a rather stout server platform though not sure why in this case it would matter. On
    Message 1 of 19 , Jan 13, 2010
    • 0 Attachment
      I'm supporting a SQL Server Express 2005 database that is actually house
      on a rather stout server platform though not sure why in this case it
      would matter. On several tables in the primary DB there were triggers that
      would reach out to a Pervasive database, grab some data and do a few
      updates based on a particular field in each specific SQL table being
      inserted (a key field). OK the point about the middle of last week the
      triggers seem to have just disappeared. To everyone's knowledge there was
      not a drop trigger issued or such.

      Has anyone heard of something like this before and does anyone know how to
      prevent it assuming this is not user error (which I tend to think not)?

      Thanks and sorry for being a bit off topic but trying to research the
      problem. Also I have the code for the triggers that isn't the issue it is
      just them disappearing that is 'alarming'.

      John Warner
    • Damhuis Anton
      Hi John I have had a few situations like this before, and all but one time (as far as I can remember) was down to some *ghost* doing it. In the one situation
      Message 2 of 19 , Jan 14, 2010
      • 0 Attachment
        Hi John

        I have had a few situations like this before, and all but one time (as far as I can remember) was down to some *ghost* doing it.
        In the one situation it was a DB restore of a previous version which caused the triggers or stored procs to go missing.

        Hope you find the solution / cause

        Regards
        Anton Damhuis

        ________________________________
        Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


        [Non-text portions of this message have been removed]
      • p_livengood
        I have never encountered this, but if you want to track it down (or prevent it) you could create a DDL Trigger that would fire if the triggers were altered (or
        Message 3 of 19 , Jan 14, 2010
        • 0 Attachment
          I have never encountered this, but if you want to track it down (or prevent it) you could create a DDL Trigger that would fire if the triggers were altered (or deleted) and either send an email, or stop the alteration.

          HTH
          Paul

          --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton <DamhuisA@...> wrote:
          >
          > Hi John
          >
          > I have had a few situations like this before, and all but one time (as far as I can remember) was down to some *ghost* doing it.
          > In the one situation it was a DB restore of a previous version which caused the triggers or stored procs to go missing.
          >
          > Hope you find the solution / cause
          >
          > Regards
          > Anton Damhuis
          >
          > ________________________________
          > Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.
          >
          >
          > [Non-text portions of this message have been removed]
          >
        • John Warner
          Good thought, might not tell me everything but could sure alert me to something happening. Thanks Paul. (have you changed your email?) John Warner ... prevent
          Message 4 of 19 , Jan 14, 2010
          • 0 Attachment
            Good thought, might not tell me everything but could sure alert me to
            something happening. Thanks Paul.

            (have you changed your email?)

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of p_livengood
            > Sent: Thursday, January 14, 2010 1:00 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
            > triggers ??
            >
            > I have never encountered this, but if you want to track it down (or
            prevent it)
            > you could create a DDL Trigger that would fire if the triggers were
            altered (or
            > deleted) and either send an email, or stop the alteration.
            >
            > HTH
            > Paul
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton
            > <DamhuisA@...> wrote:
            > >
            > > Hi John
            > >
            > > I have had a few situations like this before, and all but one time (as
            far as I
            > can remember) was down to some *ghost* doing it.
            > > In the one situation it was a DB restore of a previous version which
            caused
            > the triggers or stored procs to go missing.
            > >
            > > Hope you find the solution / cause
            > >
            > > Regards
            > > Anton Damhuis
            > >
            > > ________________________________
            > > Please note: This email and its contents are subject to the disclaimer
            as
            > displayed at the following link:
            > http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you
            not
            > have web access, send an email to legalnotice@... and a copy of this
            > disclaimer will be sent to you.
            > >
            > >
            > > [Non-text portions of this message have been removed]
            > >
            >
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • Arnie Rowland
            John, Triggers don t just disappear . As suggested, either a backup prior to the trigger creation is being restored, or they are being removed. Removal would
            Message 5 of 19 , Jan 14, 2010
            • 0 Attachment
              John,

              Triggers don't 'just disappear'. As suggested, either a backup prior to
              the trigger creation is being restored, or they are being removed.

              Removal would occur if the tables are dropped and recreated -which isn't
              a far-fetched concept in some situations.

              Creating a DDL event to either rollback or just log table changes sould
              give you useful information.

              Regards,

              Arnie Rowland, MVP (SQL Server)

              "You cannot do a kindness too soon because you never know how soon it
              will be too late."
              -Ralph Waldo Emerson




              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              Sent: Thursday, January 14, 2010 10:28 AM
              To: Arnie
              Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
              triggers ??

              Good thought, might not tell me everything but could sure alert me to
              something happening. Thanks Paul.

              (have you changed your email?)

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of p_livengood
              > Sent: Thursday, January 14, 2010 1:00 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
              > triggers ??
              >
              > I have never encountered this, but if you want to track it down (or
              prevent it)
              > you could create a DDL Trigger that would fire if the triggers were
              altered (or
              > deleted) and either send an email, or stop the alteration.
              >
              > HTH
              > Paul
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton
              > <DamhuisA@...> wrote:
              > >
              > > Hi John
              > >
              > > I have had a few situations like this before, and all but one time
              (as
              far as I
              > can remember) was down to some *ghost* doing it.
              > > In the one situation it was a DB restore of a previous version which
              caused
              > the triggers or stored procs to go missing.
              > >
              > > Hope you find the solution / cause
              > >
              > > Regards
              > > Anton Damhuis
              > >
              > > ________________________________
              > > Please note: This email and its contents are subject to the
              disclaimer
              as
              > displayed at the following link:
              > http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you
              not
              > have web access, send an email to legalnotice@... and a copy of this
              > disclaimer will be sent to you.
              > >
              > >
              > > [Non-text portions of this message have been removed]
              > >
              >
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >



              ------------------------------------

              Yahoo! Groups Links







              __________ Information from ESET Smart Security, version of virus
              signature database 4772 (20100114) __________

              The message was checked by ESET Smart Security.

              http://www.eset.com



              __________ Information from ESET Smart Security, version of virus
              signature database 4772 (20100114) __________

              The message was checked by ESET Smart Security.

              http://www.eset.com



              Disclaimer - January 14, 2010
              This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
              This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
            • John Warner
              Arnie, how would an event/trigger to detect a trigger changing/being removed work? What I mean is, I really don t know how to begin coding something like this.
              Message 6 of 19 , Jan 14, 2010
              • 0 Attachment
                Arnie, how would an event/trigger to detect a trigger changing/being
                removed work? What I mean is, I really don't know how to begin coding
                something like this. Not asking you to do it as I learn nothing that way
                but what objects am I looking for, that sort of thing?

                Thanks to both you and Paul.

                John Warner


                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                > Sent: Thursday, January 14, 2010 1:43 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                > triggers ??
                >
                > John,
                >
                > Triggers don't 'just disappear'. As suggested, either a backup prior to
                > the trigger creation is being restored, or they are being removed.
                >
                > Removal would occur if the tables are dropped and recreated -which isn't
                > a far-fetched concept in some situations.
                >
                > Creating a DDL event to either rollback or just log table changes sould
                > give you useful information.
                >
                > Regards,
                >
                > Arnie Rowland, MVP (SQL Server)
                >
                > "You cannot do a kindness too soon because you never know how soon it
                > will be too late."
                > -Ralph Waldo Emerson
                >
                >
                >
                >
                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                > Sent: Thursday, January 14, 2010 10:28 AM
                > To: Arnie
                > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                > triggers ??
                >
                > Good thought, might not tell me everything but could sure alert me to
                > something happening. Thanks Paul.
                >
                > (have you changed your email?)
                >
                > John Warner
                >
                >
                > > -----Original Message-----
                > > From: SQLQueriesNoCode@yahoogroups.com
                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of p_livengood
                > > Sent: Thursday, January 14, 2010 1:00 PM
                > > To: SQLQueriesNoCode@yahoogroups.com
                > > Subject: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                > > triggers ??
                > >
                > > I have never encountered this, but if you want to track it down (or
                > prevent it)
                > > you could create a DDL Trigger that would fire if the triggers were
                > altered (or
                > > deleted) and either send an email, or stop the alteration.
                > >
                > > HTH
                > > Paul
                > >
                > > --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton
                > > <DamhuisA@...> wrote:
                > > >
                > > > Hi John
                > > >
                > > > I have had a few situations like this before, and all but one time
                > (as
                > far as I
                > > can remember) was down to some *ghost* doing it.
                > > > In the one situation it was a DB restore of a previous version which
                > caused
                > > the triggers or stored procs to go missing.
                > > >
                > > > Hope you find the solution / cause
                > > >
                > > > Regards
                > > > Anton Damhuis
                > > >
                > > > ________________________________
                > > > Please note: This email and its contents are subject to the
                > disclaimer
                > as
                > > displayed at the following link:
                > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should you
                > not
                > > have web access, send an email to legalnotice@... and a copy of this
                > > disclaimer will be sent to you.
                > > >
                > > >
                > > > [Non-text portions of this message have been removed]
                > > >
                > >
                > >
                > >
                > >
                > > ------------------------------------
                > >
                > > Yahoo! Groups Links
                > >
                > >
                > >
                >
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
                >
                >
                >
                >
                > __________ Information from ESET Smart Security, version of virus
                > signature database 4772 (20100114) __________
                >
                > The message was checked by ESET Smart Security.
                >
                > http://www.eset.com
                >
                >
                >
                > __________ Information from ESET Smart Security, version of virus
                > signature database 4772 (20100114) __________
                >
                > The message was checked by ESET Smart Security.
                >
                > http://www.eset.com
                >
                >
                >
                > Disclaimer - January 14, 2010
                > This email and any files transmitted with it are confidential and
                intended solely
                > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                > addressee you should not disseminate, distribute, copy or alter this
                email. Any
                > views or opinions presented in this email are solely those of the author
                and
                > might not represent those of Westwood Consulting, Inc. Warning: Although
                > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                > viruses are present in this email, the company cannot accept
                responsibility for
                > any loss or damage arising from the use of this email or attachments.
                > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
              • Arnie Rowland
                John, It would be something like this: (verify with BOL) CREATE TRIGGER tr_TriggerChange ON DATABASE FOR DROP_TRIGGER, ALTER_TRIGGER AS BEGIN ... END Regards,
                Message 7 of 19 , Jan 14, 2010
                • 0 Attachment
                  John,

                  It would be something like this: (verify with BOL)

                  CREATE TRIGGER tr_TriggerChange
                  ON DATABASE
                  FOR DROP_TRIGGER, ALTER_TRIGGER
                  AS
                  BEGIN
                  --- here you could log all actions,
                  --- or check the EVENTDATA to verify the specific table involved,
                  --- then log,
                  --- or just ROLLBACK to prevent the change.
                  --- If ROLLBACK, then this trigger would have to be disabled
                  --- in order to made any other trigger changes.
                  END


                  Regards,

                  Arnie Rowland, MVP (SQL Server)

                  "You cannot do a kindness too soon because you never know how soon it
                  will be too late."
                  -Ralph Waldo Emerson




                  -----Original Message-----
                  From: SQLQueriesNoCode@yahoogroups.com
                  [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  Sent: Thursday, January 14, 2010 10:51 AM
                  To: Arnie
                  Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                  triggers ??

                  Arnie, how would an event/trigger to detect a trigger changing/being
                  removed work? What I mean is, I really don't know how to begin coding
                  something like this. Not asking you to do it as I learn nothing that way
                  but what objects am I looking for, that sort of thing?

                  Thanks to both you and Paul.

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                  > Sent: Thursday, January 14, 2010 1:43 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                  > triggers ??
                  >
                  > John,
                  >
                  > Triggers don't 'just disappear'. As suggested, either a backup prior
                  to
                  > the trigger creation is being restored, or they are being removed.
                  >
                  > Removal would occur if the tables are dropped and recreated -which
                  isn't
                  > a far-fetched concept in some situations.
                  >
                  > Creating a DDL event to either rollback or just log table changes
                  sould
                  > give you useful information.
                  >
                  > Regards,
                  >
                  > Arnie Rowland, MVP (SQL Server)
                  >
                  > "You cannot do a kindness too soon because you never know how soon it
                  > will be too late."
                  > -Ralph Waldo Emerson
                  >
                  >
                  >
                  >
                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  > Sent: Thursday, January 14, 2010 10:28 AM
                  > To: Arnie
                  > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                  > triggers ??
                  >
                  > Good thought, might not tell me everything but could sure alert me to
                  > something happening. Thanks Paul.
                  >
                  > (have you changed your email?)
                  >
                  > John Warner
                  >
                  >
                  > > -----Original Message-----
                  > > From: SQLQueriesNoCode@yahoogroups.com
                  > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of p_livengood
                  > > Sent: Thursday, January 14, 2010 1:00 PM
                  > > To: SQLQueriesNoCode@yahoogroups.com
                  > > Subject: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                  > > triggers ??
                  > >
                  > > I have never encountered this, but if you want to track it down (or
                  > prevent it)
                  > > you could create a DDL Trigger that would fire if the triggers were
                  > altered (or
                  > > deleted) and either send an email, or stop the alteration.
                  > >
                  > > HTH
                  > > Paul
                  > >
                  > > --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton
                  > > <DamhuisA@...> wrote:
                  > > >
                  > > > Hi John
                  > > >
                  > > > I have had a few situations like this before, and all but one time
                  > (as
                  > far as I
                  > > can remember) was down to some *ghost* doing it.
                  > > > In the one situation it was a DB restore of a previous version
                  which
                  > caused
                  > > the triggers or stored procs to go missing.
                  > > >
                  > > > Hope you find the solution / cause
                  > > >
                  > > > Regards
                  > > > Anton Damhuis
                  > > >
                  > > > ________________________________
                  > > > Please note: This email and its contents are subject to the
                  > disclaimer
                  > as
                  > > displayed at the following link:
                  > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should
                  you
                  > not
                  > > have web access, send an email to legalnotice@... and a copy of this
                  > > disclaimer will be sent to you.
                  > > >
                  > > >
                  > > > [Non-text portions of this message have been removed]
                  > > >
                  > >
                  > >
                  > >
                  > >
                  > > ------------------------------------
                  > >
                  > > Yahoo! Groups Links
                  > >
                  > >
                  > >
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > __________ Information from ESET Smart Security, version of virus
                  > signature database 4772 (20100114) __________
                  >
                  > The message was checked by ESET Smart Security.
                  >
                  > http://www.eset.com
                  >
                  >
                  >
                  > __________ Information from ESET Smart Security, version of virus
                  > signature database 4772 (20100114) __________
                  >
                  > The message was checked by ESET Smart Security.
                  >
                  > http://www.eset.com
                  >
                  >
                  >
                  > Disclaimer - January 14, 2010
                  > This email and any files transmitted with it are confidential and
                  intended solely
                  > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                  > addressee you should not disseminate, distribute, copy or alter this
                  email. Any
                  > views or opinions presented in this email are solely those of the
                  author
                  and
                  > might not represent those of Westwood Consulting, Inc. Warning:
                  Although
                  > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                  > viruses are present in this email, the company cannot accept
                  responsibility for
                  > any loss or damage arising from the use of this email or attachments.
                  > This disclaimer was added by Policy Patrol:
                  http://www.policypatrol.com/
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >



                  ------------------------------------

                  Yahoo! Groups Links







                  __________ Information from ESET Smart Security, version of virus
                  signature database 4772 (20100114) __________

                  The message was checked by ESET Smart Security.

                  http://www.eset.com



                  __________ Information from ESET Smart Security, version of virus
                  signature database 4772 (20100114) __________

                  The message was checked by ESET Smart Security.

                  http://www.eset.com
                • John Warner
                  Thanks Arnie! John Warner
                  Message 8 of 19 , Jan 15, 2010
                  • 0 Attachment
                    Thanks Arnie!

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                    > Sent: Thursday, January 14, 2010 2:43 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                    > triggers ??
                    >
                    > John,
                    >
                    > It would be something like this: (verify with BOL)
                    >
                    > CREATE TRIGGER tr_TriggerChange
                    > ON DATABASE
                    > FOR DROP_TRIGGER, ALTER_TRIGGER
                    > AS
                    > BEGIN
                    > --- here you could log all actions,
                    > --- or check the EVENTDATA to verify the specific table involved,
                    > --- then log,
                    > --- or just ROLLBACK to prevent the change.
                    > --- If ROLLBACK, then this trigger would have to be disabled
                    > --- in order to made any other trigger changes.
                    > END
                    >
                    >
                    > Regards,
                    >
                    > Arnie Rowland, MVP (SQL Server)
                    >
                    > "You cannot do a kindness too soon because you never know how soon it
                    > will be too late."
                    > -Ralph Waldo Emerson
                    >
                    >
                    >
                    >
                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    > Sent: Thursday, January 14, 2010 10:51 AM
                    > To: Arnie
                    > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                    > triggers ??
                    >
                    > Arnie, how would an event/trigger to detect a trigger changing/being
                    > removed work? What I mean is, I really don't know how to begin coding
                    > something like this. Not asking you to do it as I learn nothing that way
                    > but what objects am I looking for, that sort of thing?
                    >
                    > Thanks to both you and Paul.
                    >
                    > John Warner
                    >
                    >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
                    > Rowland
                    > > Sent: Thursday, January 14, 2010 1:43 PM
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                    > > triggers ??
                    > >
                    > > John,
                    > >
                    > > Triggers don't 'just disappear'. As suggested, either a backup prior
                    > to
                    > > the trigger creation is being restored, or they are being removed.
                    > >
                    > > Removal would occur if the tables are dropped and recreated -which
                    > isn't
                    > > a far-fetched concept in some situations.
                    > >
                    > > Creating a DDL event to either rollback or just log table changes
                    > sould
                    > > give you useful information.
                    > >
                    > > Regards,
                    > >
                    > > Arnie Rowland, MVP (SQL Server)
                    > >
                    > > "You cannot do a kindness too soon because you never know how soon it
                    > > will be too late."
                    > > -Ralph Waldo Emerson
                    > >
                    > >
                    > >
                    > >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    > > Sent: Thursday, January 14, 2010 10:28 AM
                    > > To: Arnie
                    > > Subject: RE: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                    > > triggers ??
                    > >
                    > > Good thought, might not tell me everything but could sure alert me to
                    > > something happening. Thanks Paul.
                    > >
                    > > (have you changed your email?)
                    > >
                    > > John Warner
                    > >
                    > >
                    > > > -----Original Message-----
                    > > > From: SQLQueriesNoCode@yahoogroups.com
                    > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                    > p_livengood
                    > > > Sent: Thursday, January 14, 2010 1:00 PM
                    > > > To: SQLQueriesNoCode@yahoogroups.com
                    > > > Subject: [SQLQueriesNoCode] Re:SQL Server Express 2005 disappear
                    > > > triggers ??
                    > > >
                    > > > I have never encountered this, but if you want to track it down (or
                    > > prevent it)
                    > > > you could create a DDL Trigger that would fire if the triggers were
                    > > altered (or
                    > > > deleted) and either send an email, or stop the alteration.
                    > > >
                    > > > HTH
                    > > > Paul
                    > > >
                    > > > --- In SQLQueriesNoCode@yahoogroups.com, Damhuis Anton
                    > > > <DamhuisA@...> wrote:
                    > > > >
                    > > > > Hi John
                    > > > >
                    > > > > I have had a few situations like this before, and all but one time
                    > > (as
                    > > far as I
                    > > > can remember) was down to some *ghost* doing it.
                    > > > > In the one situation it was a DB restore of a previous version
                    > which
                    > > caused
                    > > > the triggers or stored procs to go missing.
                    > > > >
                    > > > > Hope you find the solution / cause
                    > > > >
                    > > > > Regards
                    > > > > Anton Damhuis
                    > > > >
                    > > > > ________________________________
                    > > > > Please note: This email and its contents are subject to the
                    > > disclaimer
                    > > as
                    > > > displayed at the following link:
                    > > > http://www.investmentsolutions.co.za/EmailLegalNotice.htm. Should
                    > you
                    > > not
                    > > > have web access, send an email to legalnotice@... and a copy of this
                    > > > disclaimer will be sent to you.
                    > > > >
                    > > > >
                    > > > > [Non-text portions of this message have been removed]
                    > > > >
                    > > >
                    > > >
                    > > >
                    > > >
                    > > > ------------------------------------
                    > > >
                    > > > Yahoo! Groups Links
                    > > >
                    > > >
                    > > >
                    > >
                    > >
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > >
                    > > __________ Information from ESET Smart Security, version of virus
                    > > signature database 4772 (20100114) __________
                    > >
                    > > The message was checked by ESET Smart Security.
                    > >
                    > > http://www.eset.com
                    > >
                    > >
                    > >
                    > > __________ Information from ESET Smart Security, version of virus
                    > > signature database 4772 (20100114) __________
                    > >
                    > > The message was checked by ESET Smart Security.
                    > >
                    > > http://www.eset.com
                    > >
                    > >
                    > >
                    > > Disclaimer - January 14, 2010
                    > > This email and any files transmitted with it are confidential and
                    > intended solely
                    > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                    > > addressee you should not disseminate, distribute, copy or alter this
                    > email. Any
                    > > views or opinions presented in this email are solely those of the
                    > author
                    > and
                    > > might not represent those of Westwood Consulting, Inc. Warning:
                    > Although
                    > > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                    > > viruses are present in this email, the company cannot accept
                    > responsibility for
                    > > any loss or damage arising from the use of this email or attachments.
                    > > This disclaimer was added by Policy Patrol:
                    > http://www.policypatrol.com/
                    > >
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > __________ Information from ESET Smart Security, version of virus
                    > signature database 4772 (20100114) __________
                    >
                    > The message was checked by ESET Smart Security.
                    >
                    > http://www.eset.com
                    >
                    >
                    >
                    > __________ Information from ESET Smart Security, version of virus
                    > signature database 4772 (20100114) __________
                    >
                    > The message was checked by ESET Smart Security.
                    >
                    > http://www.eset.com
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                  • Prasanna Dhakephalkar
                    Hi,   we use varible query inside with clause ? Like   WITH tim AS ( select abc from || Input_table_name || a where num 5 ) select abc from tim;  
                    Message 9 of 19 , Jan 22, 2010
                    • 0 Attachment
                      Hi,
                       
                      we use varible query inside with clause ?
                      Like
                       
                      WITH tim AS
                      ( ' select abc from ' || Input_table_name || ' a where num > 5')
                      select abc from tim;
                       
                      This is part of a strored procedure input_table_name is a varchar2 input value to the procedure.
                       
                       
                      I have variable query that gets used 3 times as a sub query,
                      I thought with clause can help me.
                       
                      As such it is not allowing me.
                       
                      Any help is welcome.
                       
                      Thanks and Regards,
                       
                      Prasanna.




                      [Non-text portions of this message have been removed]
                    • Arnie Rowland
                      You cannot use dynamic SQL inside a common table expression (cte) as you are attempting. You might be able to make the entire CTE dynamic SQL -but I think
                      Message 10 of 19 , Jan 22, 2010
                      • 0 Attachment
                        You cannot use dynamic SQL inside a common table expression (cte) as you are attempting.

                        You 'might' be able to make the entire CTE dynamic SQL -but I think you would find that to be very cumbersome and perhaps ineffective for the task.


                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "You cannot do a kindness too soon because you never know how soon it will be too late."
                        -Ralph Waldo Emerson



                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Prasanna Dhakephalkar
                        Sent: Friday, January 22, 2010 6:25 AM
                        To: Arnie
                        Subject: [SQLQueriesNoCode] With Clause

                        Hi,

                        we use varible query inside with clause ?
                        Like

                        WITH tim AS
                        ( ' select abc from ' || Input_table_name || ' a where num > 5')
                        select abc from tim;

                        This is part of a strored procedure input_table_name is a varchar2 input value to the procedure.


                        I have variable query that gets used 3 times as a sub query,
                        I thought with clause can help me.

                        As such it is not allowing me.

                        Any help is welcome.

                        Thanks and Regards,

                        Prasanna.




                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links







                        __________ Information from ESET Smart Security, version of virus signature database 4797 (20100122) __________

                        The message was checked by ESET Smart Security.

                        http://www.eset.com



                        __________ Information from ESET Smart Security, version of virus signature database 4797 (20100122) __________

                        The message was checked by ESET Smart Security.

                        http://www.eset.com



                        Disclaimer - January 22, 2010
                        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                      Your message has been successfully submitted and would be delivered to recipients shortly.