Help!!!! Its driving me crazy!!!!!

[yomyom]

Hello all,

I've nearly completed a sales ledger application (using Ms sql and delphi) but one concept is holding me back.

I want to share the idea so I can find out if its alright and how to do it.

Idea: At the end of each year, I would naturally want to store the year's data (sales database) in a directory called 2003 (for this year, 2004 next year ...etc and rename the database 2003 or 2004 etc.) and then delete the tables in the sales database so that fresh data can be captured during the following year. The customer number file and the lastbal tables would not be affected since they point to the firms customers and thier last account balances. All other tables would be collapsed.

The user can then refer to the database stored in 2003 (assuming it works and we're now in 2004), directory to view / print the previous year's transactions but not be able to alter the database(read only).

The problem: How to achieve the above, either through code with delphi or with T-sql (so that I can still perform it from my delphi).

when I do it manually i do the following:
1. switch off the server
2. make a new directory i.e \2003
3. copy sales database i.e sales.mdf and log.ldf to \2003
4. rename the sales database in \2003 i.e sales.mdf to 2003.mdf and sales.ldf to 2003.ldf.
5. turn back on the server
6. use sp_attach_db to place the 2003 database in the server

How can I do it all through code ?
I hope answers will flow....

yomyom.

 

[lovalles]

Well thats all quite interesting but all that it can be done in a Store procedure.
1. You dont need to switch off the server to do all that.
2. use the xp_cmdshell 'mkdir \2003'
3. use the t-sql 'Insert into 2003 select * from sales'
4. To clear all table data 'delete sales'

Thats it!!!
hope this helps.