sql server programming: tbh I don't know why I bother nowadays

ok

i have a trigger a nice trigger it is for horizontal partitioning it
is 700 lines (really big database) so I am not posting it not that it
is necessary the only valuable information it is acts on when a record
is updated and a certain value is added to a column in the database
the trigger then acts and horizontal partitions that record sticking
it in a second part of the database basically a mirror for old data
that the "boss" doesn't want delete ... ever.

of course I convinced the boss that the computer is not an infinite
storage place and now the "boss" (and I am using this term lightly at
this point) wants to be able to extract all information from the
database on a certain record set (this set is a project and all data
related to that project) and stick it in a file so she can then burn
it to a cd ... or dvd depending on how big it is. But wait there is
more the "boss" wants to be able to import it back into the system at
any time if the data on that project suddenly becomes relevant for god
knows what reason.

I need a bit of sql that I can add to that trigger that will write all
that data to a file with insert statements and what not that are
related to that projectid. that or I add it somewhere else the thing
is i need to either put it in a trigger where I can update a value
like I am doing for the horizontal partitioning trigger or give the
sql to the interface guy and tell him to add it to the interface to be
processed at the click of a button on the interface.

Regards
The guy with the gun hunting his "boss"
Kelvan

[quoted text, click to view]

Couldn't you use partitioned view (2000) or partitioned table (2005) for
that?

[quoted text, click to view]

Use BACKUP or BCP for example. It makes no sense at all to do that from a
trigger IMO.

--
David Portas

Lord Kelvan (the_iddiot@hotmail.com) writes:
[quoted text, click to view]

If the data size is not bigger than it can fit on a DVD, why even bother?
Or you are running MSDE or SQL Express and have keep within the database
size limits?

[quoted text, click to view]

So the idea is to archive the data as INSERT statements? First of all,
don't to this from the trigger. A trigger is meant for operation that
must be carried out as part of the transaction defined by the statement
that fired the trigger, and this obviously isn't. As I understand it,
a set of data is marked for archiving. Then a process at some later
point could perform the archiving operation.

That process could run from SQL Server Agent or be started manually.
Whatever, it should not run from T-SQL, because T-SQL is not meant
for writing to files.

As for which form to archive the data, this requires some consideration.
You appear to be considering INSERT statements. But what if the schema
has changed when you need the data in two years? Maybe it would be better
to move the data to a second database with table and all, and the you
can just write that database file to the CD or DVD. Just keep in mind
that if you need it in 10 years from now, the version of SQL Server you
have then, may not support attaching a database of the version you have now.

How big is the total database? Maybe it the simplest is to take a full
backup of the database, send the backup to the archive and the purge the
rows from the database. Of course the same caveat about what may happen
in 2017 still applies.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

well it just needs to be able to be run from a interface command the
database will be very large most likly 20000 records total over all
tables per project and upto 50 projects say every six months basically
it will grove very fast and since the database is storing some bery
large fields + the intercafe is storing files on each project (file
path is stored in the database) my estimations is 8 years till space
is run out.

i am not going to be there in 8 years to bail them out when this
happens i am just a acting database engineer they wont have anyone
administring it so i also have built some automatic sql agent commands
to administer the database as it stands the scheema will not alter
mainly cause no one there can do it but the database will be well
documented if someone wants to alter the scheema and if they do the
documentation will tell them what else they need to alter to
compensate so that is not a major problem and if this does not happen
it is not my problem i just have to make this work.

i know that php (the interface system being used for this extranet
system) is capible of running sql procedures so would there be a
procedure that i can have which will do all this. as i said it needs
to be able to be done from the interface side of things one way or
another as there will be no one on the back end to deal with it.

Lord Kelvan (the_iddiot@hotmail.com) writes:
[quoted text, click to view]

And no one is going to buy more hardware in those eight years?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

no had brought new harware for the server i am putting it on for the
past 8 years no one else uses it if they want to buy more hdds their
choice regardless of the fact either i do this or i take it out of
scope of the database and let some other poor shmo deal with it when
the hdd gets full

regards
kelvan

cough that and they have 4 hdds in there all ready two raided and the
other two are a raided mirror of the raided two

it is a well put together server as far as i can tell that has never
been used -_-