OPENQUERY Error on a linked server

  • Thanks.. This helped a bunch by allowing me to run sp_Who2 and limit the results only to blockers.

    [font="Courier New"] SELECT



    OPENQUERY(MyServerName, 'SET FMTONLY OFF; EXEC master.dbo.sp_Who2') t


    t.BlkBy != ' . '[/font]

  • David,

    My understanding is that OPENQUERY must return a result set, which is why it can be used wherever a able expression is appropriate, such as a FROM clause. I don't understand why direct calling of your procedure is not effective, but it looks like you have a permission error of some sort. My recommendation would be to see if you can resolve the error, and then call it via a 4-part name as you attemped:

    [SERVERNAME]...SP_Name '','SPPassword'

    Nonetheless, I found that with some test code, even though I received an error, an insert still occurred. This was tested in SQL2000 and SQL 2008.

    Here's some sample code:

    use tempdb

    create table dbo.myTemp

    (col1 int identity(1,1)

    ,col2 datetime default getdate()

    ,col3 varchar(10) )


    create procedure dbo.test1

    (@myVal varchar(10) )



    insert myTemp (col3)




    -- !!! REPLACE myServerName with the actual name of your sql server instance

    select * from openquery([i]myServerName[/i], 'set fmtonly off; exec tempdb.dbo.test1 ''AA'' ')

    select * from tempdb.dbo.myTemp

    /* Cleanup when done

    drop proc dbo.test1

    drop table dbo.myTemp


    Bottom line: Doing inserts in a proc via OPENQUERY does not seem especially robust. As I said above, try the 4-part name call first.

    Scott Thornburg

  • DFolz,

    Excellent use! I've done something similar, filtering by database or user or SPID. You can also use this to insert the results of SP_WHO2 into a table (have to pre-create the table or use a SELECT INTO, and watch out for the double SPID column).

    Glad this helped,

    Scott Thornburg

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply