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

RE: [SQLQueriesNoCode] Delete with Join-Urgent

Expand Messages
  • John Warner
    Thanks for the tip, I for one was unaware of that. John Warner ... From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf
    Message 1 of 21 , Mar 1, 2006
    • 0 Attachment
      Message
      Thanks for the tip, I for one was unaware of that.
       
       
      John Warner
      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Max Parmenter
      Sent: Tuesday, February 28, 2006 7:29 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent

      There is a parameter in MS SQL Query Analyser that allows you to wrap everything in a transaction by default (like default Oracle rollback/commit behavior).
       
      To do this, click 'Tools' -> 'Options' -> 'Connection Properties' and then tick the Set implicit_transactions checkbox. Voila!, default Oracle functionality just as it should be :) It's saved my bacon too on a couple of occasions!
       
      All the best,
       
      Max
      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
      Sent: 28 February 2006 14:18
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent

      Wrap them in a Transaction, but I'm not sure if you can view the result set before the commit. I think Row count is available though, before a commit or roll back is called for.
       
       
      John Warner
      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael.Gerholdt@...
      Sent: Tuesday, February 28, 2006 8:58 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent

      Thanks, John – I don’t quite know enough to teach and my job isn’t high-paying enough to give up yet J

       

      You are right, of course … in practice I always do exactly as you describe. Select first and look-see.

       

      In my Oracle environment we can have ‘dirty reads’ in a session – run delete statements but leave them uncommitted in the rollback segment, and any select statements within that session will reflect the results of the delete. Any other session doing the same selects would not see any change in the data. It is a useful tool and has also saved my bacon a couple of times …

       

      If not happy, roll back, if happy, commit … I know this is typical database function but I haven’t seen how in EM or QA or MS SQL Studio Express (I’m migrating myself slowly and am impressed with it) how to run deletes but not commit immediately. Probably there’s a way … anyone know?

       


      From:SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com ] On Behalf Of John Warner
      Sent: Tuesday, February 28, 2006 8:49 AM
      To:SQLQueriesNoCode@yahoogroups.com
      Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent

       

      Great explanation Michael, if you aren't in front of a class room you missed your calling. One change I would make to your approach, in the last query where you do the delete, I would first run a select again just to make sure I was happy and then edit the SELECT keyword to DELETE. I just can't stand deleting until I've seen for sure what I am deleting. Note though, your DELETE gets the correct records so far as I can tell based on what we know. I'm just always a bit cautious before 'attacking' data. But give up that high paying DBA job and get in a classroom <wink/>.

       

      John Warner

    • John Warner
      TOAD for SQL Server might be worth a look. (same people that do Toad for Oracle) John Warner ... From: Gerholdt, Michael
      Message 2 of 21 , Mar 1, 2006
      • 0 Attachment
        TOAD for SQL Server might be worth a look. (same people that do Toad for
        Oracle)


        John Warner

        -----Original Message-----
        From: Gerholdt, Michael [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf
        Of Michael.Gerholdt@...
        Sent: Tuesday, February 28, 2006 8:10 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent


        Beautiful, thanks. I figured there must be a way.

        For Oracle I use PL/SQL Developer from AllAround Automations. It has some
        nice intellisense auto-complete features similar to Visual Studio. Give a
        table an alias, and type the alias and a dot and there's a drop-down of all
        the column names, for instance.

        Now tell me we can make QA or MSSE do that ...

        _____

        From: SQLQueriesNoCode@yahoogroups.com on behalf of Max Parmenter
        Sent: Tue 2/28/2006 7:28 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent


        There is a parameter in MS SQL Query Analyser that allows you to wrap
        everything in a transaction by default (like default Oracle rollback/commit
        behavior).

        To do this, click 'Tools' -> 'Options' -> 'Connection Properties' and then
        tick the Set implicit_transactions checkbox. Voila!, default Oracle
        functionality just as it should be :) It's saved my bacon too on a couple of
        occasions!

        All the best,

        Max
      • Max Parmenter
        A pleasure :)
        Message 3 of 21 , Mar 1, 2006
        • 0 Attachment
          A pleasure :)

          > Thanks for the tip, I for one was unaware of that.
          >
          >
          > John Warner
          >
          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Max Parmenter
          > Sent: Tuesday, February 28, 2006 7:29 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent
          >
          >
          > There is a parameter in MS SQL Query Analyser that allows you to wrap
          > everything in a transaction by default (like default Oracle
          > rollback/commit behavior).
          >
          > To do this, click 'Tools' -> 'Options' -> 'Connection Properties' and
          > then tick the Set implicit_transactions checkbox. Voila!, default Oracle
          > functionality just as it should be :) It's saved my bacon too on a
          > couple of occasions!
          >
          > All the best,
          >
          > Max
          >
          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          > Sent: 28 February 2006 14:18
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent
          >
          >
          > Wrap them in a Transaction, but I'm not sure if you can view the result
          > set before the commit. I think Row count is available though, before a
          > commit or roll back is called for.
          >
          >
          > John Warner
          >
          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
          > Michael.Gerholdt@...
          > Sent: Tuesday, February 28, 2006 8:58 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent
          >
          >
          >
          > Thanks, John - I don't quite know enough to teach and my job isn't
          > high-paying enough to give up yet :-)
          >
          >
          >
          > You are right, of course . in practice I always do exactly as you
          > describe. Select first and look-see.
          >
          >
          >
          > In my Oracle environment we can have 'dirty reads' in a session - run
          > delete statements but leave them uncommitted in the rollback segment,
          > and any select statements within that session will reflect the results
          > of the delete. Any other session doing the same selects would not see
          > any change in the data. It is a useful tool and has also saved my bacon
          > a couple of times .
          >
          >
          >
          > If not happy, roll back, if happy, commit . I know this is typical
          > database function but I haven't seen how in EM or QA or MS SQL Studio
          > Express (I'm migrating myself slowly and am impressed with it) how to
          > run deletes but not commit immediately. Probably there's a way . anyone
          > know?
          >
          >
          >
          >
          > _____
          >
          >
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          > Sent: Tuesday, February 28, 2006 8:49 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Delete with Join-Urgent
          >
          >
          >
          > Great explanation Michael, if you aren't in front of a class room you
          > missed your calling. One change I would make to your approach, in the
          > last query where you do the delete, I would first run a select again
          > just to make sure I was happy and then edit the SELECT keyword to
          > DELETE. I just can't stand deleting until I've seen for sure what I am
          > deleting. Note though, your DELETE gets the correct records so far as I
          > can tell based on what we know. I'm just always a bit cautious before
          > 'attacking' data. But give up that high paying DBA job and get in a
          > classroom <wink/>.
          >
          >
          >
          > John Warner
          >
          >
          > _____
          >
          > YAHOO! GROUPS LINKS
          >
          >
          >
          > * Visit your group "SQLQueriesNoCode
          > <http://groups.yahoo.com/group/SQLQueriesNoCode> " on the web.
          >
          >
          > * To unsubscribe from this group, send an email to:
          > SQLQueriesNoCode-unsubscribe@yahoogroups.com
          > <mailto:SQLQueriesNoCode-unsubscribe@yahoogroups.com?subject=Unsubscribe
          >>
          >
          >
          > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          > Service <http://docs.yahoo.com/info/terms/> .
          >
          >
          > _____
          >
          >
          >
        • John Warner
          I have a database with relations defined, tables, columns, constraints, and even a few rows of data in some tables. Rather then export the data file and import
          Message 4 of 21 , Mar 1, 2006
          • 0 Attachment
            I have a database with relations defined, tables, columns, constraints, and
            even a few rows of data in some tables. Rather then export the data file and
            import it at the next location, I would like to have SQL Server create a
            script of the DDL to recreate the database. The idea being the user could
            just run the script in QA or Enterprise manager and presto the database has
            been created on their Server. What I think you end up with is a filename.sql
            file that can be edited with a text editor if required.

            I know this ability exists, but darned if I am asking Books Online the right
            question. Can someone on the list point me in the correct direction?

            Thanks (oh, if it matters SQL Server 2K)

            John Warner
          • Michael.Gerholdt@fredonia.edu
            Enterprise Manager Drill down to the database in question, view tables in right pane Select the tables in question. Right click for a context menu, All Tasks
            Message 5 of 21 , Mar 1, 2006
            • 0 Attachment
              Message

              Enterprise Manager

               

              Drill down to the database in question, view tables in right pane

              Select the tables in question.

               

              Right click for a context menu, All Tasks à Generate SQL script …

               

              Take a look at all the options – you can do it all, with constraints, without constraints, with data, without data, with drop if exist statements, without them, etc.

               

              Same is true for stored procedures.

               

               


              From: SQLQueriesNoCode@yahoogroups.com [mailto: SQLQueriesNoCode@yahoogroups.com ] On Behalf Of John Warner
              Sent: Wednesday, March 01, 2006 8:12 AM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: [SQLQueriesNoCode] Create a script to reproduce a database in SQL Server

               

              I have a database with relations defined, tables, columns, constraints, and even a few rows of data in some tables. Rather then export the data file and import it at the next location, I would like to have SQL Server create a script of the DDL to recreate the database. The idea being the user could just run the script in QA or Enterprise manager and presto the database has been created on their Server. What I think you end up with is a filename.sql file that can be edited with a text editor if required.

               

              I know this ability exists, but darned if I am asking Books Online the right question. Can someone on the list point me in the correct direction?

               

              Thanks (oh, if it matters SQL Server 2K)

               

              John Warner

            • John Warner
              Thank you, there are none so blind as those to dumb to see. John Warner ... From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On
              Message 6 of 21 , Mar 1, 2006
              • 0 Attachment
                Message
                Thank you, there are none so blind as those to dumb to see.
                 
                 
                John Warner
                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael.Gerholdt@...
                Sent: Wednesday, March 01, 2006 8:35 AM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: RE: [SQLQueriesNoCode] Create a script to reproduce a database in SQL Server

                Enterprise Manager

                 

                Drill down to the database in question, view tables in right pane

                Select the tables in question.

                 

                Right click for a context menu, All Tasks à Generate SQL script …

                 

                Take a look at all the options – you can do it all, with constraints, without constraints, with data, without data, with drop if exist statements, without them, etc.

                 

                Same is true for stored procedures.

                 

                 


                From: SQLQueriesNoCode@yahoogroups.com [mailto: SQLQueriesNoCode@yahoogroups.com ] On Behalf Of John Warner
                Sent: Wednesday, March 01, 2006 8:12 AM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] Create a script to reproduce a database in SQL Server

                 

                I have a database with relations defined, tables, columns, constraints, and even a few rows of data in some tables. Rather then export the data file and import it at the next location, I would like to have SQL Server create a script of the DDL to recreate the database. The idea being the user could just run the script in QA or Enterprise manager and presto the database has been created on their Server. What I think you end up with is a filename.sql file that can be edited with a text editor if required.

                 

                I know this ability exists, but darned if I am asking Books Online the right question. Can someone on the list point me in the correct direction?

                 

                Thanks (oh, if it matters SQL Server 2K)

                 

                John Warner

              Your message has been successfully submitted and would be delivered to recipients shortly.