Best way to implement external Log file

Hi,

I am developing application for our clinet.
some of requirements are system failure scenarios.
SQL Server is generating numbers for items inserted in some tables ( something like "oridinal number / year". logic for this is specified by my client and is somewhat complicated ) . most important requirement is to have external( usb stick on the server for example ) log file with latest numbers generated so that in the case of system failure it can be red and work can be countinued on the paper( with countinuing numbers) until system recovery.
Ideally, write it should be done inside of number generating and record inserting transaction.
There is avarage of 1 number generated per minute, but there is also a batch insert option for inserting thousands of records which must have numbers generated.

What is the best way to implement this:
-new data file on usb stic (external hard disk?) and single table in that data file? What about speed of usb stick. i suppose that usb stick with caching on can be fast enough (we are talkong about few rows only).
-maybe to configure SQL Server log to be written on that usb stick?
-some of the offices may have Internet access and some may not, so sending numbers via e-mail is not an option

-maybe some new MS SQL Server 2005 feature?.
What is the best practice in this case?

PS.
I know this sounds like an MCP exam, so, come on, MCSD's nad MCDBAs ;)

Hi,

if this is business critical I would suggest you to harden the availbility o your SQL Server instead :-). The idea about the USB stick sounds fancy but wouldn′t be professional at all. So use a job which reads periodically the numbers from that table and persit that in a log (I assume that you numbers dont have to be machine-readable, so the option with the logfile of the job is sufficient for you). otherwise if it shoudl be machine-readable, you could use a job which uses SQLCMD / or OSQL (depending on your version) does a export of some data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I understans what are you saying, but i need to conform to specs i recieved. They have some old-school requsts that they think are robust. I thik it is becouse they were thinking it will be done in older and less reliable system than SQL Server.

Job is not suitable becouse there will be lost data if SQL Express goes down. It needs to be done within insert transaction.

|||

ok, then you would need to implement something which purges out a log within OSQL.

Using a trigger / or within your stored procedure, depends on how you insert the data in the table) which will use sort of the following code:

OSQL -Q"SELECT Yournumber from youtable" -SServername -O"C:\yourlogfile"

Keep in mind that triggers react asynchronously, so the transaction will nlock the rest of the system unless the process comes back with either an error message or the successfull execution of the command.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de