Clearing out Sales Nightly

Version 4 V4 Question
1

Hello,

I found the script to clear out sales. What do you guys recommend is the most efficient way to do run the query to clear out all sales history and reports nightly.
I can think of 2 ways:

  1. Setup a Job to run the query on a nightly schedule through ms sql server (full version only)
  2. Setup the query to run nightly through sambaPos with triggers to run nightly at 3am. Not so familiar with this setup just a conclusion I formed by looking through SambaPos.
2

Unless you will keep SambaPOS running 24/7, you will need to schedule this by other means. I suggest using Windows Task Scheduler to run a BAT file at a set time.

@echo off
cls

set SName=localhost\sqlexpress
set DbName=SambaPOS4
set SQL=cleardb.sql
set ERRFILE=_ErrFile_Report.txt

D:
CD\
CD D:\Programs\POS\SALES

if exist %ERRFILE% del %ERRFILE%

@echo on
sqlcmd -S %SName% -E -d %DbName% -I -i %SQL% >> %ERRFILE% 2>&1
::@notepad %ERRFILE%

:end
1 Like
3

So I can just run sql quaries through windows without having to purchase a full version of sql server to use their agent to run the scripts. Will the windows method work just as well as the agent through ms sql server?

4

You do not have to purchase SQL server at all… SQL Express works just fine and its free. Yes you can run SQL scripts with SQL Express.

5

Really are you sure, I searched for hours a little while back and found out from various forums that you would need sql server agent which only comes with the full version of sql server.

How would you do it through sql server express?

7

That is an automation service… you can run scripts with .bat files… that is two different things.

8

@Hasa, you don’t need the full version or the Agent as @Jesse mentioned.

SQL Express comes with command-line utilities that allow you to run scripts and even perform full DB backups. Use the commands in a BAT file like I showed above, and schedule the BAT file using Windows Task Scheduler.

The command utilities are located in a path similar to:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

Search for information on how to use them - here are a few examples:

sqlcmd
osql
bcp

Backup the DB:

osql.exe -S %SERVERINSTANCE% -E -Q "BACKUP DATABASE [%DBNAME%] TO DISK='%pathBU%%BackupName%'"

Run a SQL script:

sqlcmd.exe -S %SName% -E -d %DbName% -I -i %SQL%
4 Likes