1

I've a long script in sql server 2000 (17k lines and growing).

Problem is that about the half, several triggers must be dropped & recreated as part of remote installation procedure.

Sometimes, one or two tables are in use and installation stopped without warning me, until i take a look into messages tab and found it.

I would like a semi-unattended installation and I've at least two options:

a) pessimistic approach: Confirm all tables availability at the beginning and show a warning + "set exec off" if some table is in use (very unlikely to be in use between starting and trigger creation)

b) optimistic approach: Show a warning at the beginning when some table is in use and leave it to me if installation can continue, encapsulating offending triggers with set exec off/on code. i can do this because trigger code tend to be VERY stable (several years old)

Anyway, i just need to know if table is available for trigger dropping/creating without stopping my process (table locking?). i logged into BOL & some forums but i really don't know what to look for.

update:
Use this select to find your Type_Id & Mode_Id & Sts_Id values...
(mine are 5, 6 & 1, respectively, but COULD be others, those came just from initial testing)
%%%WARNING: remember MS can CHANGE or DEPRECATE them as this is NOT the best way to do it, but the only one i've found 'til now... 

use <your_db_here>  
go  

declare @TblNam varchar(100)  
select  @TblNam = '<your_[usr.]table_name_here>'  

select  convert (smallint, l.req_spid)  spId,
        coalesce(s.loginame,'')         UsrNam,
        coalesce(s.hostname,'')         HstNam,
        l.rsc_dbid                      dbId,
        db_name(l.rsc_dbid)             dbNam,
        l.rsc_objid                     objId,
        object_name(l.rsc_objid)        objNam,
        l.rsc_indid                     indId,
        substring(v.name, 1, 4)         Type,
        substring(l.rsc_text, 1, 16)    Resource,
        substring(u.name, 1, 8)         Mode,
        substring(x.name, 1, 5)         Status,
        l.rsc_type                      Type_Id,
        l.req_mode+1                    Mode_Id,
        l.req_status                    Sts_Id

   from master.dbo.syslockinfo  l                                                   inner join  
        master.dbo.spt_values   v   on v.type = 'LR' and v.number = l.rsc_type      inner join  
        master.dbo.spt_values   x   on x.type = 'LS' and x.number = l.req_status    inner join  
        master.dbo.spt_values   u   on u.type =  'L' and u.number = l.req_mode+1  
        master.dbo.sysprocesses s   on s.spid = l.req_spid

  where db_name(l.rsc_dbid) = db_name()  
    and v.name      = 'TAB'  
    and l.rsc_objid = object_id(@TblNam)
OldyGuy
  • 21
  • 3
  • Wy can you not ALTER the triggers? You can get info on acquired locks using sp_lock which you can resolve down to objects but that info can change moment to moment. – Code Magician Dec 15 '11 at 00:31
  • i don't have locking info because this is a datawarehouse. – OldyGuy Dec 15 '11 at 04:51
  • seems more complicated than i would liked, but i'm digging into MS sp_lock2's source code to find locking status – OldyGuy Dec 15 '11 at 05:16
  • 1
    @OldyGuy You haven't clarified why you can't simply ALTER the triggers in place without re-creating them completely. I'm also not sure what your comment about not have locking info means: a datawarehouse is just a database and locking works the same way (unless you mean that you set it to read-only). Apart from that, a 17K line script sounds very difficult to manage; you may want to re-consider your approach in general, and at least break it into smaller chunks that can be run individually. – Pondlife Dec 15 '11 at 10:19
  • hi @Pondlife, 1) i'm working in ALTER, but my guess is that table locking problem should be the same (maybe i'm wrong). 2) my comment about datawarehouse was broken, sorry, i mean i don't have access to the base system code to check for their locking scheme. 3) you're right, obviously there are many changes every week in 400+ files involved in the script... i developed a script integrator to aggregate them in particular order and perform a remote install in 50+ customers. 4) i'll report soon about alter & udf_lock – OldyGuy Dec 16 '11 at 00:06

1 Answers1

1

this approach is better than digging into system tables:

set lock_timeout 1
select top 1 * from <your_[usr.]table_name_here> (tablockx)
select @@error
OldyGuy
  • 21
  • 3