Keeping users out while updating data

I have a flat file with data that I need to use to update existing records
in my database with. I wrote a Stored Proc to do this, but I want to run it
when everyone is out of the database. My question is, how do I keep users
from accessing the database while my update is running? I thought about
taking it offline but BOL said that while it's offline it can not be
modified. Does this mean the data or the structure or both?
Thanks
Mike
You could set it to single_user temporarily...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"M Smith" <msmith@.avma.org> wrote in message
news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> I have a flat file with data that I need to use to update existing records
> in my database with. I wrote a Stored Proc to do this, but I want to run
it
> when everyone is out of the database. My question is, how do I keep users
> from accessing the database while my update is running? I thought about
> taking it offline but BOL said that while it's offline it can not be
> modified. Does this mean the data or the structure or both?
> Thanks
> Mike
>
|||OK, I set the database to start up in single_user mode. The problem is when
I try to open query analyzer to execute my stored proc it won't let me in.
It gives me a log in failure because SQL Server is in single user mode. How
can I execute my stored proc?
Mike
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> You could set it to single_user temporarily...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
records[vbcol=seagreen]
run[vbcol=seagreen]
> it
users
>
|||Setting it to restricted_user might be better assuming the normal users do
not have elevated privileges - with single_user the risk is someone getting
the connection before you which sounds like what is happening
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M Smith" <msmith@.avma.org> wrote in message
news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> OK, I set the database to start up in single_user mode. The problem is
when
> I try to open query analyzer to execute my stored proc it won't let me in.
> It gives me a log in failure because SQL Server is in single user mode.
How[vbcol=seagreen]
> can I execute my stored proc?
> Mike
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> records
> run
> users
about
>
|||How about just using a locking hint for a more restrictive lock. If you used the holdlock locking hint:
SELECT * FROM [TABLE] (HOLDLOCK) it would be as if you were briefly the only user of that table.
There are other locks less restrictive than that like tablock and UPDLOCK, which will let others read the data.
|||Hi,
I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure
Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER
Thanks
Hari
MCDBA
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting[vbcol=seagreen]
> the connection before you which sounds like what is happening
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> when
in.[vbcol=seagreen]
> How
to
> about
>