Use the procedure below to purge Server data from your SQL database. If the database is active, you should delete the records in small "chunks," perhaps 1000 at a time, so that you do not affect the responsiveness of the SQL server for new/ongoing transactions. If you run script after hours or on a test system, you can delete all of the records in larger chunks.
To purge the data
Download the SQL script PurgeEftData.txt.
Open the script in a text editor and edit the following values:
If the database name is not EFTDB, modify the following line with the name of the database: USE EFTDB.
All records BEFORE the "DeleteTo"
date are affected. In the "DeleteTo" variable:
SELECT @DeleteTo =
'1/1/2007', @DeleteScope = '1000'
If
you want all rows to be affected, edit the date and time to some time
in the future.
"DeleteScope" is the number of rows to be deleted and how quickly they are deleted.
SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'
In the "DeleteScope" variable, leave it as '1000' or change it to another static value or a percentage of rows to be deleted.
Examples are commented in the top of the script and shown below.
--SELECT
@DeleteTo = '1/1/2006', @DeleteScope = '50%'
--SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'
--SELECT @DeleteTo = '1/1/2007', @DeleteScope = '2000000000'
Save the script as PurgeEftData.sql.
After you have edited the script as needed, then you can craft the proper command line for "oSQL" to connect to the database:
Open a command prompt. (Click Start > Run. Type cmd, then press ENTER.)
Type the following to execute the sql file:
[path to oSQL]\oSQL.exe -S [server address] -U [username] -P [password] -i "[c:\path\to\PurgeEftData.sql]\PurgeEftData.sql"
For example, type:"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\oSQL.exe" -S 192.168.19.17 -U jbond -P asd123!f$s1 -i "C:\Program Files\GlobalSCAPE\EFT\Reports\PurgeEftData.sql"