CJSmith dot me

Just another blog

Author: Chris Smith

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

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

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

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.

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

Updating a network location in Windows 2012

Published / by Chris Smith / Leave a Comment

I had an issue where a server which was joined to a domain had incorrectly set its network as Public.
A TechNet article and TenForum article provided the answer (As it states Windows 10 it also applies to Server 2012) so I have placed credits above the steps I followed.
Following a re-occurrence I have found my steps below didn’t work for me a second time, so this post now has a second way of performing this with PowerShell thanks to a blog post from Clint Boessen.

Credits:
MS Technet
TenForums

Press [Windows Key] + [R] together at the same time, the Windows Run prompt should appear. If it hasn’t appeared press Windows Key and type Run, a Run desktop program should appear in the list
Type “gpedit.msc” and press enter
If this also fails try and launch it via the Microsoft Management Console
Local Group Policy Editor should appear
Expand Computer Configuration –> Windows Settings –> Security Setting –> Network List Manager Policies
Right click “Network List Manager Policies” and click Show All Networks
Choose your affected Network name from the right pane (If you are unsure check its name in Control Panel –> Network and Sharing Centre)
Go to Network Location tab and change the Location type from Public to Private
Close Local Group Policy Editor
Restart the computer or server for it to take effect.

Clint Boessen’s method actually worked much better for me and also didn’t need a machine reboot.

Open up Command Prompt as Administrator
Type “PowerShell” and press enter
Get a list of interfaces by using the command:
“Get-NetConnectionProfile”
You will want the InterfaceIndex as I prefer to specify exactly the interface to be modified (Required when you have more than one network cable attached)
To modify the interface you require use the command in Powershell, however swap out and choose whether you want Private or Public set:
“Get-NetConnectionProfile ¦ Set-NetConnectionProfile -InterfaceIndex -NetworkCategory

Filtering in Event Viewer Windows Server 2008 onwards

Published / by Chris Smith / Leave a Comment

The Event Viewer from Server 2008 onwards is XML based.
Filters based on XML syntax can be used such as:

You can search for anything which appears in the data section such as service names, error messages etc..

Notes:
MS Technet

Method of working out check digit for an SSCC code

Published / by Chris Smith / Leave a Comment

Method of working out check digit for an SSCC shipping code in SQL on a system with Sage Line 500 and Datalinx WHM (Although its easy enough to change, the first “set @SSCC” basically gets the digits, the rest of the code simply calculates the check digit).
This proc uses the Datalinx WHM Pallet ID and Sage stock warehouse code as Sage Stock lot numbers may contain text and slashes.
If called from a Datalinx WHM telnet/SSH script all print lines must be commented out.
GS1 codes can be shorter or longer, remember only 16 digits can be used (The GS1 code goes in from the second digit and digit 18 is always a check code!)
After needing two different versions (One required spaces in between different blocks the other didn’t) I decided to be lazy and specify the field locations. This version expects no spaces, if you need spaces simply change the Substrings and increase the variable SSCC from varchar(18).
Another thing to mind is I don’t need to change the first digit (GS1 states the same SSCC code can not be used within a year).

Apply ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to SQL Server

Published / by Chris Smith / Leave a Comment

We needed to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to specific databases on a SQL Server 2008 instance, I found to do this even with users off I was being shown errors stating it must be done in Single User Mode.
I decided I wanted to do this as quickly as possible so slapped together some SQL code.

Source: Microsoft MSDN
Microsoft MSDN
I am pretty sure the Check options are enabled must have been someone else’s code, when I find where I got this I will add a further credit.

Add a user to DatabaseMailUserRole on msdb

Published / by Chris Smith / Leave a Comment

I had a user unable to use a script which generated an email, I found the user didn’t have permission, Microsoft state:
To reduce the surface area of SQL Server, Database Mail stored procedures are disabled by default.
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

You can add the user manually by:
Opening SQL Server Management Studio
Connecting to the server in question
Expand Databases
Expand System Databases
Expand msdb
Expand Security
Expand Users, right click the user or group and click properties
If the user is not in the list right click users and add the user or group
On the Membership tab ensure the user or group is a member of DatabaseMailUserRole

If you are lazy use a script like the one below (It will return a 1 if SQL can’t find the user or the user already has access, 0 means it was successful)

Source: Microsoft TechNet
Microsoft MSDN

Notes:
I found using Windows Active Directory Groups required an extra step, by default all users have access to the Public Profile but not groups on SQL Mail.
Groups need to be added via “Manage Profile Security” –> “Private Profiles” tab.
As the group would be added select the AD group from the drop down box.
Ensure Access is ticked and click Next and OK.
As I have only done this the once I haven’t looked up if this can be done directly in T-SQL.

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)

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.
 

Credits:
SS64
Microsoft MSDN (Example Set C)