As Eddy has said, you need to address your requirements first! Attempting to retain every change, both from and to via the .ldf is not going to work. Eddies suggestion that you make copies of records instead of updating is valid (I have used this under duress), marking deleted records as Disabled is also a recognised solution.
Go back to your requirements and reassess them in the light of what you have learned over the last few weeks trying to use the wrong design to achieve a desired result.
Never underestimate the power of human stupidity
What does the trigger-solution provide, besides extra trouble in maintainability? We're not synchronizing on every record, and he merely needs a duplicate. Changing the command being executed would be a tad more efficient than adding triggers here to copy each and every reveived value to another table (with the same structure).
May I offer a possible solution, if all of the database actions are being "called" by a front end application then another method to maintain an audit trail is to simply append and database change details to a ascii log file (simple text file).
For example where I work if anybody makes a change to any database field the action is recorded also to the log file.
Then if we need to access who made the changes and what changes were made on any particular date in time, even years ago it's a simple matter to check the log file. When it get's too big archive it and start a new one.
You get the values you need to save from the front end application, what I mean to say is the values you are using in the database insert or update query are also used for the text report audit trail. In the case of the original values that are being replaced, they are stored as a variable when the update/insert page is first populated.
Yes that's right, you keep the values you are inserting/updating/deleting then call a routine to record the changes in the ascii text audit file.
Re Getting data from the DB, I'm sorry but I don't know how to retrieve data from SQLServer from past transactions, I'm sure it's possible from the transaction log. But in my circumstances it's been easier to just store the old values and new values when they happen to variables then send to the ascii log file.
in my database table , i have one field (say field1) which contains the value like 1,2,3,10,11,20, a, b,c , 30, 40. I want to write a query which sore the result on the base of field1 and output should be like
whether it is possible through query or i need to adjust in code ?
Yhat should be possible using a query. Add a computed field that indicates whether it's a number or a range of characters, and put that as the first column to sort on. Or check out the collations if you're on Sql Server - might be as easy as changing the setting and have the server order everything in the correct way for your locale.
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names.
It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
None I know about, but you can select a range from the sheet like: SELECT * FROM [Sheet1$A4:F1243] Which could solve your problem.
But you would need to know, or find out, how many rows there are before doing the select.
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget).
Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g. ['sheet 1$'] and I have been unable to get that to work with a range.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''. System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.
Just for testing I renamed the sheet to remove the SPACE and it works.