Clear All Transaction Data with SQLCMD and Batch File

Version 4
15

@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.

Pasted image1128×588 161 KB

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
2 Likes