Clear All Transaction Data with SQLCMD and Batch File

robfromcanada · November 12, 2016, 11:03am

Hello! I would like to clear all transaction data from my SambaPOS4 database using a batch file and SQLCMD. I am following a few examples from the internet and forums, but it doesn’t seem to be making the changes to the database. I am running v4.1.82 with SQ database.

The files I am using are:
-database file: c:\Users\Rob\SambaPOS4.mdf
-batch file: c:\Users\Rob\ClearSambaBatch.bat
-script file: c:\Users\Rob\ClearSambaScript.sql

I run the batch file from CMD line, which calls the script file, and hopefully adjusts the database! After I run SambaPOS and add data, I can see that the database file has been updated, so the location is correct. When I run the script, it seems to connect to the database. The first time I ran the script, the first 3 commands picked up many affected rows. The next time it runs, no rows are affected. When I subsequently run SambaPOS, all the data is still there. I am not sure if the SQLCMD is picking up another copy of the database that was associated with SSMS (I can’t find another copy with an updated date).

Any ideas how to get this to work??

Batch File: ClearSambaBatch.bat

SQLCMD -S LAPTOPASUS\SQLEXPRESS -d SambaPOS4 -i ClearSambaScript.sql

Script File: ClearSambaScript.sql

DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [CostItems]
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
DELETE FROM [Tasks]
GO
DELETE FROM [EntityStateLogs]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = ‘[{“S”:“Available”,“SN”:“Status”}]’ Where EntityStates like ‘%Status%’
GO
DBCC CHECKIDENT (‘WorkPeriods’, RESEED, 0);

emre · November 12, 2016, 3:29pm

I don’t think this is a SQLCMD issue. It will be a good idea to ensure you’re clearing correct database.

QMcKay · November 12, 2016, 3:30pm

It should work by the looks of it.

Keep in mind, SambaPOS should be closed when this is run, because it caches data, and changes to the DB will not be picked up while it is running.

Put a PAUSE statement in your BAT file so you can see the output of the SQLCMD, or direct output to a txt/log file.

robfromcanada · November 13, 2016, 12:07am

Thanks for your response guys! ’

BTW, I have win8.1 installed, and SQL Server 2016 installed. I also close down SambaPOS while working with SSMS or SQLCMD.

From what I can tell, on my system, SambaPOS is requiring that the database be in C:\Users\Rob\SambaPOS4.mdf. SSMS (and maybe SQLCMD) is requiring the database to be in the C:\Program Files\Microsoft SQL Server\blahblahblah\Data\SambaPOS4.mdf. This is why I think that SambaPOS and SSMS/SQLCMD are maybe accessing different physical databases.

If I try to use SSMS to access the database in the other location, I get login errors because I think it’s not picking up the Windows Authentication.

If I edit (or even open) the database in SSMS and then copy the database file (same size) back to the C:\Users\Rob\ directory that SambaPOS likes to use, then SambaPOS won’t load the database. Again I think its somehow due to a login error. If I replace the SambaPOS database with the original file, I still have to go into the Documents\SambaPOS4\ and remove the log file before SambaPOS will open the original database again.

Any further insights?? Would it help if I provide the log files?

JTRTech · November 13, 2016, 12:29am

Is your using SQL express samba doesn’t look at a directory it looks to the service…
If you can’t get in to db with MSSQL manager samba won’t be able to either given the same details.
As a service you specify hostname\instance not directory…

robfromcanada · November 13, 2016, 2:24am

Hi JTRTech, thanks for the fast response.

I am using SQLEXPRESS and the SSMS service is LAPTOPASUS\SQLEXPRESS. But SambaPOS looks to the directory in C:\Users\Rob, and this is NOT the same as where SSMS is looking. I am not convinced that SambaPOS is looking to the service.

How can I verify where it is looking, or which service SambaPOS is looking to??

JTRTech · November 13, 2016, 10:01am

If the connection string has a directory then samba is NOT looking at SQL express

robfromcanada · November 13, 2016, 1:09pm

Hey JTR… I am just trying to understand, so please excuse my ignorance.

The settings in SambaPOS do not have a connection string or a server. It is left blank. SambaPOS is saving the database in C:\Users\Rob and is NOT using the default location for my SQLSERVER which is LAPTOPASUS\SQLEXPRESS.

So, I am guessing that SambaPOS is NOT using SQL Express. Correct?
How can I get it to use SQL Express???

JTRTech · November 13, 2016, 1:18pm

You have to set to connection string to the SQL express, default is blank which is generally localDB which is a single user lite SQL.

Read the SQL setup tutorial kendash wrote it explains it all.

Jesse · November 13, 2016, 1:18pm

Yes it is not using SQL express. It is using localdB version of SQL Express. You need to point it to your SQL server and instance by setting a proper connection string.

robfromcanada · November 13, 2016, 1:54pm

I am having a helluva time with myself and my friend (an IT guy) trying to figure out how to connect and edit my SambaPOS database. We are missing something for connecting to the database properly. We are also living in Laos in Southeast Asia, so there isn’t any expertise out here.

What I need (reasonably fast) is to have a copy of my database with all transactional data and tickets cleared, and all users/settings/menus intact. We can’t seem to do it, and will have too many questions following the guide.

I know you are busy guys (and so am I), but I thought I would ask you if you would be willing to help me out. If I send you my database (57,000k zipped), I am sure you could knock this off in 10 minutes. I don’t mind to send some cash or BTC. Would you be willing to do this??

JTRTech · November 13, 2016, 2:23pm

Its not that hard you post sugests your clearing transactions using the script for the sql express database but your samba is using a localdb database…
Just install the database tools module in trial mode - go to database tools section in manage,create default tasks and run clear transactions task…
This will still leave you with a localdb database but clear the transactions.

robfromcanada · November 13, 2016, 3:21pm

Yeah, I am sure I am just missing something very basic.

Just to clarify, do I have my Data Source / User Id / Password correct when logging into SambaPOS? It seems to be barfing on it. Because in SSMS it asks me to use Windows Authentication (no user/pwd needed), I figure I would use the same user name and windows password. Doesn’t work. I also tried my Administrator / Samba password per the online guide, and that didn’t work either. What am I missing??

.

BTW - thanks so much for your suggestion about the database tools!!! Solved what I needed on an immediate basis, but I would still like to understand more!

JTRTech · November 13, 2016, 3:40pm

mixed mode auth would be username ‘sa’ and pasword set when setting up sql express.
You also need to specify a database name…

QMcKay · November 13, 2016, 10:24pm

@JTRTech is correct. The Connection String will use a SQL Account and Password if you specify those parameters. It does not work with a Windows Account, so you must provide a SQL Account name when specifying parameters for User/Pass in the Connection String. If you do not specify parameters for the User/Pass in the Connection String, then it will revert to using a Windows Account for authentication.

By default, the main SQL User account is named ‘sa’. This cannot be changed. It is also not required if you use solely Windows Auth, so the the sa account might not even exist (yet). When you enable Mixed Auth, you are required to set a Password for the sa Account. You can create other SQL Users if you wish and use those as well, but for simplicity, we do not detail how that is done, and how to give those users access to the DB.

If you do not specify a User/Pass in the connection String, or as a parameter along with SQLCMD, then the connection is made with a Windows Account, and it will only be successful if that Windows Account has access to the DB. So in general, we recommend that your DB is NOT in a “user folder” because of access permissions on user folders. Instead the DB is usually in a more global location, for example:

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\

The best thing you can do for the most flexibility is:

install full SQL Express, not SQL Express LocalDB. This gives you the ability to attach multiple users to the Engine. The LocalDB version does not allow for that.
set up Mixed Mode Authentication, so that you can attach using a Windows Account or a SQL Account.

You can circumvent some of your problems with paths by doing some things with your BAT file.

set pathSQL="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe"
set SERVERINSTANCE=localhost\SQLEXPRESS
set DBNAME=SambaPOS4

set SCRIPTPATH=c:\Users\Rob\
set LOGFILE=SQLCMDlog.txt
set SCRIPTFILE=ClearSambaScript.sql

C:
CD %SCRIPTPATH%

:: use trusted connection (-E) (Windows Authentication)
%pathSQL% -S %SERVERINSTANCE% -d %DBNAME% -E -i %SCRIPTFILE% >> %LOGFILE% 2>&1

:: use SQL Authentication (-U -P)
::%pathSQL% -S %SERVERINSTANCE% -d %DBNAME% -U sa -P sambapos -i %SCRIPTFILE% >> %LOGFILE% 2>&1

robfromcanada · November 24, 2016, 3:07am

Thanks so much for your help everyone! I am learning lots! I got things to work now. I am now wondering if I can create a button within SambaPOS that will execute this script. I will look in the forums to see if anyone has done something like this before.

One further question regarding the clearing of the database using the Tools. Why doesn’t the database filesize reduce drastically after the transactions have been removed??

QMcKay · November 24, 2016, 4:52pm

It depends on the number of transaction records.

A DB with basic setup for Products, Automation, etc. with no Tx Data is something like 20-30 MB in size.

The DB size with Transactions is variable: currently, one system I have is 700 MB, while the other is 200 MB. The larger DB has been in operation for ~5 years, and the smaller one has been in operation ~2 years. It is important to note that the Transactions are not limited to Sales only, they can and do contain other Transaction Types such as Account Payments, etc. so that makes it variable as well… it is not accurate enough to just count Ticket Numbers.

So clearing Tx Data on either of those DBs will reduce their size “significantly”, bringing them down to 20-30 MB.

robfromcanada · November 26, 2016, 2:27pm

My database is up to 593,984 kb, and after clearing is still 457,792 kb.
I don’t use accounts, clients, or credit payments.

QMcKay · November 26, 2016, 2:53pm

It should be much smaller than that.

Are you getting any errors running the script?
Can you tell whether there are errors running it or not?

What happens when you run it manually through SSMS?

How are you doing your measurement? Physical file?
My measurements are of a BAK file, not the MDF.

Jesse · November 26, 2016, 3:44pm

Are you using SQL or CE? Which file are you referring to? I am also wondering what your afraid of with Database size? 400mb is very small.