CJSmith dot me

I dump stuff I find useful here

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:

<QueryList>
  <Query Id="0" Path="System">
    <Select Path="System"> 
                 *[EventData[Data and (Data='<Search Parameter>')]] 
              </Select>
  </Query>
</QueryList>
 
<QueryList>
  <Query Id="0" Path="Application">
    <Select Path="Application">
                 *[EventData[Data and (Data='<Search Parameter>')]] 
              </Select>
  </Query>
</QueryList>

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

- <EventData>
  <Data Name="param1">Service Display Name</Data> 
  <Data Name="param2">Details</Data> 
  <Data Name="param3">Details</Data> 
  <Data Name="param4">Service Name</Data> 
  </EventData>
  </Event>

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:      Generate an SSCC code with check digit
-- The first digit is always zero (Unless we re-use lot numbers within a year)
-- Digits 2 to 7 are our GS1 number of 1234567
-- Digits 9 to 17 are out lot number (With +0 taken off the front)
-- Digit 18 is a check digit.  Use GS1 Check Digit Rules to Calculate
-- =============================================
-- GS1 Check Digit Rules
-- 1 Starting with the right hand digit of the number, add all the alternate digit values
-- 2 Multiply the result of step 1 by 3
-- 3 Add up all the other remaining digits
-- 4 Add the result of step 2 to the result of step 3
-- 5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10
-- Check output with http://www.gs1.org/check-digit-calculator to ensure accurate
-- =============================================
-- Data sources used:
-- scheme.stquem
-- =============================================
-- Example execution
-- exec [dbo].[usp_generatecheckdigitfor_SSCC] @palletno = '+001234567', @wh = 'WH'
-- =============================================
CREATE PROCEDURE [dbo].[usp_generatecheckdigitfor_SSCC] 
       -- Add the parameters for the stored procedure here
       @palletno varchar(10) = '+000000000', 
       @wh varchar(2) = 'WH'
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
 
DECLARE @SSCC varchar(18)  --The SSCC Code
DECLARE @SSCCcd_working1 int  --For Step 1, 2 and 4 and for final calc
DECLARE @SSCCcd_working2 int  --For Step 3 and 4
DECLARE @SSCCcd_r int  --Rounded results calc
DECLARE @SSCCcd_dif int  --Used to calc check digit
 
--Input is 17 numbers for SSCC number.  A check digit must be calculated at the end
--Our GS1 Company code is 7 digit long and is "1234567".  An extra zero is added at the end as we can't send the plus symbol of out pallet id
       set @SSCC = (select '01234567' + SUBSTRING(users_text_field,2,8) as SSCC from scheme.stquem(nolock) where users_text_field = @palletno and warehouse = @wh)
       print @SSCC
 
--Work out Check Digit from @SSCC
print '1 Starting with the right hand digit of the number, add all the alternate digit values'
       set @SSCCcd_working1 = SUBSTRING(@SSCC,17,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,15,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,13,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,11,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,9,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,7,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,5,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,3,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,1,1)
       print @SSCCcd_working1
print '2 Multiply the result of step 1 by 3'
       set @SSCCcd_working1 = @SSCCcd_working1*3
       print @SSCCcd_working1
print '3 Add up all the other remaining digits'
       set @SSCCcd_working2 = SUBSTRING(@SSCC,16,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,14,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,12,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,10,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,8,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,6,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,4,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,2,1)
       print @SSCCcd_working2
print '4 Add the result of step 2 to the result of step 3'
       set @SSCCcd_working1 = @SSCCcd_working1 + @SSCCcd_working2
       print @SSCCcd_working1
print '5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10'
       set @SSCCcd_r = ROUND(@SSCCcd_working1,-1)
       print @SSCCcd_r
-- Avoid issues from rounding down, add 10 to ensure @SSCCcd_r is always greater or equals to @SSCCcd_working1
       If @SSCCcd_r < @SSCCcd_working1
              set @SSCCcd_r = @SSCCcd_r+10
       print @SSCCcd_r
--Work out difference between rounding and check digit
       set @SSCCcd_dif = @SSCCcd_r - @SSCCcd_working1
       print @SSCCcd_dif
       print @SSCC
       set @SSCC = @SSCC + CAST(@SSCCcd_dif as varchar)  --Don't treat as number!
       print @SSCC
       select @SSCC as SSCC

END

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.

--Set Db to single user mode, rollback if any process still connected
ALTER DATABASE "<Database>" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE "<Database>" SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE "<Database>" SET READ_COMMITTED_SNAPSHOT ON
GO
--Set Db to multi user mode
ALTER DATABASE "<Database>" SET MULTI_USER
GO

--Check options are enabled (Value=1) or disabled (Value=0)
SELECT snapshot_isolation_state_desc FROM sys.databases where name="<Database>"
SELECT is_read_committed_snapshot_on FROM sys.databases where name="<Database>"

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)

EXEC msdb.sys.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<AD_or_SQL_Username>'

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)

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

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.
http://www.accessmvp.com/DJSteele/DSNLessLinks.html – AccessMVP (LINK BROKEN!) 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.

ECHO Running the program with start allows the batch script to move on to the next line
start "DBCapture" "d:\myprogram.exe" -runparameter1 -runparameter2
ECHO Waiting 120 seconds before killing off the task
ECHO Credit For lower CPU usage, instead of using WAIT use Ping as suggested on http://ss64.com/nt/timeout.html
PING -n 121 127.0.0.1>nul
ECHO Kill the program
Taskkill /IM "myprogram.exe" /F

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.

Load Object Unlink Mail\<NSFFILE>.nsf

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

Load Object Unlink <SHAREDMAILSTORE>.nsf

Additional Notes: https://web.archive.org/web/20180201115815/http://www-01.ibm.com:80/support/docview.wss?uid=swg21096686 – IBM Notes in regards withdrawn support for this deprecated feature of Lotus Domino (Link no longer works)
Shared Mail was replaced with a feature called DAOS in IBM Domino 8.5
http://www-01.ibm.com/support/docview.wss?uid=swg21088860 – If delinking all Notes Databases ensure the items are purged from the shared database. (Link no longer works)