Professional Web Applications Themes

stoopid question ... - Microsoft SQL / MS SQL Server

How do I do this: select * from sp_who where dbname like '%xyz%' I thought sp's were essentially like datasets but apparantly not. Thanks, Bob Castleman SuccessWare Software...

  1. #1

    Default stoopid question ...

    How do I do this:

    select * from sp_who where dbname like '%xyz%'


    I thought sp's were essentially like datasets but apparantly not.

    Thanks,

    Bob Castleman
    SuccessWare Software


    Bob Guest

  2. #2

    Default Re: stoopid question ...

    Stored procs can do a lot of things but they are not to be treated as rowsets, since they don't necessarily return rows. For your example, you'd want to go with a table-valued user-defined function (UDF) and then you can apply a WHERE onto that.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Bob Castleman" <nomailhere> wrote in message news:#SN#$phx.gbl...
    How do I do this:

    select * from sp_who where dbname like '%xyz%'


    I thought sp's were essentially like datasets but apparantly not.

    Thanks,

    Bob Castleman
    SuccessWare Software



    Tom Guest

  3. #3

    Default Re: stoopid question ...

    EXEC sp_serveroption '<server>', 'data access' , 'true'
    go
    select *
    from openquery (<server>, 'exec sp_who')
    where dbname like '%db%'

    --
    -Vishal

    "Bob Castleman" <nomailhere> wrote in message
    news:#SN#$phx.gbl... 


    Vishal Guest

  4. #4

    Default Re: stoopid question ...

    Bob,

    You can use OPENQUERY, but its not efficient.If in SQL2k, UDFs are your best
    bet.

    Add your server name as linked server :

    exec sp_addlinkedserver [servername]
    exec sp_serveroption [servername], 'data access', 'true'

    then,

    SELECT * FROM OPENQUERY([servername], 'EXEC SP_WHO') WHERE dbname = 'master'


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Bob Castleman" <nomailhere> wrote in message
    news:%23SN%23$phx.gbl... 


    Dinesh.T.K Guest

  5. #5

    Default Thanks guys (no text)

    no text


    Bob Guest

  6. #6

    Default Re: stoopid question ...

    Hi all
    I am executing this command

    EXEC sp_serveroption 'KISHOR', 'data access' , 'true'
    GO
    select * from openquery ([Kishor],'exec sp_who')
    where dbname like '%ktemp%'

    I am having problem. Please see error

    Server: Msg 17, Level 16, State 1, Line 1
    SQL Server does not exist or access denied.


    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Kishor Guest

  7. #7

    Default Re: stoopid question ...

    Had you first added a linked server named KISHOR?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Kishor" <com> wrote in message news:#CgATc#phx.gbl...
    Hi all
    I am executing this command

    EXEC sp_serveroption 'KISHOR', 'data access' , 'true'
    GO
    select * from openquery ([Kishor],'exec sp_who')
    where dbname like '%ktemp%'

    I am having problem. Please see error

    Server: Msg 17, Level 16, State 1, Line 1
    SQL Server does not exist or access denied.


    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]



    Tom Guest

  8. #8

    Default Re: stoopid question ...

    This works in query yzer:

    SELECT loginame, dbname
    FROM OPENQUERY(SW21CLSTR,'EXEC sp_who') WHERE dbname LIKE '%wil%'


    but this returns nothing when called:

    CREATE FUNCTION dbo.SW21_Who (dbname char(50))
    RETURNS usersTab table
    (
    loginName char(100),
    databaseName char(100)
    )
    AS
    BEGIN

    insert into usersTab
    select loginame, dbname
    from openquery(SW21CLSTR,'exec sp_who') WHERE dbname like '%'+dbname+'%'
    return
    END


    So what am I missing?


    Also, if I use the enterprise manager to create the UDF, when I attempt to save it I get the following message:

    "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

    If I create the UDF in the Query yzer, it works, but if I use the QA to alter (with script to new window as alter) it gets the same message. The script generated has the line SET ANSI_NULLS OFF. I am assuming the EM puts that in there when it is saving the script. Why would it turn this off?


    Finally, do the calls to sp_addlinkedserver and sp_serveroption need to be called again if the server is rebooted? And sp_addlinkedserver only needs to be called if you're accessing info that is not on the server where I'm logged in, correct?


    Thanks,

    Bob

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Had you first added a linked server named KISHOR?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Kishor" <com> wrote in message news:#CgATc#phx.gbl...
    Hi all
    I am executing this command

    EXEC sp_serveroption 'KISHOR', 'data access' , 'true'
    GO
    select * from openquery ([Kishor],'exec sp_who')
    where dbname like '%ktemp%'

    I am having problem. Please see error

    Server: Msg 17, Level 16, State 1, Line 1
    SQL Server does not exist or access denied.


    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Bob Guest

  9. #9

    Default Re: stoopid question ...


    Compile the following view under the master DB. Once you have so, you
    can now do such things are select * from vw_who2bydb where status =
    'runnable' and dbname = 'master'

    /************************************************** *********************
    *******/
    Print 'VW_who2byDB Start: ' + cast(GetDate() as varchar(20))
    /************************************************** *********************
    *******/
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO
    /****** Object: dbo.VW_who2byDB Script Date: Wednesday, August 06,
    2003 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[VW_who2byDB]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop View [dbo].[VW_who2byDB]
    GO
    CREATE vIEW VW_who2byDB
    AS
    SELECT
    spid
    ,case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end as
    [dbName]
    ,convert(sysname, rtrim(loginame)) as [LoginName]
    ,Status
    ,hostname as [HostName]
    ,program_name as [ProgramName]
    ,cmd as [SQLCommand]
    ,cpu as [CPUResource]
    ,physical_io AS [Reads]
    ,blocked as [Block]
    ,dbid as [DatabaseID]
    ,spid as 'Spid Sort'
    , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
    + substring( convert(varchar,last_batch,113) ,13 ,8 )
    as 'RunDate'
    from master.dbo.sysprocesses (nolock)
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    GRANT SELECT ON [dbo].[VW_who2byDB] TO [PUBLIC]
    GO



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mitchell Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. regexp question + html::pr question on the side
    By boris in forum PERL Miscellaneous
    Replies: 4
    Last Post: September 27th, 02:24 AM
  4. Stoopid Question
    By Sandie Kerkvliet in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 2
    Last Post: July 14th, 04:46 AM
  5. newB question: related tables question
    By Blue man in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:13 AM

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