Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: processing result table from SP in SELECT

    I am trying to process the resulting table for the sp_who2, to have blocked SPID's on top of the list (ORDER BY BlkBy, SPID). But I can't get it to work. This is what I have done so far:
    Code:
    EXECUTE sp_AddLinkedServer 'EAVTS0042', N'SQL Server'
    
    EXECUTE sp_serverOption EAVTS0042, 'data access', 'true'
    
    SELECT * 
    from OPENQUERY(EANTS00422,
          'EXECUTE EAVTS0042.MSCRM.dbo.sp_who2')
    The SELECT * gives me this error message.
    -- ErrorMsg
    -- Msg 7357, Level 16, State 2, Line 1
    -- Cannot process the object "EXECUTE EAVTS0042.MSCRM.dbo.sp_who2". The OLE DB provider "SQLNCLI" for linked server "EAVTS0042" indicates that either the object has no columns or the current user does not have permissions on that object.
    Is this the best way to process the resulting table from a sp? I'd prefer to avoid to create a temp #sp_who2Table, but if that is what it takes ....

    SQL Server 2005, logged in as sa.
    Last edited by Wim; 09-01-09 at 12:24.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm pretty sure the only method is using a temp table - I'd love to be shown otherwise though!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Suck it up you big girl
    Code:
    DECLARE @spwho TABLE
        (
              spid          INT
            , status        VARCHAR(255)
            , login         VARCHAR(255)
            , hostname      VARCHAR(255)
            , blkby         VARCHAR(10)
            , dbname        VARCHAR(255)
            , command       VARCHAR(255)
            , cputime       INT
            , diskio        INT
            , lastbatch     VARCHAR(255)
            , programname   VARCHAR(255)
            , spid2         INT
            , requestid     INT
        )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by gvee
    I'd love to be shown otherwise though!
    I have found this solution.
    This syntax works in both SQL Server 2000 and SQL Server 2005, and requires integrated security to be turned on. Under SQL Server 2005, Ad Hoc Distributed Queries must be enabled.

    Here’s a simple sample that assigns the output from master.sp_who to a derived table called tbl:

    SELECT *
    FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES ;','set fmtonly off exec master.dbo.sp_who')
    AS tbl
    It works with sp_who. It doesn't work with sp_who2, because the result contains multiple columns with the same name: SPID.

    I'll go with the temp table. Thank you Pootle for the DDL.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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