CJSmith dot me

Just another blog

Author: Chris Smith

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)

Microsoft Access 2010 loader

Published / by Chris Smith / Leave a Comment

Credits (As what I consider the hard stuff was actually done by other people):
TheSmileyCoder for the solid awesome little VBS File, this basically gets around the limitation of not actually getting Access to load another Access Database. At the time I only ever needed one modification (A rarely issue affected two P4 machines with a 5400RPM drive and bloated AV software). More than likely WScript.Sleep(200) won’t be required and the original file can be used.
UtterAccess for the AddTrustedLocation code.
AccessMVP as for this loader I tend to use the DSNLESS code to remove the need to deploy yet another ODBC link to user machines.
VBA Tips and Tricks for the FileExists module.

About:
The following contains what I call the Microsoft Access 2010 Loader. It was put together to try and stop heavily used Microsoft Access database front ends from corrupting (We had a point where one particular Access front end would be corrupted up to 3 or 4 times a day as around 40 to 50 people would be in a 40MB Access front end at any time). The included version will require the table to be linked to a database, we use a central SQL database.
This access file will treat its own filename as the file it will launch, so be aware the loader and your actual access front ends needs to be located in different places.

What does it do:
It checks if a copy of the Access front end and VBS file exists locally (I use the %appdata% folder, you may want to use another location or %localappdata% instead)
If it exists it checks if the version in registry (HKEY_LOCAL_USER\Software\VB and VBA Program Settings\\Version\Version) matches what is in the database table “dbo_access_version_master”
If it doesn’t exist locally or the version does not match it then downloads from a location such as “\\myserver\access$\”
If downloaded the registry is updated with the new version number
It then passes parameters to a VBS file created by TheSmileyCoder and launches it

Limitations:
The first form of the called Access file MUST have VBA code of some sort or else the Access file simply closes.
I haven’t found a way to detect if a local copy of an Access ACCDR file has corrupt VBA.
I have found users copy and rename the loaders file breaking it, try and ensure shortcuts are used.
I haven’t been able to trap where Windows AD accounts have expired or locked, as the error occurs when Access connects to our central SQL database.
If the locations aren’t added to the Trusted Locations the user will be prompted twice to allow the Access files to open. The loader uses code to add this on first launch.
I sometimes find the repaints don’t always work.
Looking at the code used its a bit horrible looking, more than likely it can be optimized much better.

What do I need to change in VBA (Alt + F11):
Replaced in the VBA code “MyCompany” with your own on the main form and module SwitchFrontEnd.
Replace \\myserver\access$\ with where you will store your files (I used access$ as this is hidden if someone views the server)
If you don’t use .accdr files replace .accdr on the forms code

The actual files:
Microsoft Access Loader Files

Why use ACCDE and then ACCDR Access Front Ends

Published / by Chris Smith / Leave a Comment

Nearly every single Microsoft Access “database” I use has a proper database attached instead of using the internal Jet Engine, this means data is not actually stored in the Access “database” and I therefore call them Access Frontends.
Compiling your Access front ends to ACCDE provides a compiled copy of your Access Front End.
I find it is much harder for users to corrupt the Access front ends when compiled.
Making a compiled Access ACCDE file a runtime file is as simple as renaming the extension from ACCDE to ACCDR, all this does is make sure full installs of Microsoft Access launch in the runtime mode. It also means you can use the Access Runtime which is free from Microsoft (However always ensure you read through the EULA).
As runtime mode is used, its easier for development as I don’t have to hide menus.

To create an ACCDE from a Microsoft Access file, open the file in question, ensure your “Visual Basic for Applications” Code compiles otherwise the next step will fail.
Go to File –> Save & Publish –> Make ACCDE
Save the file in the location you want.
You can now rename the extension from ACCDE to ACCDR.
Now test the file launches.

Within a batch file run a program for a certain length of time before killing it

Published / by Chris Smith / Leave a Comment

I needed to run a scheduled batch job and kill it off after a certain amount of time, this was because the program would run what it needed and then run as normal (If it exists then there is no reason to use start or timeout). As it actually ran as a service I didn’t want to leave it running in this way.
As I use Taskkill on the programs exe name, obviously don’t use it if it will be run on a server where it is running other executables with the same program name.
I replaced timeout with ping as the process launched fully utilises the CPU cores.

Credits: I replaced my use of timeout based on a suggestion on SS64.com

Delink a Shared Mail Notes Files when the system utilised Shared Mail

Published / by Chris Smith / Leave a Comment

We had a number of nsf files given to us by a company we had purchased, however they utilised Shared Mails and the resulting Notes Files contained lots of missing attachments.
A process of de-linking needed to be run to basically tell Domino to copy shared items back to the nsf file and not to use Shared Mail.
Run the following command in the Domino Console, the shared mail files need to be on the same Domino Server and accessible.

To unlink a specific store run and put all mail back into linked Notes Storage Files (Make sure you have free space and do not run when in production use!)

Additional Notes: IBM Notes in regards withdrawn support for this deprecated feature of Lotus Domino
Shared Mail was replaced with a feature called DAOS in IBM Domino 8.5
If delinking all Notes Databases ensure the items are purged from the shared database.