Category: Windows

DBForms: Check and format email addresses from Sage Line 500/1000

Published October 28, 2018 / by Chris Smith / Leave a Comment

As the email field can sometimes not be used for the correct purpose I use a bit of code to look for an @ symbol and then a dot. If more than one email address is in I replace the comma with -t which is required by dbmailsend.
If using Sage Line 500/1000 the CustomerTable will be scheme.slcustm
I expect the DBForms def below to pickup the customer code already so there is only a lookup to the customers email address.

In [INPUT]
 
VARIABLE=EMAIL_ADDRESS
VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"
 
In [EXTRACT]
LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")
DEBUG ("Email >" + EMAIL_ADDRESS) + "<"
 
 
In [DELIVERY]
LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)
                NOTE Check for valid email address; is there an @?
                IF  INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid"
                }
                NOTE Check for valid email address; are there spaces?
                NOTE Check for valid email address; is there at least one full stop?
                IF  INSTR(EMAIL_ADDRESS,".",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid2"
                }
                DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"
                NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.
                IF  INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN
                {
                        LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")
                        DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"
                }
 
 
 
In [OUTPUT]
 
OUTPUT=<EMAILOUTPUTNAME>
INVOCATION="<pathtodbmailsend>"
PARAMETERS=
{
        "-f"
        QUOTED <EmailAddress>
        "-r"
        QUOTED <EmailAddress>
        "-c"
        QUOTED <EmailAddress>
        "-s"
        QUOTED <EMAILSUBJECT>
        "-a"
        <ATTACHMENT>
        "-t"
        QUOTED EMAIL_ADDRESS
        OUTPUT_FILE
}

In [INPUT]

VARIABLE=EMAIL_ADDRESS

VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"

In [EXTRACT]

LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")

DEBUG ("Email >" + EMAIL_ADDRESS) + "<"

In [DELIVERY]

LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)

NOTE Check for valid email address; is there an @?

IF INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid"

}

NOTE Check for valid email address; are there spaces?

NOTE Check for valid email address; is there at least one full stop?

IF INSTR(EMAIL_ADDRESS,".",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid2"

}

DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"

NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.

IF INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN

{

LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")

DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"

}

In [OUTPUT]

OUTPUT=<EMAILOUTPUTNAME>

INVOCATION="<pathtodbmailsend>"

PARAMETERS=

{

"-f"

QUOTED <EmailAddress>

"-r"

QUOTED <EmailAddress>

"-c"

QUOTED <EmailAddress>

"-s"

QUOTED <EMAILSUBJECT>

"-a"

"-t"

QUOTED EMAIL_ADDRESS

OUTPUT_FILE

}

Posted In Sage Line 500 ERP | V1 DBForms

Remotely remove a broken user profile from another computers registry when the user folder is deleted

Published October 21, 2018 / by Chris Smith / Leave a Comment

I recently had an issue where someone had deleted my admin user profile from a remote server by deleting its folder.
The logon then failed as Windows still expected the folder to be there, as obviously the registry entries were not removed.
Rather than manually checking HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList for the SID and then removing it WMIC can be used. A user called Tommylotmanagement posted an answer and I found it worked if the user folder profile has been deleted as well.

wmic /node:<Server> path win32_UserProfile where LocalPath="c:\\users\\<ADUsername>" Delete 2>>c:\windows\temp\wmic.err

1	wmic /node:<Server> path win32_UserProfile where LocalPath="c:\\users\\<ADUsername>" Delete 2>>c:\windows\temp\wmic.err

It should then show:
Deleting instance \\<Server>\root\cimv2:Win32_UserProfile.SID=”<SID>”
Instance deletion successful

If it fails check the file c:\windows\temp\wmic.err in a text editor.

Credit: Tommylotmanagement

I have also found this has worked to correct issues where accounts keep generating TEMP or TEMP. folders.

Posted In Windows Server 2008 | Windows Server 2012

With a faulty drive stop Windows from mounting

Published September 02, 2018 / by Chris Smith / Leave a Comment

Credit: A Video from Linus Tech Tips reminded me of a way to stop Windows 7/10 from automounting drives

If you have an issue with a hard drive (That is spinning up, doesn’t sound like its head is craching/clicking and a SMART tool like CrystalDiskInfo Standard edition shows its healthy) that simply appears to hang your machine/take forever to mount, Windows can annoy you by attempting to mount drives. Windows can be told not to mount new drives so it can be told to not mount and forget older external drives which were plugged in.
This is useful if you want to drive and image the drive with software (If you don’t have a stand alone duplicator).

diskpart
automount disable
automount scrub
exit

diskpart

automount disable

automount scrub

exit

Autoscrub disable means you don’t have to modify the windows registry.
Automount scrub means you don’t have to remove the device from device manager (Often a pain with lots of external drives)

Obviously afterwards re-enable to restore default functionality

diskpart
automount enable
exit

diskpart

automount enable

exit

Posted In Windows 10 | Windows 7

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

Display the old USB Device Removal Tab

Published February 11, 2018 / by Chris Smith / Leave a Comment

Credit:
Waseemulla S on Microsoft Answers

I had a USB device that I could not unload, I prefer the old dialog box anyway and found it is still possible to show this box in Windows 10. The forum post above has the answer, however according to someone was a very poor answer. For me the answer from “Waseemulla S” worked exactly as expected, it was then I found the USB Caddy I had plugged in didn’t support stopping the USB device in this way. Waseemulla S’s steps are below (I see no need to change it)

Create a batch file that will prompt you to “Safe to remove Hardware” with just a click.
Open notepad and type

“rundll32 shell32.dll,Control_RunDLL hotplug.dll”

1	“rundll32 shell32.dll,Control_RunDLL hotplug.dll”

Click save as and change file type to all files, type the file name Filename.bat (Where Filename is the name of your choice)
Double click the file anytime you want to get “Safe to remove Hardware” prompt.

Posted In Windows 10

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

Getting a Kodak i160 to work on a KEC 1582t card in Windows 7

Published November 12, 2017 / by Chris Smith / Leave a Comment

We had an issue where a Kodak i160 scanner would appear when a Windows 7 machine booted but then disappear after the Kodak i160 scanner went to sleep.
The Kodak scanner was using a Kofax Virtual Rescan driver, after some checking it turned out the machine was using a standard fire wire driver for the KEC 1582t PCI card which worked fine on Windows XP Professional SP3.

The way around this was to use a legacy driver which is included as part of Windows 7 (It appears to work with a number of other fire wire cards as well for slow performance).

Windows 8 doesn’t have this driver as part of the OS and it can be obtained from Microsoft Support
The original MS article announcing this seems to show it doesn’t work for Windows 10, however a website called Studio 1 seems to show it does. I haven’t tested this as the Kodak i160 Scanner has no Windows 10 support, the parts are difficult to get hold of and therefore we are replacing them.

Posted In Windows | Windows 10 | Windows 7

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Published August 12, 2017 / by Chris Smith / Leave a Comment

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity

FROM "scheme"."eiorhdm" t1

INNER JOIN "scheme"."eiordtm" t2

ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Posted In Sage Line 500 ERP | SQL Server 2008

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

CJSmith dot me

I dump stuff I find useful here

Category: Windows

DBForms: Check and format email addresses from Sage Line 500/1000

Remotely remove a broken user profile from another computers registry when the user folder is deleted

With a faulty drive stop Windows from mounting

Creating a shortcut to a non existent location on Windows

Display the old USB Device Removal Tab

Generate a text file with subfolders in a location on explorer

Getting a Kodak i160 to work on a KEC 1582t card in Windows 7

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Display advanced properties of scheduled tasks in command line

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