Post reply

How to tell if someone using linked server

  • August 9, 2006 at 9:41 am

    #75036

    HI. I have a view of an oracle database that we access thru a linked server in sql. Is there a way to view the users who are accessing this view? Can this be seen thru enterprise manager?

    Thanks,

    Juanita

     

  • August 10, 2006 at 2:32 pm

    #654431

    There should be locks created by anyone accessing the view.  This query should tell you the SPID for someone using the view MyDB.xyz.MyView

    SELECT

    DISTINCT req_spid FROM syslockinfo WHERE rsc_dbid = db_id('MyDB') AND rsc_objid = object_id('MyDB.xyz.MyView')

    I tested this with a long-running linked server view I have and saw two locks while the query was running (hence the DISTINCT).  If you want to see who it is, go to the sysprocesses table.

    SELECT

    hostname, loginame FROM sysprocesses WHERE SPID IN (SELECT DISTINCT req_spid FROM ...)

    Note that this only works if you run it while their query is running.

  • August 11, 2006 at 6:56 am

    #654528

    Thank you so much!

    Juanita

     

Viewing 3 posts - 1 through 2 (of 2 total)

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