Post reply

link server

  • July 20, 2004 at 9:56 am

    #162317

    I am trying to use profiler to see linked server queries.  Which server should I run the trace on? 

    1) where the query executes from (the ultimate destination sql machine)?

    2) where the user is connected to?

     

  • July 21, 2004 at 4:04 am

    #515441

    The answer could be either or both. It depends on the nature of the SQL query and the event(s) that you wish to capture.

    The SQL:BatchStarting, SQL:BatchCompleted, SQL:StmtStarting and SQL:StmtCompleted events will show up on the originating server and not the linked server.

    Accordingly, native SQL (eg SELECT * mylinkedserver.mydb.dbo.mytable) from  is straightforward. The position is a bit more complex where you are dealing with stored procedures. Running a proc on the linked server (eg exec mylinkedserver.mydb.dbo.myproc) has different results to running a proc on the local server which contains a query against the linked server.

    In the latter case the position is similar to what happens with running native SQL in that, as well as the SQL events, the SP:Starting, SP:Completed, SP:StmtStarting and SP:StmtCompleted events will appear on the local machine.

    However, when running a sp on the linked server the SP:Starting, SP:Completed, SP:StmtStarting and SP:StmtCompleted events will appear on the linked server and not the local server.

    Hope this is of some help.

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

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