Professional Web Applications Themes

help with sp_who - Microsoft SQL / MS SQL Server

I want to get the user login name and host name for any user who is accesing the databases thru enterprise manager. I found out I can execute sp_who with spid as parameter and I get back result as a select statement result. I want to know how can I concatenate loginname and hostname field together and store in a variable in a select statement or do I have to create a function and store the result in a table and access the table via select ??? Other question is is it faster to execute sp_who to get the above ...

  1. #1

    Default help with sp_who

    I want to get the user login name and host name for any
    user who is accesing the databases thru enterprise
    manager. I found out I can execute sp_who with spid as
    parameter and I get back result as a select statement
    result.

    I want to know how can I concatenate loginname and
    hostname field together and store in a variable in a
    select statement or do I have to create a function and
    store the result in a table and access the table via
    select ???

    Other question is is it faster to execute sp_who to get
    the above said information inside a trigger or do a select
    directly into sysprocesses table.

    select LoginName = rtrim(loginame), HostName = rtrim
    (hostname)
    from master.dbo.sysprocesses
    where spid = SPID


    Thanks for help.

    Ricky
    Ricky Guest

  2. #2

    Default Re: help with sp_who

    sp_who does more than a simple select of sysprocesses, so the select is
    faster... but I don't think the time difference will be signficant.

    You can see how to get the info you describe if you look at the code in
    sp_who to see where it grabs it's data from. You could always write a custom
    version.... you can look at the text of sp_who directly from SQL EM. It's
    in the master DB.

    --

    Brian Moran
    Principal Mentor
    Solid Quality Learning
    SQL Server MVP
    [url]http://www.solidqualitylearning.com[/url]


    "Ricky" <ricky.arorametc.state.mn.us> wrote in message
    news:005901c347b9$87369910$a101280aphx.gbl...
    > I want to get the user login name and host name for any
    > user who is accesing the databases thru enterprise
    > manager. I found out I can execute sp_who with spid as
    > parameter and I get back result as a select statement
    > result.
    >
    > I want to know how can I concatenate loginname and
    > hostname field together and store in a variable in a
    > select statement or do I have to create a function and
    > store the result in a table and access the table via
    > select ???
    >
    > Other question is is it faster to execute sp_who to get
    > the above said information inside a trigger or do a select
    > directly into sysprocesses table.
    >
    > select LoginName = rtrim(loginame), HostName = rtrim
    > (hostname)
    > from master.dbo.sysprocesses
    > where spid = SPID
    >
    >
    > Thanks for help.
    >
    > Ricky

    Brian Moran Guest

Similar Threads

  1. Manipulating the results of sp_who
    By Ioannis Demetriades in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 10:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139