return username or id?

Question

Post reply

return username or id?

SQLRep

SSCrazy

Points: 2052
More actions
March 3, 2005 at 9:59 am

#163748

Hi is there a way that i can return the user who is using the data or accessing the record?
how can i create that store procedure?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

March 3, 2005 at 10:05 am

#545281

have a look at suser_sname() in BOL

HTH

* Noel

SQLRep SSCrazy Points: 2052 More actions · Answer 2

oh ok i understand. but is it possible that i can check that the record id 45 is being accessed and then return 1 or 0 on it. For example. i want to create stored procedure that check the user y logs into the system from front and trying to access the same record but the same record is being access by user x so it returns 1 if it is being access otherwise return 0.

is it possible with stored procedure?!!

Thanks

philcart SSC-Forever Points: 47794 More actions · Answer 3

As I explained in this thread

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=163621

"Once the record is displayed on the users screen SQL Server is no longer involved."

You will not be able to query the record to see who's accessing it.

What is the problem with implementing the solution I provided in the other thread?

--------------------
Colt 45 - the original point and click interface

SQLRep SSCrazy Points: 2052 More actions · Answer 4

Hi phil,

I did explain that situation to them but they are not ready to accept that fact the if i allowed shared lock on the stored procedure or queries the other user will get either time out error or will have to wait longer. but they want me to find something that would work to see if the query returns that this record is in use right now so that i thought that if the query or sp can return 1 or 0 depeneding it the record is being used or not that i can return that value to frontend and generate the error in front end enviornment!!

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 5

Just see my comments about some ways of implementing pesimistic locking mechanisms on sql server on this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=152781#bm152867

HTH

* Noel

SQLRep SSCrazy Points: 2052 More actions · Answer 6

would you please give me an example that how can i do it! i have pasted the stored porcedure that i want to use as you have suggested in the other post!

CREATE PROCEDURE test

(

@ANo nvarchar (36),

@PNo nvarchar(20),

@Rtype smallint,

@En0 nvarchar(15),

@PLevel smallint,

@RStatus smallint,

@Sdate datetime,

@creator nvarchar(50),

@desc nvarchar(4000) = NULL,

@jn0 nvarchar(15) = NULL,

@cdate datetime = NULL,

@Cause nvarchar(4000) = NULL,

@TName nvarchar(50) = NULL,

@retval int output

--@Repaircategory char(10),

)

as

--declare @TransactionDate datetime

--select @TransactionDate = getdate()

--select @TransactionDate = convert(varchar(20),@TransactionDate ,106)

insert into Etable (pno, ano, Rtype, Repair_Status, Priority_Level, Sdate, cdate, desc, Cause, job_number, ENumber, Tname, creator)

values

(

@Pno,

@Ano,

@Rtype,

@RStatus,

@PLevel,

@Sdate,

@cdate,

@desc,

@Cause,

--@Repaircategory,

@jobno,

@Enumber,

@TName,

@creator

&nbsp

set @retval = @@IDENTITY

GO