September 12, 2018 at 11:20 am
I'm using Profiler to try and capture when a specific field in a specific table is modified by the accounting software we use and so far I'm not seeing in the data captured by Profiler where the update/insert is made. I can see when some inserts are made as well as updates but none of the DML captured is accounting for what ends up in the data so I'm wondering if there is some way for an app to change the data in a SQL Server table that is not captured by Profiler such as when using the various data objects that are found in VB/VB.Net programing.
Basically is it possible for an application not add or edit data in a table in a sql server DB and Profiler not be able to capture it?
Kindest Regards,
Just say No to Facebook!September 13, 2018 at 8:14 am
As long as you're capturing rpc_completed or sql_batch_completed, you're capturing the incoming calls. However, triggers could fire and affect things and you won't see them as such. Instead, you have to add sp:stmt_completed. Just be warned, in a trace, that's a very expensive operation that will spit out a lot of information. Since you're running 2008, you don't have options. However, if you were running 2012 or greater, I'd suggest using extended events instead because you can get much better filtering on the events and not have to collect as much data.
It's also possible, I think, to have nested procedure calls that won't show up as the rpc_completed call. You can get those also with the sp_stmt_completed event.
Other than triggers, there's no way to modify data in secret.
"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
September 13, 2018 at 8:32 am
Grant Fritchey - Thursday, September 13, 2018 8:14 AMAs long as you're capturing rpc_completed or sql_batch_completed, you're capturing the incoming calls. However, triggers could fire and affect things and you won't see them as such. Instead, you have to add sp:stmt_completed. Just be warned, in a trace, that's a very expensive operation that will spit out a lot of information. Since you're running 2008, you don't have options. However, if you were running 2012 or greater, I'd suggest using extended events instead because you can get much better filtering on the events and not have to collect as much data.It's also possible, I think, to have nested procedure calls that won't show up as the rpc_completed call. You can get those also with the sp_stmt_completed event.
Other than triggers, there's no way to modify data in secret.
Grant
So if I add the below events to my trace (on a SQL 2008R2 server) I should in your opinion capture %999 of all DML? I realize it will end up producing a lot of data to dig thru but if I want to make sure I miss nothing then these are the events to trace?
rpc_completed
sql_batch_completed
sp:stmt_completed
Thanks
NOTE: I managed to find the update I was looking for but this is still good info to have for future reference/use of Profiler. The accounting software vendor we deal with is known for having a brick wall between support and development and while the support folks mean well and do their best, often the answer to something like this has to be found by a savvy user/client b/c the development department is like a red tape plagued government agency.
Kindest Regards,
Just say No to Facebook!September 13, 2018 at 8:37 am
YSLGuru - Thursday, September 13, 2018 8:32 AMGrant Fritchey - Thursday, September 13, 2018 8:14 AMAs long as you're capturing rpc_completed or sql_batch_completed, you're capturing the incoming calls. However, triggers could fire and affect things and you won't see them as such. Instead, you have to add sp:stmt_completed. Just be warned, in a trace, that's a very expensive operation that will spit out a lot of information. Since you're running 2008, you don't have options. However, if you were running 2012 or greater, I'd suggest using extended events instead because you can get much better filtering on the events and not have to collect as much data.It's also possible, I think, to have nested procedure calls that won't show up as the rpc_completed call. You can get those also with the sp_stmt_completed event.
Other than triggers, there's no way to modify data in secret.
Grant
So if I add the below events to my trace (on a SQL 2008R2 server) I should in your opinion capture %999 of all DML? I realize it will end up producing a lot of data to dig thru but if I want to make sure I miss nothing then these are the events to trace?
rpc_completed
sql_batch_completed
sp:stmt_completedThanks
NOTE: I managed to find the update I was looking for but this is still good info to have for future reference/use of Profiler. The accounting software vendor we deal with is known for having a brick wall between support and development and while the support folks mean well and do their best, often the answer to something like this has to be found by a savvy user/client b/c the development department is like a red tape plagued government agency.
I don't know of anything that wouldn't catch, no. Should be good. Unless someone has invented some sort of super back door hack that I've never heard of, the only way to manipulate data is through DML calls. Doesn't matter how you do those, they're either rpc or batch and individual statements within them.
I hate when support acts that way. It's supposed to be a service for crying out loud.
"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
September 13, 2018 at 8:43 am
Grant Fritchey - Thursday, September 13, 2018 8:37 AMYSLGuru - Thursday, September 13, 2018 8:32 AMGrant Fritchey - Thursday, September 13, 2018 8:14 AMAs long as you're capturing rpc_completed or sql_batch_completed, you're capturing the incoming calls. However, triggers could fire and affect things and you won't see them as such. Instead, you have to add sp:stmt_completed. Just be warned, in a trace, that's a very expensive operation that will spit out a lot of information. Since you're running 2008, you don't have options. However, if you were running 2012 or greater, I'd suggest using extended events instead because you can get much better filtering on the events and not have to collect as much data.It's also possible, I think, to have nested procedure calls that won't show up as the rpc_completed call. You can get those also with the sp_stmt_completed event.
Other than triggers, there's no way to modify data in secret.
Grant
So if I add the below events to my trace (on a SQL 2008R2 server) I should in your opinion capture %999 of all DML? I realize it will end up producing a lot of data to dig thru but if I want to make sure I miss nothing then these are the events to trace?
rpc_completed
sql_batch_completed
sp:stmt_completedThanks
NOTE: I managed to find the update I was looking for but this is still good info to have for future reference/use of Profiler. The accounting software vendor we deal with is known for having a brick wall between support and development and while the support folks mean well and do their best, often the answer to something like this has to be found by a savvy user/client b/c the development department is like a red tape plagued government agency.
I don't know of anything that wouldn't catch, no. Should be good. Unless someone has invented some sort of super back door hack that I've never heard of, the only way to manipulate data is through DML calls. Doesn't matter how you do those, they're either rpc or batch and individual statements within them.
I hate when support acts that way. It's supposed to be a service for crying out loud.
Thanks Grant
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply