Deleting Sales Receipts - Which tables get cleared?

I need to reduce my db size...

Many have posted about the Retail Realms archiving utility. This sounds like 
a great app, but since I'm somewhat familiar with the db schema in RMS and 
know SQL, I would like to attempt it on my own.

Has anyone tried removing sales history through SQL? What tables should be 
cleared, does anyone have a script that they use?

I understand that a backup should be made prior to any changes :)

Paul
0
Paul297 (622)
1/28/2006 4:46:02 PM
pos 14173 articles. 0 followers. Follow

2 Replies
425 Views

Similar Articles

[PageSpeed] 33

Paul,
To see what tables are taking up all the space run this query:
-------------------------
SELECT [total size KB], B.rows, O.name
FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
[total size KB], id
FROM sysindexes group by id)
A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
ORDER BY [total size KB] desc

Usually you want to reduce the size on the Journal table.
Run this query to do so:
------------------------
Delete from journal where time < '10/01/05'
( Change the date to remove anything before that date)

Hope this helps,
Casey Hanson
New West Technologies

Process credit cards anywhere on your PDA or Cell Phone - no database
needed:
http://www.newestech.com/POS/ranger.htm


Paul wrote:
> I need to reduce my db size...
>
> Many have posted about the Retail Realms archiving utility. This sounds like
> a great app, but since I'm somewhat familiar with the db schema in RMS and
> know SQL, I would like to attempt it on my own.
>
> Has anyone tried removing sales history through SQL? What tables should be
> cleared, does anyone have a script that they use?
>
> I understand that a backup should be made prior to any changes :)
> 
> Paul

0
chanson (56)
1/29/2006 9:36:35 PM
Thanks!
I'll give it a try...I appreciate the help :)

Paul


"CaseyHanson" wrote:

> Paul,
> To see what tables are taking up all the space run this query:
> -------------------------
> SELECT [total size KB], B.rows, O.name
> FROM (select sum(convert(decimal(10,0),dpages)*8129/1024)
> [total size KB], id
> FROM sysindexes group by id)
> A INNER JOIN sysindexes B ON A.id = B.id and B.indid in (0,1)
> INNER JOIN sysobjects O ON B.id = O.id WHERE B.rows <> 0
> ORDER BY [total size KB] desc
> 
> Usually you want to reduce the size on the Journal table.
> Run this query to do so:
> ------------------------
> Delete from journal where time < '10/01/05'
> ( Change the date to remove anything before that date)
> 
> Hope this helps,
> Casey Hanson
> New West Technologies
> 
> Process credit cards anywhere on your PDA or Cell Phone - no database
> needed:
> http://www.newestech.com/POS/ranger.htm
> 
> 
> Paul wrote:
> > I need to reduce my db size...
> >
> > Many have posted about the Retail Realms archiving utility. This sounds like
> > a great app, but since I'm somewhat familiar with the db schema in RMS and
> > know SQL, I would like to attempt it on my own.
> >
> > Has anyone tried removing sales history through SQL? What tables should be
> > cleared, does anyone have a script that they use?
> >
> > I understand that a backup should be made prior to any changes :)
> > 
> > Paul
> 
> 
0
Paul297 (622)
1/30/2006 8:19:27 PM
Reply:

Similar Artilces:

how do I change the table from which a form obtains data
I have a form that I have set up, but I would like to re-create it several times and change the table that each copy gets data from. EG Table 1 feeds Form A. I want to copy this form to create form B but it currently gets data from table 1, so I need to change it so that it now gets data from table 2. That's probably clear as mud but hopefully someone can help me! thanks Jo, If all the tables have exactly the same fields, you just need to change the Form's RecordSource. Copy the form to another name, and change the RecordSource table, or more usually, a query against the...

Have Outlook 2000. How do I get rid of spam?
I need step by step directions because I don't know the termenology. When we open Outlook it comes up under my husband's e-mail. Then I Open mine. He is not spamed. I get hundreds each day. HELP! winnieB <winnieB@discussions.microsoft.com> wrote: > I need step by step directions because I don't know the termenology. > When we open Outlook it comes up under my husband's e-mail. Then I > Open mine. He is not spamed. I get hundreds each day. HELP! Step by step: 1) Open Internet Explorer. 2) Browse to http://spambayes.sourceforge.net/ 3) Downoad SpamBaye...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

How do I convert a word table into an excel document?
I have managed to get the info accross no problem but the formatting is all over the place. For instance - 07/10 meaning July 2010 is appearing as 07/Oct despite me going into format cells custom then enter mm/yy which has always worked previously. Any ideas? You can't use it like that regardless of formatting, you need to put in the whole date or else Excel will always assume the current year so any real date used for calculations needs to be numeric and needs a day, so you can enter (assuming US date format) 07/01/10 and use a custom format of mm/yy or if you don't need it for...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Deleted items folder in o2k3
The big boss refuses to empty or archive his deleted items, now holding some 25,000 messages and approaching 3 gigs in size for the deleted items folder alone. Does Outlook treat messages in the deleted items folder any differently than messages in other folders? Would it be better for him to never delete messages since he doesn't really want to delete them? It would be better to not delete them since Deleted Items... is for things you want to get rid of. "Wowbagger" <none> wrote in message news:uKyAPby2GHA.3656@TK2MSFTNGP04.phx.gbl... > The big boss refuses ...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

"x" button enables deletion.... can we put a focus trigger there?
Hi, on three forms, I put focus triggers on the "delete" button because I need to validate some additional data before allowing the user to delete. However, if the user clicks on the corner "x" button to close the form and gets the dialogue box saying they have changes, do they want to "Save", "Delete" or "Cancel", they can delete the record anyways. Is there any way to put a focus trigger on the button of the dialogue box, or on the "x" button? Thanks in advance. I assume you are working with Dexterity. To work with an add...

Publishing Layout and tables
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to copy and paste text from one cell of a table to another cell in the same table. The document is in Publishing Layout. The paste command deletes the text in the destination cell and then places a big empty text box on top of the table. I do dozens of these documents that are primarily tables and graphics. Previously I used Publisher on my old PC. Should I go back, or can this be done in Word for the mac? Hello, On 2010.01.29 8:44 AM, in article 59bb1ce2.-1@webcrossing.JaKIaxP2ac0, "Toni_T@officefor...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Getting font properties
I have a LOGFONT - complete with all desired properties including face name. I use CFont::CreateFontIndirect() to create a font. If the font I have specified is not available on my machine then it uses the nearest available in some appropriate sense. So far so good. But CFont::GetLogfont() just returns me the LOGFONT which I used to create the font, and not the one it actually adopted - in particular as regards face name. Is there an easy way to get the face name of the font it actually used? Dave -- David Webber Author MOZART the music processor for Windows - http://www.mozart.co.uk...

make outlook automatically delete html mail with linked images
The junk mail filter should be able to automatically delete html mail with linked images ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=9e1b783b-c9c1-44f2-a1dd-93da59216c56&dg=microsoft.public.outloo...

Retreive a deleted file
How can I retreive a delted file Hi Janet If you have setup Excel to automatically create backup copies; then check that folder for the last backed-up version....If it is from your PC, check out the Recylcle Bin (on your desktop) and restore the file. If the file was on a network drive you may need to contact your network administrator and check whether they can recover. -- Jacob (MVP - Excel) "Janet" wrote: > How can I retreive a delted file Go into your Recycle Bin. If deleted from there; http://www.ozgrid.com/Services/recover-deleted-files.htm ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Exclude Certain Recipients from Read Receipt
Windows XP Office XP I have a read receipt requested automatically to every email message I send on my business email. I have several contacts from whom I do not want to request a read receipt. Is there a VBA code or something that will request a read receipt to every email message except those addresses which I specify. You can use rules to request receipts and exceptions to the rule for those that don't need them. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other S...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

Where can I get a good holiday booking form for my golf business?
I need a quality Booking Form for my Golf Holiday business on the Costa Del Sol. One which includes sections for accommodation, dates, numbers, flight enquiries and car hire enquiries. Any help would be much appreciated on a tight budget. Thanks Dave Wrong forum. Try templates or doing it yourself. :-) -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Dave" wrote: > I need a quality Booking Form for my Golf Holiday business on the Costa Del > Sol. > One which includes sections for accommodation, dates, numbe...

Tables in 2007
What are the advantages to converting a list to a table in 2007? Why does the table have a name? I did read in help that you can post a 2007 table to sharpoint services. What does that mean? -- Thanks. Confused Hi, Go to the excel help and type Tables then open the one that says Demo: Organize your data by using an Excel table, and then in How to do it, click on Overview of Excel Tables if this helps please click yes, thanks "Confused" wrote: > What are the advantages to converting a list to a table in 2007? Why does > the table have a name? > > I did read...

Frame window does not get displayed properly
Hi All, I have a Dialog based applciation. I want to create a Frame window when clicked on the button on Dialog. The problem is I handled the Painting of the dialog (I draw a picture on so WM_PAINT message I handle.) but doing this I am not able to draw the frame window properly. It appears to be transparent to me . I am setting the CFrameWnd to be the child of CDialog Please Can you let me know how to draw the farme window of how can I have a floting window on a dialog. Thanks Anup I don't know if what you are doing is a good idea. But if you want to have a floating window ins...

How to delete incorrect entries from Dictionary
On occasion, Outlook 2000 recommends a spelling that's obviously incorrect. I suppose that I clicked on a word too hastily. I'd like to remove these incorrect entries but how? tools, options, spelling, edit dictionary (from any app that supports the office spell check) - its also available from the spell check screen when you press F7 or you can search for custom.dic and edit it with notepad. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visu...

Deleting a Batch from company DB table SY00500
This is a multi-part message in MIME format. ------=_NextPart_000_002F_01C96F54.F7568BB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there a way to see all the TRX in a Batch saved in SY00500. Also, what if we delete a batch in SY00500 will there be any other place = to look for; besides this? I had a batch sitting here for a long time = and there were few (3) trxs ( Sales Credits) in it as well. I wonder if I could retrieve those one by one and then delete them as = well as these belong to a fiscal period which is closed plus we...

Payment on Account receipt question
When I apply a payment to customer's account, a receipt prints with an entire history of the customer's purchases. Is there a way to limit this information, as some of my customer's history is quite extensive. Thank you in advance for your help. I would go into the xml for the receipt you are looking forward. You should be able to add another <for> loop in the section that writes out your history to have it stop at an arbitrary number. I haven't tried it, but something like the following, right inside your for loop for history, should work <set name="Hi...

Deleted email not going to Deleted Items folder
I am using Outlook 2003 on WinXP Pro SP2 setup with POP3 accounts. When I delete an email it does not go to the Deleted Items folder - it does not pormpt me it just disappears. The shift key is not being pressed. This also happens when I drag an email to the Deleted Items folder - it just disappears. However, if the email is Unread then it does stay. As soon as I open the email it too disappears. I have tried to upgrade to Office sp2 but is did not fix the problem Change your View, Arrange by settings to All Messages instead of Unread ones. "techie_comps" wrote: > I am usin...

Does table size make a difference in how well Access protects data
I am having a problem that has been going on for several weeks now. I am slowly trying to figure it out. the problem is that Access 2003 keeps deleting records after they are entered. Quick view of database: running Access 2k3 back end and frontends, 12 machines access the DB at any one time, mixture of Win2k Pro and Win XP pro all patches and service packs installed for all OS's as well as Access 2k3. Running Client/server set up controlled by Small Business Server 2k3. database resides on a machine running XP Pro. when new orders are entered into the system, the DB will tell the d...