Semaphor global variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Semaphor global variable

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2008-05-15 : 04:52:59

I need to block access to a process when it is running but the process appends outside of SQL in DB2 on a mainframe.

I planing to do something like :

declare @@LastProcessTime datetime
IF @@LastProcessTime is null or datediff(mi, @@LastProcessTime, getdate())>3
Set @@LastProcessTime=getdate()
IF datepart(mi, coalesce(@@LastProcessTime, getdate())< 3
BEGIN
update DB2 --takes 2 minutes
Set @@LastProcessTime=null
END
ELSE
Raise error 'Under processing since ' + usf_format_dt(@@LastProcessTime, 'HH:NN')

Is it the right approach?

Mathias
Posting Yak Master

119 Posts

Posted - 2008-05-15 : 07:48:10

In fact @@var can be created but are not reachable from another connection. I have found a way to get it working with :

declare @LastStartTime smalldatetime
declare @Result int
select @LastStartTime=crdate from tempdb.dbo.sysobjects where name like '%##Semaphor%'
Set @Result=@@RowCount
IF @LastStartTime is null or datediff(mi, @LastStartTime, getdate())>2
begin
IF @Result>0
drop table ##Semaphor

create table ##Semaphor(LAST_START_TIME datetime)
print 'processing DB2'
drop table ##Semaphor
end
ELSE
begin
declare @t varchar(100)
Set @t='Already under process : '+dbo.usf_FormatDate(@LastStartTime,'HH:NN')
RAISERROR (@t,12,1)
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources