CJSmith dot me

I dump stuff I find useful here

Category: Batch File Scripting

Powershell issue with output characters changing to ?

Published / 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"'

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);

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

Published / 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
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

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

Published / 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
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

Creating a shortcut to a non existent location on Windows

Published / 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"

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"

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

Published / 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			
	ECHO Windows 10 version launching
	start <Accessfilelocation>\%1.accdr
	ECHO Windows 7 version launching
	start <Accessfilelocation>\%1.accdr

Display advanced properties of scheduled tasks in command line

Published / 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

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

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

Published / 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\"

Credits: SS64

Rebuild a V1 DBCapture Database Queue

Published / 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 *********************************
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>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 *********************************

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

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

Published / 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%
rem add any further Batch Code here
echo Error Level 0 so closing
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>'

After a few issues with some scripts I have stopped using Else in favour of the not equaling 0

print @result
   IF @result = 0
                IF @result != 0

Microsoft MSDN (Example Set C)