Category: Batch File Scripting

Powershell issue with output characters changing to ?

Published September 29, 2019 / by Chris Smith / Leave a Comment

On PowerShell command pipe to export CSV add Encoding Unicode if characters change to ?
Credit: Idera

execute xp_cmdshell 'powershell -Command "Get-ChildItem -File -Path @path -filter *.* -Recurse | ForEach-Object {[PsCustomObject]@{Fullname = "$($_.FullName)"; LastWriteTime = "$($_.LastWriteTime)"}} | Sort Fullname -Descending | Export-Csv -Path @CSVFile -NoTypeInformation -Encoding unicode"'

1	execute xp_cmdshell 'powershell -Command "Get-ChildItem -File -Path @path -filter . -Recurse \| ForEach-Object {[PsCustomObject]@{Fullname = "$($_.FullName)"; LastWriteTime = "$($_.LastWriteTime)"}} \| Sort Fullname -Descending \| Export-Csv -Path @CSVFile -NoTypeInformation -Encoding unicode"'

If using BULK INSERT ensure widechar is used (It will work but TSQL will add notices which may add pointless text to log files)

BULK INSERT #bcp_file_mem
                FROM @CSVFile
                                WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK);

BULK INSERT #bcp_file_mem

FROM @CSVFile

WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK);

Posted In Batch File Scripting | SQL Server | SQL Server 2008 | SQL Server 2008 Express | SQL Server 2012

Maintain regtrans-ms and blf files generated by Georgia UTS sessions

Published April 14, 2019 / by Chris Smith / Leave a Comment

A little script I schedule for servers using Georgia UTS.
Below I use Robocopy to move files older than 3 days, to avoid anyone currently logged in. For me I expect users to be logged on for no more than a day, active user files should therefore be untouched.

echo This Batch Files removes .regtrans-ms and .TM.blf which can take up space if unmaintained
echo Change to C drive to ensure correct
%systemdrive%
cd \
echo Create temporary directory
mkdir deltempfiles
echo Copy temporary files into temp directory
ROBOCOPY C:\Users\ C:\deltempfiles\ *.regtrans-ms /move /minage:3 /S /R:1 /W:1 /XJ
ROBOCOPY C:\Users\ C:\deltempfiles\ *.TM.blf /move /minage:3 /S /R:1 /W:1 /XJ
echo Cleanup
rmdir C:\deltempfiles\ /S /Q
echo Complete
exit

echo This Batch Files removes .regtrans-ms and .TM.blf which can take up space if unmaintained

echo Change to C drive to ensure correct

%systemdrive%

cd \

echo Create temporary directory

mkdir deltempfiles

echo Copy temporary files into temp directory

ROBOCOPY C:\Users\ C:\deltempfiles\ *.regtrans-ms /move /minage:3 /S /R:1 /W:1 /XJ

ROBOCOPY C:\Users\ C:\deltempfiles\ *.TM.blf /move /minage:3 /S /R:1 /W:1 /XJ

echo Cleanup

rmdir C:\deltempfiles\ /S /Q

echo Complete

exit

Posted In Batch File Scripting | Georgia Universal Terminal Server

Georgia Telnet or SSH generate a list of users for GS_L_User.txt

Published June 30, 2018 / by Chris Smith / Leave a Comment

Georgia Telnet can restrict the number of users that connect to Telnet or SSH.
I am lazy so I tend to generate a list based off our user logon scripts folders, however I do of course take a backup and test it afterwards.

echo Change to the drive where your Georgia logon scripts are located
d:
echo Change to the folder to where your Georgia logon scripts are located
cd D:\Datalinx\GS_UTS\scripts
echo Get the dir command to write out a simple list of directories into a file called dir.txt
dir /B /A:D >dir.txt
echo add a 1 to end of each row and copy the list into the file GS_L_User.txt located normally a directory above the user script folders.
echo It takes place straight away

echo Change to the drive where your Georgia logon scripts are located

echo Change to the folder to where your Georgia logon scripts are located

cd D:\Datalinx\GS_UTS\scripts

echo Get the dir command to write out a simple list of directories into a file called dir.txt

dir /B /A:D >dir.txt

echo add a 1 to end of each row and copy the list into the file GS_L_User.txt located normally a directory above the user script folders.

echo It takes place straight away

Additional information:
Georgia Telnet Manual and Logon Features

Posted In Batch File Scripting | Georgia Universal Terminal Server

Creating a shortcut to a non existent location on Windows

Published May 13, 2018 / by Chris Smith / Leave a Comment

Credit: ServerFault
Credit: Craig Tolley
I came across the need to create a shortcut to a non existent file share (Where a wireless network had a different ip range on a different site). I thought Windows would let shortcuts for SMB shares be created if they didn’t exist, but Windows 10 insisted the share had to exist before being created.
ServerFault has a nice little VBS script (Although it created the shortcut, when run it didn’t close the Microsoft Script Host program. I just closed the command prompt window after)

Set oWS = WScript.CreateObject("WScript.Shell")
sLinkFile = "C:\MyShortcut.LNK"
Set oLink = oWS.CreateShortcut(sLinkFile)
oLink.TargetPath = "C:\Program Files\MyApp\MyProgram.EXE"
oLink.Save

Set oWS = WScript.CreateObject("WScript.Shell")

sLinkFile = "C:\MyShortcut.LNK"

Set oLink = oWS.CreateShortcut(sLinkFile)

oLink.TargetPath = "C:\Program Files\MyApp\MyProgram.EXE"

oLink.Save

The forum post shows it has been run on Windows 7, I have run this on Windows 10 Enterprise 64 Bit 1607 and created the shortcut on the desktop

After finding another use for the code I ran into an issue adding parameters to the shortcuts.
I wasn’t aware “Arguments” can be used, Craig Tolley has a nice post on how it is used.

Set oWS = WScript.CreateObject("WScript.Shell")
sLinkFile = "C:\MyShortcut.LNK"
Set oLink = oWS.CreateShortcut(sLinkFile)
oLink.TargetPath = "C:\Program Files\MyApp\MyProgram.EXE"
oLink.Arguments = "-argument1 -argument2 option"
oLink.Save

Set oWS = WScript.CreateObject("WScript.Shell")

sLinkFile = "C:\MyShortcut.LNK"

Set oLink = oWS.CreateShortcut(sLinkFile)

oLink.TargetPath = "C:\Program Files\MyApp\MyProgram.EXE"

oLink.Arguments = "-argument1 -argument2 option"

oLink.Save

Posted In Batch File Scripting

Batch script to help with launching files for correct version of Windows

Published January 20, 2018 / by Chris Smith / Leave a Comment

We encountered an issue during a migration to Windows 10 where I had missed a program which had hard coded links, these no longer worked due to Microsoft Access being a newer version.
I generated a simple script so we could change the hard coded links to allow it to select the correct version, all we do is call the script and add the access filename. It only handles Windows 7 and 10, but you can easily add you own section before goto sub_wrongos by using version numbers from MSDN. It can also be amended to launch something else.
I used start instead of call or simply the command line as this would leave the Windows Command Prompt window in the background until the application it launched was closed.
This isn’t ideal and meant to be temporary, so when fully migrated we will hard code them back to our Office 2016 versions of Access Front ends.

	REM This script will need to be passed an Access Front End Name, it will then based on the Windows version launch the correct Access Front End
	REM Call with <scriptname.bat> <AccessFrontEndName> without an extension (Assume compiled as an accdr)
	set arg1=%1
	REM Check Windows Version is 10
		ver | findstr /i "10\.0\." > nul
		IF %ERRORLEVEL% EQU 0 goto sub_windows10
	REM Check Windows Version is 7
		ver | findstr /i "6\.1\." > nul
		IF %ERRORLEVEL% EQU 0 goto sub_windows7
			goto sub_wrongos			
:sub_windows10
	ECHO Windows 10 version launching
	start <Accessfilelocation>\%1.accdr
	EXIT
:sub_windows7
	ECHO Windows 7 version launching
	start <Accessfilelocation>\%1.accdr
	EXIT
:sub_wrongos
	ECHO THIS SHOULD NOT SHOW!
	EXIT

REM This script will need to be passed an Access Front End Name, it will then based on the Windows version launch the correct Access Front End

REM Call with <scriptname.bat> <AccessFrontEndName> without an extension (Assume compiled as an accdr)

set arg1=%1

REM Check Windows Version is 10

ver | findstr /i "10\.0\." > nul

IF %ERRORLEVEL% EQU 0 goto sub_windows10

REM Check Windows Version is 7

ver | findstr /i "6\.1\." > nul

IF %ERRORLEVEL% EQU 0 goto sub_windows7

goto sub_wrongos

:sub_windows10

ECHO Windows 10 version launching

start <Accessfilelocation>\%1.accdr

EXIT

:sub_windows7

ECHO Windows 7 version launching

start <Accessfilelocation>\%1.accdr

EXIT

:sub_wrongos

ECHO THIS SHOULD NOT SHOW!

EXIT

Posted In Batch File Scripting

Generate a text file with subfolders in a location on explorer

Published January 20, 2018 / by Chris Smith / Leave a Comment

I needed to generate a list of folders and their subfolders, I used a simple command for dir command and slapped into a text file.
Below replaced with the folder you want to get the list from (i.e C:\Users\Default) and replace with where you want to save a textfile (i.e C:\textfile.txt).

dir /b /s /a:d "<Location>\*" > <TextFile>

1	dir /b /s /a:d "<Location>\*" > <TextFile>

Credit: SS64

Posted In Batch File Scripting

Display advanced properties of scheduled tasks in command line

Published July 22, 2017 / by Chris Smith / Leave a Comment

The following command requests a detailed display of the tasks on the local computer. It uses the /v parameter to request a detailed (verbose) display and the /fo LIST parameter to format the display as a list for easy reading. You can use this command to verify that a task you created has the intended recurrence pattern.
Before running increase the command prompts screen buffer (Right click the Window, go to properties, go to Layout and set the Screen Buffer Height to 9999). If this is not done you will find due to the amount of Windows scheduled tasks, that the tasks you want are cleared from thje screen buffer.

schtasks /query /fo LIST /v

1	schtasks /query /fo LIST /v

SchTasks.exe displays a detailed property list for all tasks. I normally will then copy everything into the clipboard (Ctrl + A) and pout it into a text editor, I then delete anything after “Folder: \Microsoft” so I then only have my list of scheduled tasks.

Credit: Technet

Posted In Batch File Scripting | Windows Server 2008 | Windows Server 2012

A simple batch file which compresses every folder into a 7z Archive

Published July 22, 2017 / by Chris Smith / Leave a Comment

For backing up old files I tend to sort into lots of different folders, anything I am finished with I put in a 7zip Archive
The following batch makes use of a For loop in the batch, uses 7-zip with Ultra Compression and names the archive the same as the folder.

for /D %%X in (*) do "c:\Program Files\7-Zip\7z.exe" a -mx9 "%%X.7z" "%%X\"
pause

1 2	for /D %%X in (*) do "c:\Program Files\7-Zip\7z.exe" a -mx9 "%%X.7z" "%%X\" pause

Credits: SS64

Posted In Batch File Scripting

Rebuild a V1 DBCapture Database Queue

Published July 16, 2017 / by Chris Smith / Leave a Comment

We have an issue where on a rare occasion a scan from V1 DBScanner to DBCapture can corrupt the queue and cause DBCapture Admin to state it is unable to open a document rendering the whole application unusable.
I have a batch script to perform some maintenance. Over time I have commented out lines which are not required. I have left my change drive to d script as this is normally where server applications are installed. I have replaced our port with as this can be different per install (As well as having more than once instance of the service on different ports)
After the queue is rebuilt the program actually starts the server as normal, in my batch the program is executed in a seperate process and then killed off after 400 seconds (As long as the rebuild is complete then V1 is fine with this, if running line by line press Ctrl + C to close the program instead). Depending on the amount of documents in the queue it may need to be increased.

ECHO *********************************
ECHO * Restarting DBCapture Services *
ECHO *********************************
ECHO *********************************
ECHO * Change to drive D             *
ECHO *********************************
d:
ECHO *********************************
ECHO * Stopping V1 Services          *
ECHO *********************************
REM net stop DbADepServer
REM net stop DbCSArchiveServer-<port>
REM net stop DbADepServer-<port>
REM net stop DbCSAuthServer-<port>
net stop DbCapComms-<port>
net stop DbCapOCRServer
REM net stop DbCapturePDFFileWatcher
net stop DbCSCaptureServer
net stop DbCSCaptureServerExport
REM net stop DbCheckFilewatcher
REM net stop DbLoginServer
REM net stop DbWebQLiveCacheClean
REM net stop DbArchiveCacheClean
ECHO *********************************
ECHO * Rebuild DBCapture Queue       *
ECHO *********************************
start "DBCapture" "<path to dbcapture communications server>\dbcapcomms.exe" -debug <port> -queueupdate
ECHO *********************************
ECHO * Waiting 400 seconds           *
ECHO *********************************
rem Credit For lower CPU usage, instead of using WAIT use Ping as suggested on http://ss64.com/nt/timeout.html
PING -n 401 127.0.0.1>nul
ECHO *********************************
ECHO * Kill of DBCapture program     *
ECHO *********************************
Taskkill /IM "DbCapComms.exe" /F
ECHO *********************************
ECHO * Starting V1 Services          *
ECHO *********************************
REM net start DbLoginServer
REM net start DbCapComms-<port>
REM net start DbCheckFilewatcher
REM net start DbWebQLiveCacheClean
REM net start DbArchiveCacheClean
REM net start DbADepServer-<port>
REM net start DbCSArchiveServer-<port>
REM net start DbCSAuthServer-<port>
net start DbCapComms-<port>
net start DbCapOCRServer
REM net start DbCapturePDFFileWatcher
net start DbCSCaptureServer
net start DbCSCaptureServerExport
ECHO *********************************
ECHO * Complete Exiting              *
ECHO *********************************
EXIT

ECHO *********************************

ECHO * Restarting DBCapture Services *

ECHO *********************************

ECHO * Change to drive D *

ECHO *********************************

ECHO * Stopping V1 Services *

ECHO *********************************

REM net stop DbADepServer

REM net stop DbCSArchiveServer-<port>

REM net stop DbADepServer-<port>

REM net stop DbCSAuthServer-<port>

net stop DbCapComms-<port>

net stop DbCapOCRServer

REM net stop DbCapturePDFFileWatcher

net stop DbCSCaptureServer

net stop DbCSCaptureServerExport

REM net stop DbCheckFilewatcher

REM net stop DbLoginServer

REM net stop DbWebQLiveCacheClean

REM net stop DbArchiveCacheClean

ECHO *********************************

ECHO * Rebuild DBCapture Queue *

ECHO *********************************

start "DBCapture" "<path to dbcapture communications server>\dbcapcomms.exe" -debug <port> -queueupdate

ECHO *********************************

ECHO * Waiting 400 seconds *

ECHO *********************************

rem Credit For lower CPU usage, instead of using WAIT use Ping as suggested on http://ss64.com/nt/timeout.html

PING -n 401 127.0.0.1>nul

ECHO *********************************

ECHO * Kill of DBCapture program *

ECHO *********************************

Taskkill /IM "DbCapComms.exe" /F

ECHO *********************************

ECHO * Starting V1 Services *

ECHO *********************************

REM net start DbLoginServer

REM net start DbCapComms-<port>

REM net start DbCheckFilewatcher

REM net start DbWebQLiveCacheClean

REM net start DbArchiveCacheClean

REM net start DbADepServer-<port>

REM net start DbCSArchiveServer-<port>

REM net start DbCSAuthServer-<port>

net start DbCapComms-<port>

net start DbCapOCRServer

REM net start DbCapturePDFFileWatcher

net start DbCSCaptureServer

net start DbCSCaptureServerExport

ECHO *********************************

ECHO * Complete Exiting *

ECHO *********************************

EXIT

Credits:
SS64 for recommending Ping instead of Wait – SS64
Datel Support for providing the command to rebuild the queue – Datel

Posted In Batch File Scripting | V1 Document Management

Simple error checking when running batch files from T-SQL in SQL Server

Published January 14, 2017 / by Chris Smith / Leave a Comment

A while ago we added error trapping to the batch file called by a SQL script, this was to ensure if a program failed the rest of the process would not run:

After ensuring the program would return an error level if something went wrong we then I then used the ErrorLevel and an if statement. If the error level is equal to or greater than 1, I make it go to the end of the file and exit with code 1 (By default its zero which normally means the batch or program executed as expected)

rem Batch Code here must support returning errors otherwise it will be assumed to have run without issue
echo Error Level %ERRORLEVEL%
IF ERRORLEVEL GEQ 1 GOTO EOF
rem add any further Batch Code here
echo Error Level 0 so closing
exit
:EOF
echo Reached End of file due to program error
exit /b 1

rem Batch Code here must support returning errors otherwise it will be assumed to have run without issue

echo Error Level %ERRORLEVEL%

IF ERRORLEVEL GEQ 1 GOTO EOF

rem add any further Batch Code here

echo Error Level 0 so closing

exit

:EOF

echo Reached End of file due to program error

exit /b 1

We then modified our existing code in SQL to ensure the batch program is run and the exit code is captured in a variable. It can then be dealt with and the example below can easily be changed to suit whatever is required.

----------------------------------------------------------------------------------------------------------
print 'Running Windows Batch Script'
----------------------------------------------------------------------------------------------------------
--Capture the exit code into @result and run the batch script
DECLARE @result int
       EXEC @result = xp_cmdshell '<Patch_to_Batch_Script>'
--Only one command can be run in this if statement if more than one required consider a GOTO command!
              IF (@result = 0)
--Run if program ran fine
                     EXEC xp_cmdshell '<Patch_to_Batch_Script>'
--Run if program did not run fine
                     ELSE '<Patch_to_Batch_Script>'

----------------------------------------------------------------------------------------------------------

print 'Running Windows Batch Script'

----------------------------------------------------------------------------------------------------------

--Capture the exit code into @result and run the batch script

DECLARE @result int

EXEC @result = xp_cmdshell '<Patch_to_Batch_Script>'

--Only one command can be run in this if statement if more than one required consider a GOTO command!

IF (@result = 0)

--Run if program ran fine

EXEC xp_cmdshell '<Patch_to_Batch_Script>'

--Run if program did not run fine

ELSE '<Patch_to_Batch_Script>'

Credits:
SS64
Microsoft MSDN (Example Set C)

Posted In Batch File Scripting | SQL Server

CJSmith dot me

I dump stuff I find useful here

Category: Batch File Scripting

Powershell issue with output characters changing to ?

Maintain regtrans-ms and blf files generated by Georgia UTS sessions

Georgia Telnet or SSH generate a list of users for GS_L_User.txt

Creating a shortcut to a non existent location on Windows

Batch script to help with launching files for correct version of Windows

Generate a text file with subfolders in a location on explorer

Display advanced properties of scheduled tasks in command line

A simple batch file which compresses every folder into a 7z Archive

Rebuild a V1 DBCapture Database Queue

Simple error checking when running batch files from T-SQL in SQL Server