| Author |
Topic |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2009-05-06 : 04:49:36
|
| does select queries get logged in transaction log file?Iam a slow walker but i never walk back |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 05:17:23
|
| i know insert and delete gets logged in transaction log file.but what abt select statements. will it get logged in transaction file.Thanks in advance.Iam a slow walker but i never walk back |
 |
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-05-06 : 05:48:55
|
| No. The Insert,Update,Delete statemenets are logged in the transaction log file for using the same in the event of a rollback. Select Statements are not being logged as there is nothing to be rolled back for a Select Query |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 05:50:35
|
| Fine thanks.Iam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 06:46:15
|
I was emailed this by dinershrajan_it:quote:
Hi Charles,I need a help from your side. I need to audit select statements. I dont have enterprise edition. The scenario is when a doctor views the patient record some few days back. what r all the records he viewed must get logged into table. thanks in advance
Dineshrajan -- don't email me. I don't know you. also -- If you want a question answered or some help with something just POST. I've yet to see a question go unanswered or a request for help go unheeded here.It's not a good idea to contact people directly -- its a breach of etiquette that just makes people not want to help.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 07:06:28
|
| Sorry.Iam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 07:15:35
|
| what does the doctor use to run his queries?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 07:23:38
|
| He implies the select statement to view the patient details.so that in future i have to get the rows viewed by doctor on particular dayIam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 07:41:00
|
quote:
Originally posted by dineshrajan_it
He implies the select statement to view the patient details.so that in future i have to get the rows viewed by doctor on particular dayIam a slow walker but i never walk back
What? he implies the select statement?I meant what program or interface does the doctor use to generate and run his queries?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 08:05:49
|
| if i select 10 records on a particular day.after few days, when i open my report to see what all the rows viewed by the user. i should be able to show that. thats scenarioIam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 08:14:10
|
| Yes I understand your scenario.Are you going to answer my question?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-06 : 09:00:34
|
| I cant get your question. Select top 100 * from orders.i need to log these records into audit table.i logged them in form of xml. but it consumes more space. Any other alternative. does compressing the data is right solution.Iam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-06 : 09:30:59
|
| OKYou want to be able to track what a doctor has asked for and when - Correct?The doctor uses some *way* of running his queries -- some interface to your database? A program? some other way?If your users are going through your application to access the database then presumably you could change logging to keep track of queries ran.Basically I'm talking about altering the middle tier of you platform -- I'm assuming that doctors don't get to format and run their own queries directly! I don't know because you haven't told us.Keeping track of the results returned to the doctors is a really bad idea -- it's better to track the queries used by them.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-07 : 01:19:20
|
| yes i konw that its better to log"select col1,col2 from table" rather than logging the results in the log table. But this is client requirement to know what doctor has viewed(including results) on that particular day.Hectic Task. Any soln.Iam a slow walker but i never walk back |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-07 : 01:38:14
|
logging the result may not be a feasible solution. If the doctor runs a query that returns 1,000 rows and the query was executed let's say 10 times in a day. So 1 day you will have 10,000 rows. 1 Month 10,000 x 30 = 300,000 rows. 1 year 300,000 x 12 = 3,600,000 rowsJust multiply this by the number of doctors and number of such query you have . . your hardware vendor will be very happy. 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-07 : 02:03:56
|
| Yes ur correct. so whats the solution to overcome this. i have done compression. will that help?Iam a slow walker but i never walk back |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-07 : 04:12:40
|
| Is your database set up for 2d time?By that I mean that:If you have the query the doctor used -- can you rerun that (at any point in the future with the relevant date) to regenerate the result set that the doctor would have seen?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Select Statement