Professional Web Applications Themes

Manipulating the results of sp_who - Microsoft SQL / MS SQL Server

Hi, Is there a way of manipulating the results of sp_who from within a stored procedure? I basically need to call sp_who <UserName> and get all the open sesions of <UserName> and then filter out the records based on the dbName. Can this be done in a stored procedure? Thanks Ioannis...

  1. #1

    Default Manipulating the results of sp_who

    Hi,

    Is there a way of manipulating the results of sp_who from within a stored
    procedure? I basically need to call sp_who <UserName> and get all the open
    sesions of <UserName> and then filter out the records based on the dbName.
    Can this be done in a stored procedure?

    Thanks
    Ioannis


    Ioannis Demetriades Guest

  2. #2

    Default Re: Manipulating the results of sp_who

    you can pass the output of sp_who to a temporary table like this

    EXEC sp_serveroption <server name>, 'data access' , 'true'

    select *
    into #temptable
    from openquery (<server name>, 'exec sp_who')

    optionally you can also fire a select statement against the data set
    retrieved by sp_who.

    select *
    from openquery (<server name>, 'exec sp_who')

    --
    -Vishal
    "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    news:e$41mWhQDHA.2476TK2MSFTNGP10.phx.gbl...
    > Hi,
    >
    > Is there a way of manipulating the results of sp_who from within a stored
    > procedure? I basically need to call sp_who <UserName> and get all the open
    > sesions of <UserName> and then filter out the records based on the dbName.
    > Can this be done in a stored procedure?
    >
    > Thanks
    > Ioannis
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Manipulating the results of sp_who

    Is there another way of doing it without having to specify the <server name>
    ?

    Thanks

    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:OhLVhchQDHA.3880tk2msftngp13.phx.gbl...
    > you can pass the output of sp_who to a temporary table like this
    >
    > EXEC sp_serveroption <server name>, 'data access' , 'true'
    >
    > select *
    > into #temptable
    > from openquery (<server name>, 'exec sp_who')
    >
    > optionally you can also fire a select statement against the data set
    > retrieved by sp_who.
    >
    > select *
    > from openquery (<server name>, 'exec sp_who')
    >
    > --
    > -Vishal
    > "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    > news:e$41mWhQDHA.2476TK2MSFTNGP10.phx.gbl...
    > > Hi,
    > >
    > > Is there a way of manipulating the results of sp_who from within a
    stored
    > > procedure? I basically need to call sp_who <UserName> and get all the
    open
    > > sesions of <UserName> and then filter out the records based on the
    dbName.
    > > Can this be done in a stored procedure?
    > >
    > > Thanks
    > > Ioannis
    > >
    > >
    >
    >

    Ioannis Demetriades Guest

  4. #4

    Default Re: Manipulating the results of sp_who

    You can also extract the code that makes up sp_who, and edit it to do what
    you want.

    EXEC sp_helptext sp_who will show you the code. Copy and paste it into the
    query window and make changes as desired.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    [url]www.SolidQualityLearning.com[/url]


    "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    news:uokJgnhQDHA.2176TK2MSFTNGP12.phx.gbl...
    > Is there another way of doing it without having to specify the <server
    name>
    > ?
    >
    > Thanks
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:OhLVhchQDHA.3880tk2msftngp13.phx.gbl...
    > > you can pass the output of sp_who to a temporary table like this
    > >
    > > EXEC sp_serveroption <server name>, 'data access' , 'true'
    > >
    > > select *
    > > into #temptable
    > > from openquery (<server name>, 'exec sp_who')
    > >
    > > optionally you can also fire a select statement against the data set
    > > retrieved by sp_who.
    > >
    > > select *
    > > from openquery (<server name>, 'exec sp_who')
    > >
    > > --
    > > -Vishal
    > > "Ioannis Demetriades" <idemetriadesyahoo.co.uk> wrote in message
    > > news:e$41mWhQDHA.2476TK2MSFTNGP10.phx.gbl...
    > > > Hi,
    > > >
    > > > Is there a way of manipulating the results of sp_who from within a
    > stored
    > > > procedure? I basically need to call sp_who <UserName> and get all the
    > open
    > > > sesions of <UserName> and then filter out the records based on the
    > dbName.
    > > > Can this be done in a stored procedure?
    > > >
    > > > Thanks
    > > > Ioannis
    > > >
    > > >
    > >
    > >
    >
    >

    Kalen Delaney Guest

Similar Threads

  1. formatting find results in results window or in savedfile
    By InQuistve in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: January 23rd, 03:19 PM
  2. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  3. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  4. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  5. help with sp_who
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 02:54 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