SQL Server Performance

Migrating Script(7.00 to 2000)

Discussion in 'Contribute Your SQL Server Scripts' started by gkrishn, Mar 15, 2005.

  1. gkrishn New Member

    Hi following is a script which track userinfo and write to a table Useractivity.Its running fine in 7.00 but not in 2000. Can anyone make some changes to this ?

    CREATE PROC RefreshUserActivity
    AS
    INSERT UserActivity
    SELECT suser_sname(sid) as name,
    spid,
    db_name(dbid) as db,
    hostname, program_name,
    login_time, last_batch,
    NULL, net_address, net_library
    from master..sysprocesses as P
    where suser_name(suid) NOT IN ('probe', 'BackUpUser')
    and spid not between 1 and 6
    AND NOT EXISTS (select * from UserActivity as U
    where P.login_time = U.login_time
    and P.spid = U.spid)

    UPDATE UserActivity
    SET last_batch = P.last_batch
    FROM master..sysprocesses as P
    WHERE UserActivity.login_time = P.login_time
    AND UserActivity.spid = P.spid
    AND UserActivity.last_batch <> P.last_batch

    UPDATE UserActivity
    SET closed_by = getdate()
    WHERE closed_by IS NULL
    AND NOT EXISTS (select * from master..sysprocesses as P
    where P.login_time = UserActivity.login_time
    and P.spid = UserActivity.spid)


    i think suser_sname(sid) or suser_name(suid) is making error. Can anyone edit it Properly? its urgent. please reply
  2. satya Moderator

    BOL refers about
    SUSER_NAME always returns NULL when used in Microsoft® SQL Server™ 2000. This system built-in function is included only for backward compatibility. Use SUSER_SNAME instead.
    Try replacing with SUSER_SNAME.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page