0

I've got a SQL Server 2005 instance and I have a table that I'd like to trace, which we'll call Table1. I also have a linked server, which will query Table1.

If I set up my trace, it will capture events if I query from the host. If I query from the linked server, I capture nothing.

Has anyone tried to do this before and found success? Thanks in advance!

hughball
  • 50
  • 7
  • Can't you just set up a trace on the linked server? The linked server is the one actually executing SQL, so that's where you'll have to be running the trace. – Michael Dunlap Nov 14 '13 at 21:11
  • Yes, I'm sure I could. I'm trying to minimize the amount of traces though. Good suggestion - I'll keep it as a backup. – hughball Nov 14 '13 at 21:18

1 Answers1

0

Don't mean to summon an old question, but some may find this answer helpful in the future.

In your trace you also need to specify an Event called RPC:Starting. This is a trigger for when a Remote Procedure Call is executed.

If you are using the SQL Server Profiler, this event can be found under the Stored Procedures drop down.

If creating a server side script, you will need to turn on event 11 (the RPC:Starting event number) Example: 

EXEC sp_trace_setevent @TraceID, 11,  1, 1 -- TextData: The SQL statement that was executed

You can find the other event codes and column numbers here: https://msdn.microsoft.com/en-us/library/ms186265.aspx

Colin Douglas
  • 483
  • 1
  • 4
  • 9