jmlc1 (7/28/2016)
Thanks for the response. Say I have application A. I have chosen SQL Server (2012) as the DB backend, and I will write all my stored procs etc., in the backend.However, for UI (logon, reporting etc), I am using say Access (or something else) for the frontend.
The goal is to have the database reside on a server and let clients connect with an instance of Access running locally on their machine. Using SQL Server how would I trace data movement via stored procs/functions if possible. Does this make sense? Excuse the ambiguity as I am new to this.
1) Access would simply be doing pass-through sproc calls to make stuff happen.
2) Your idea of tracing "data movement" is flawed I think. You should know what happens to your data because you know what all sproc calls do with anything passed into them (both into and out of the database).
3) You have visibility into executions that happen using Profiler, which is a tool that comes with SQL Server installation (you can do just tools only on your dev box if you want). You can also see executions via Extended Events, but as a newbie I would definitely recommend using Profiler.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service