August 10, 2006 at 2:38 pm
Hello,
We are having one ssis package to run against our database.This package is getting some files from the mainframe and it's loading the tables in sqlserver.
This package runs from 6:00 PM to 6:00 am everyday.While this package is running what we have to do is
1.Kill all user process at 6:00PM
2.dont' allow any users to access the tables while it's loading the tables.
What i have to do , to implement this .
Thanks,
August 10, 2006 at 3:28 pm
1) to kill all users see http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=30
There are also a couple of other similiar scripts available
2: to not allow users in set databse to single user status see BOL - single user EXEC sp_dboption 'SRX_000_MDS', 'single user', 'TRUE'
and use EXEC sp_dboption 'SRX_000_MDS', 'single user', 'FALSE' to change back
Francis
August 11, 2006 at 6:57 am
this scripts i have to specify inside the package i am running or in a job??
August 11, 2006 at 7:51 am
either.. but since they relate to the package I would put them inside the package as the 1st and last tasks to be done. (I think its called Execute SQL task) at least it was in DTS.
They don't have to be separate tasks if you have appropriate tasks set up alreadybut if they are you can more closely control their execution as in always remove the single user restriction even if the rest of the package fails.
Francis
August 11, 2006 at 11:06 am
sorry for again asking question.
Suppose i just want to the user who is access the tables which is getting inserted means , how can i do that
August 11, 2006 at 11:15 am
Please ask the question again. I don't understand
Francis
August 11, 2006 at 12:07 pm
The package is updating a table name " Test"
I just want to lock the table and kill the users who ever is accessing the table and the table should not be able to access until it's loaded.
August 11, 2006 at 12:31 pm
SQL will take care of this itself. If someone is locking the table so your package cannot get write access, the package will wait until it can get access. Once it has access, then a table lock will be issued so noone else can issue an exclusive lock until the package finishes with this table.
If you want to see who is locking this table and manage the locks youself use sp_lock or sp_lock2 (http://www.sqlservercentral.com/columnists/achigrik/lockview.asp) You will need to figure out the object id of the table and look it up in the sp_lock output. I'm not sure this is a good idea but it might be in your environment. Ususlly nobody has locks on for such a long time that SQL can't manage the process itself. But sp_lock may help you
Francis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply