locking an SQL Server DB with .NET

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • DraguVaso
    #1

    locking an SQL Server DB with .NET

    Hi,

    My VB.NET-application has to do some actions on a SQL Server Database, but
    during these actions the DataBase may not be used by any other application.
    the total time of the lock could be up to 15 minutes. How can I perform
    something like a lock, who denies other users/applications to access the
    Database, and after a while grant themagain permission.

    Thanks,

    Pieter


    Tags: None
    • David Lloyd
      #2
      Re: locking an SQL Server DB with .NET

      Pieter:

      Transact SQL has a number of locking hints that can be used in queries to
      provide the appropriate level of database locking. I would recommend
      browsing SQL Server Books On Line for more information on locking hints, as
      well as other ways to lock a database.

      --
      David Lloyd
      MCSD .NET
      Managed I.T. Services & I.T. Consulting | Lemington Consulting
      http://LemingtonConsulting.com
      Award-winning Managed I.T. Services, I.T. Consulting, cloud solutions, database hosting, and A.I. software development for organizations nationwide.


      This response is supplied "as is" without any representations or warranties.


      "DraguVaso" <pietercoucke@h otmail.com> wrote in message
      news:#QnccLIVFH A.2664@TK2MSFTN GP15.phx.gbl...
      Hi,

      My VB.NET-application has to do some actions on a SQL Server Database, but
      during these actions the DataBase may not be used by any other application.
      the total time of the lock could be up to 15 minutes. How can I perform
      something like a lock, who denies other users/applications to access the
      Database, and after a while grant themagain permission.

      Thanks,

      Pieter



        Comment

        • Alejandro Mesa
          #3
          RE: locking an SQL Server DB with .NET

          See "ALTER DATABASE" in BOL. You can use this statement to set the database
          status in SINGLE_USER, do your stuff and set it back to MULTI_USER.

          Example:

          use northwind
          go

          alter database northwind
          set SINGLE_USER with ROLLBACK IMMEDIATE
          go

          -- do your stuff here
          ....
          go

          alter database northwind
          set MULTI_USER
          go


          AMB

          "DraguVaso" wrote:
          [color=blue]
          > Hi,
          >
          > My VB.NET-application has to do some actions on a SQL Server Database, but
          > during these actions the DataBase may not be used by any other application.
          > the total time of the lock could be up to 15 minutes. How can I perform
          > something like a lock, who denies other users/applications to access the
          > Database, and after a while grant themagain permission.
          >
          > Thanks,
          >
          > Pieter
          >
          >
          >[/color]

            Comment

            • DraguVaso
              #4
              Re: locking an SQL Server DB with .NET

              Thanks both.
              Isn't it a bit dangerous to do things like this? Otherwise: what if i workd
              with a different kind of login/username, and put the rights of the other
              users to read-only (db_datareader) ?

              "Alejandro Mesa" <AlejandroMesa@ discussions.mic rosoft.com> wrote in message
              news:94BA1795-B850-43C2-8D7A-A1259425A10A@mi crosoft.com...[color=blue]
              > See "ALTER DATABASE" in BOL. You can use this statement to set the[/color]
              database[color=blue]
              > status in SINGLE_USER, do your stuff and set it back to MULTI_USER.
              >
              > Example:
              >
              > use northwind
              > go
              >
              > alter database northwind
              > set SINGLE_USER with ROLLBACK IMMEDIATE
              > go
              >
              > -- do your stuff here
              > ...
              > go
              >
              > alter database northwind
              > set MULTI_USER
              > go
              >
              >
              > AMB
              >
              > "DraguVaso" wrote:
              >[color=green]
              > > Hi,
              > >
              > > My VB.NET-application has to do some actions on a SQL Server Database,[/color][/color]
              but[color=blue][color=green]
              > > during these actions the DataBase may not be used by any other[/color][/color]
              application.[color=blue][color=green]
              > > the total time of the lock could be up to 15 minutes. How can I perform
              > > something like a lock, who denies other users/applications to access the
              > > Database, and after a while grant themagain permission.
              > >
              > > Thanks,
              > >
              > > Pieter
              > >
              > >
              > >[/color][/color]


                Comment

                • Nick Malik [Microsoft]
                  #5
                  Re: locking an SQL Server DB with .NET

                  what is it that you are doing that you want to write to the db but you are
                  willing to allow others to read from it?

                  And why on earth does it take 15 minutes?

                  Are you pulling data out for a data warehouse?

                  --
                  --- Nick Malik [Microsoft]
                  MCSD, CFPS, Certified Scrummaster
                  http://blogs.msdn.com/nickmalik

                  Disclaimer: Opinions expressed in this forum are my own, and not
                  representative of my employer.
                  I do not answer questions on behalf of my employer. I'm just a
                  programmer helping programmers.
                  --
                  "DraguVaso" <pietercoucke@h otmail.com> wrote in message
                  news:OoFFtRKVFH A.1200@TK2MSFTN GP14.phx.gbl...[color=blue]
                  > Thanks both.
                  > Isn't it a bit dangerous to do things like this? Otherwise: what if i
                  > workd
                  > with a different kind of login/username, and put the rights of the other
                  > users to read-only (db_datareader) ?
                  >
                  > "Alejandro Mesa" <AlejandroMesa@ discussions.mic rosoft.com> wrote in
                  > message
                  > news:94BA1795-B850-43C2-8D7A-A1259425A10A@mi crosoft.com...[color=green]
                  >> See "ALTER DATABASE" in BOL. You can use this statement to set the[/color]
                  > database[color=green]
                  >> status in SINGLE_USER, do your stuff and set it back to MULTI_USER.
                  >>
                  >> Example:
                  >>
                  >> use northwind
                  >> go
                  >>
                  >> alter database northwind
                  >> set SINGLE_USER with ROLLBACK IMMEDIATE
                  >> go
                  >>
                  >> -- do your stuff here
                  >> ...
                  >> go
                  >>
                  >> alter database northwind
                  >> set MULTI_USER
                  >> go
                  >>
                  >>
                  >> AMB
                  >>
                  >> "DraguVaso" wrote:
                  >>[color=darkred]
                  >> > Hi,
                  >> >
                  >> > My VB.NET-application has to do some actions on a SQL Server Database,[/color][/color]
                  > but[color=green][color=darkred]
                  >> > during these actions the DataBase may not be used by any other[/color][/color]
                  > application.[color=green][color=darkred]
                  >> > the total time of the lock could be up to 15 minutes. How can I perform
                  >> > something like a lock, who denies other users/applications to access
                  >> > the
                  >> > Database, and after a while grant themagain permission.
                  >> >
                  >> > Thanks,
                  >> >
                  >> > Pieter
                  >> >
                  >> >
                  >> >[/color][/color]
                  >
                  >[/color]


                    Comment

                    • DraguVaso
                      #6
                      Re: locking an SQL Server DB with .NET

                      Hehe good question :-)
                      Well: I need to write some sort of replication between different SQL
                      Servers, and the replication has some very specific property's:
                      - The SQL Servers doesn't have a direct connection: it's between different
                      palmtree-plantations in africa, and the only communication they have is once
                      a week to send as-small-ass-possible files with some kind of radio :-)
                      - so during the replication/synchronization people shouldn't be allowed to
                      make any other changes in the databases
                      - the 15 minutes: they are just slow over there to follow the whole
                      procedure...


                      "Nick Malik [Microsoft]" <nickmalik@hotm ail.nospam.com> wrote in message
                      news:CbidndJGCI roPRjfRVn-og@comcast.com. ..[color=blue]
                      > what is it that you are doing that you want to write to the db but you are
                      > willing to allow others to read from it?
                      >
                      > And why on earth does it take 15 minutes?
                      >
                      > Are you pulling data out for a data warehouse?
                      >
                      > --
                      > --- Nick Malik [Microsoft]
                      > MCSD, CFPS, Certified Scrummaster
                      > http://blogs.msdn.com/nickmalik
                      >
                      > Disclaimer: Opinions expressed in this forum are my own, and not
                      > representative of my employer.
                      > I do not answer questions on behalf of my employer. I'm just a
                      > programmer helping programmers.
                      > --
                      > "DraguVaso" <pietercoucke@h otmail.com> wrote in message
                      > news:OoFFtRKVFH A.1200@TK2MSFTN GP14.phx.gbl...[color=green]
                      > > Thanks both.
                      > > Isn't it a bit dangerous to do things like this? Otherwise: what if i
                      > > workd
                      > > with a different kind of login/username, and put the rights of the other
                      > > users to read-only (db_datareader) ?
                      > >
                      > > "Alejandro Mesa" <AlejandroMesa@ discussions.mic rosoft.com> wrote in
                      > > message
                      > > news:94BA1795-B850-43C2-8D7A-A1259425A10A@mi crosoft.com...[color=darkred]
                      > >> See "ALTER DATABASE" in BOL. You can use this statement to set the[/color]
                      > > database[color=darkred]
                      > >> status in SINGLE_USER, do your stuff and set it back to MULTI_USER.
                      > >>
                      > >> Example:
                      > >>
                      > >> use northwind
                      > >> go
                      > >>
                      > >> alter database northwind
                      > >> set SINGLE_USER with ROLLBACK IMMEDIATE
                      > >> go
                      > >>
                      > >> -- do your stuff here
                      > >> ...
                      > >> go
                      > >>
                      > >> alter database northwind
                      > >> set MULTI_USER
                      > >> go
                      > >>
                      > >>
                      > >> AMB
                      > >>
                      > >> "DraguVaso" wrote:
                      > >>
                      > >> > Hi,
                      > >> >
                      > >> > My VB.NET-application has to do some actions on a SQL Server[/color][/color][/color]
                      Database,[color=blue][color=green]
                      > > but[color=darkred]
                      > >> > during these actions the DataBase may not be used by any other[/color]
                      > > application.[color=darkred]
                      > >> > the total time of the lock could be up to 15 minutes. How can I[/color][/color][/color]
                      perform[color=blue][color=green][color=darkred]
                      > >> > something like a lock, who denies other users/applications to access
                      > >> > the
                      > >> > Database, and after a while grant themagain permission.
                      > >> >
                      > >> > Thanks,
                      > >> >
                      > >> > Pieter
                      > >> >
                      > >> >
                      > >> >[/color]
                      > >
                      > >[/color]
                      >
                      >[/color]


                        Comment

                        Previous template Next
                        Working...