October 14, 2010 at 7:31 am
Hello all,
I am dealing with database migration, but i don't have the source code for the new application that uses the new database.
What I am trying to do is : use the application and see what tables are affected with certain action.
So, to post my concrete question is there a way to see what are the affected tables in a certain time in MS SQL server 2008?
Thank you in advance.
SQL beginner. 🙂
October 14, 2010 at 7:52 am
You can always use SQL Profiler and capture a trace while users are using the application. It will capture every SQL statement executed against the database which you can then evaluate as to how the application interacts with the underlying schema. There is a performance penaly for running a trace against a production database however so be aware of that.
October 14, 2010 at 8:04 am
[EDIT] I misread your question, post removed.
-- Gianluca Sartori
October 14, 2010 at 7:22 pm
A trace might work and it might not. If the queries are calling for tables that don't exist, or columns within tables that don't exist, or the data types are different, the query will fail and you won't see it in the normal trace. You would have to trace for errors as well as RPC Complete & SQL Batch Complete. I'm not sure, off the top of my head, if the error will include the SQL text of the call.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2010 at 3:06 am
Thank you for the help so far, i will try to trace the changes. This will be my first time. 🙂
My idea was to use the application, by entering some test data and see how the tables are affected.
Do you think think that the Profiler will show all the data?
Can you recommend some settings for the trace?
Does this maybe give you any other ideas?
October 19, 2010 at 3:08 am
Just to add,
the application is not yet in use, so I can do what ever i want to it, until the users start to use it officially. This will be done after all the data from the old DB is transfered.
... I was thinking that maybe this can help you, while giving me help. 🙂
October 19, 2010 at 4:06 am
First hit the db from the application that you have and check if they are actually transferring the data to the database.
Then as friends have told, run the sql profiler restricting the trace to only the account [ the account that you use to hit the database from the application] and capture the query, which will give you the tables affected
Regards,
Prajey
October 19, 2010 at 6:12 am
MpriorJ (10/19/2010)
Thank you for the help so far, i will try to trace the changes. This will be my first time. 🙂My idea was to use the application, by entering some test data and see how the tables are affected.
Do you think think that the Profiler will show all the data?
Can you recommend some settings for the trace?
Does this maybe give you any other ideas?
Not knowing precisely what you're looking for, the information I would capture is TSQL:SQL:Batch Complete and Stored Procedure:RPC:Complete. These are the two basic calls that could be used to execute a query. The default trace from profiler will capture these, plus a few things. You could just use that. Like the other poster said, you'll want to filter the results.
Just remember, if there are tables or columns missing, as your request seems to imply, trace won't capture the missing information because that will cause compile errors, meaning, the query will never complete, so you won't see it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply