Hi Guys
I have been using SambaPos to test and now I am ready to open my store and use it live. However there are a ton of fake transactions and so the accounts, inventory and sales reports are all showing test transactions. I want to reset everything to zero but not loose my product database and recepies.
Is there any easy way to do this?
Thank you
I use the following 2 files to clear transactions with SQL Express, keeping all menus, products, and setups. It will not remove Entities or any accounts linked to an Entity like Customer Accounts - but will wipe all account transactions.
Please note that this is destructive and can not be reversed once run !!! The script will backup your database, but only if the SambaPOS SQL Instance is running as a Local System account.
Always backup your database first.
DELETE V4 DATA.bat
@echo off
REM Set your Server and Database names here.
set DATABASENAME=SambaPOS45
set SERVERNAME=localhost\SAMBAPOS
set DATESTAMP=%DATE:~4%
set BACKUPFILENAME=%CD%%DATABASENAME%-%DATESTAMP%.bak
pause
echo – BACKUP DATABASE –
sqlcmd -E -S %SERVERNAME% -d master -Q “BACKUP DATABASE [%DATABASENAME%] TO DISK = N’%BACKUPFILENAME%’ WITH INIT , NOUNLOAD , NAME = N’%DATABASENAME% backup’, NOSKIP , STATS = 10, NOFORMAT”
echo – CLEAR DATABASE –
SQLCMD -S %SERVERNAME% -d %DATABASENAME% -i V4_dtrans.sql
pause
V4_dtrans.sql
DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [CostItems]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [Orders]
GO
DELETE FROM [PaidItems]
GO
DELETE FROM [PeriodicConsumptionItems]
GO
DELETE FROM [PeriodicConsumptions]
GO
DELETE FROM [ProductTimerValues]
GO
DELETE FROM [Payments]
GO
DELETE FROM [WarehouseConsumptions]
GO
DELETE FROM [WorkPeriods]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = ‘[{“S”:“Available”,“SN”:“Status”}]’ Where EntityStates like ‘%Status%’
GO
I’m adding this post just to create links between topics.
Will this work for me with the default DB type? I setup sambapos 4 from exe file and did not change the DB type, as such, I am not sure which type of DB it uses by default.
It was designed for SQL Express (SQ), but should work with Compact SQL (CE).
I only run SQL Express because most of the time I need to run in multiuser mode.
Determining Database Mode
Double Click SambaPOS Logo to switch to window mode and check window title. At the end of the version number you’ll see one of these three suffixes.
TX: Text File Mode
CE: Single User Mode (SQL Server CE 4.0 is working)
SQ: Multi User Mode (SQL Server working)
Thank you JohnS.
Mine is using CE.
When I run this I get an error, “sqlcmd” is not recognized as a valid command. Something to the extent. Seems it is not recognizing sqlcmd from the command line.
IN SIMPLE WORDS
FIRST YOU CREATED A BACKUP THEN CLEAR THE TRANSACTIONS
IF WE JUST RUN THE .SQL SCRIPT WILL IT BE ENOUGH
LIKE:
DELETE FROM dbo.TicketEntities
GO
DELETE FROM dbo.Tickets
GO
Compact SQL needs a different command line program to run the scripts.
This was another thread that covers CE running on WinXP -> Takeaway Orders
Well i am in the same boat as this and need to delete as i also was doing testing. While keeping everything apart from the dummy accounts that were made from my testing.
My problem is i have no idea what you are all explaining in what to do 
Cheers.
Am just doing one base terminal on my PC, as for just doing the bills for each table.
Thats it.
Cheers.
@ emre…No mate, but not really sure what i am doing to be honest. As i do not want to delete all the work on the products i have put in, took a while to add all my menu. So a tad apprehensive.
Cheers all.
Install Database Tools Module
Database Tools on Settings ScreenClear Database Transactions on the list click Create Default Tasks button.Clear Database Transaction and click Execute
That will keep your menu configuration, customers, tables but clear all tickets, account transactions, inventory transactions, etc…
Wow. Did not realise Clear Database Transactions feature was available until now. Nice…
About DELETE V4 DATA.bat
Depending on the windows local settings, DATE will give different results like
2014-03-31 or 31-03-2014
or worst 31/03/2014 where “/” will be interpreted as a path and not as part of the backup file name.
This will create an error and no backup.
In my case (typing “DATE” in cmd gives “31/03/2014”) I modified:
set DATESTAMP=%DATE:~4%
into
set DATESTAMP=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%
which save the backup as XXX-2014-03-31.bak
Hi everyone,
Using the script I faced various errors in the Backup process. Two things needed to be done to fix them:-
Firstly error : MSG 3201 , Level 16 error
Solution: Assign a folder for the Backup file i.e.: where the script is. Then ensure that NT System/MSSQL account is added to the Security tab and given Full Access privilege.
Second error: DATESTAMP value produces wrong results due to locale difference.
Solution I overcame this by assigning the following as DATESTAMP i.e.:
set DATESTAMP=%DATE:/=-%@%TIME::=-%
Obviously this creates a filename that has both and Date and Time value. Hope this helps anyone facing similar issues.
I’m using pos4 app 4.1.82 can I purchase the database tools doesn’t seem to appear on my samba markey