Program to exclusively lock tables?

[roswald]

We have a program that runs at 5pm each night for around 15 minutes. Sometimes if users are on the system and accessing any of the three tables that the 5 pm program updates, the updates will fail.
Lets say the program updates tableA, B, and C.
Is there a way to write a script that says get exclusive lock on tableA, B, and C for 5pm program?
We don't have access to the source, otherwise I could use table hint or perhaps setting an isolation level, but we only have the object.
Any ideas would really be appreciated.

bob

 

[NoCoolHandle]

One Option..
The following code Kills all connectons to a given database and then sets the db to SIngle User Mode.. This would then be and ideal state to run your updates..

e.g. After running the following code. [blue]exec Sp_KickOutUsersAndSetSingleUserMode 'crap'[/blue] would kick all users out of the "Crap" database and leave it in single user mode..

(don't forget to set it back when you are done


Rob

Use Master
go
Create Proc Sp_KickOutUsersAndSetSingleUserMode @DatabaseName varchar(3000)
as
Declare @spid int,
@cmd Varchar(8000)
set @spid = 0
while not @spid  is null
	begin
		select @spid = min(spid) from master.dbo.sysprocesses
		where dbid = db_id(@DatabaseName) and Spid > @spid
		and spid <> @@spid
		if @spid is null 
			begin
				break
			end
		Set @cmd =  'osql -E -Q"Kill ' +  cast(@spid as varchar(2000)) + '"'
		print @cmd
		exec (@cmd)
	end
exec	sp_dboption @DatabaseName,'Single User',true
go
exec Sp_KickOutUsersAndSetSingleUserMode 'crap'

 

[SQLDenis]

or the nuclear option

ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do your stuff here


-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/