March 31, 2003 at 11:08 pm
I am trying to detect which procedure a user has just created and have written the sql below as a test of what is happening.
However, the code only returns a result for every second connection.
Does anyone have any ideas?
BEGIN TRAN
GO
CREATE PROCEDURE _testproc
AS
SELECT ''
GO
SELECT DISTINCT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(name) AS ObjectName
FROM dbo.sysobjects AS o
INNER JOIN master.dbo.syslocks as l ON l.id = o.id
WHERE l.spid = @@SPID
AND (OBJECTPROPERTY(o.id, 'IsProcedure') = 1)
AND (db_name(dbid) = dbname())
GO
ROLLBACK TRAN
GO
April 1, 2003 at 8:41 am
Could it be that the "GO" statements are terminating the command? I would try removing these.
Guarddata-
April 1, 2003 at 4:43 pm
We need the GO statements in between because a Create Procedure statement must be the first statement in a command batch
April 1, 2003 at 5:00 pm
Mmm..hmmm. That is certainly true.
Unfortunately, SQL2K no longer uses the syslocks table so I can't help much. However, isn't the table only viable while the lock is in place? Unless this code is run during the creation of the procedure, it wouldn't return results. My head may be asleep and I'm in never-land, but if that is correct, you would only be able to detect the procedure in the same transaction as it is being created.... (which may be what you are wanting)
Sorry - no real answers.
Guarddata-
April 1, 2003 at 5:12 pm
Thanks guarddata. I didn't realise that syslocks is no longer supported in SQL2K. I changed the statement to use syslockinfo and it seems to work every time.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply