Category Archives: Computers

Azure AD Connect – Force Sync Via Command Line

Azure AD connect is a new tool which replaces DirSync for syncing Active Directory information to Azure from the local active directory.

Synchronisation automatically works in the background, but sometimes you want to push through a synchronisation for some reason or another (rather than waiting for the next time it runs). This can be done by running the following 2 commands from the C:Program FilesMicrosoft Azure AD Syncbin directory:

DirectorySyncClientCmd.exe initial
DirectorySyncClientCmd.exe delta
  • initial – this forces a full sync
  • delta – this forces a delta sync

Example:
Azure AD Connect Command Line

Status can be checked by running “Synchronisation Service” and checking the log. In the example below, 452 entries where unchanged and 1 was deleted as part of the forced sync.
Azure AD Connect Synronisation Service Log

Microsoft SQL Server – Database Recovery After Restart – ETA for Recovery

When restarting SQL Server during the middle of a large transaction (say committing 100k rows), the database needs to recover by rolling back the transactions. The may take a while and removes all access to the database.

SQL Management Studio is not very helpful in giving an time for recovery, however the following commands come in useful in estimating the ETA:

SQL Server 2008

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
	 [LogDate]
	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
	,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

SQL Server 2012/2014

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
	 [LogDate]
	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
	,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

Thanks Tim Lagua for original post.

XML timestamptype using VBScript or SQL

The XML “timestamptype” date/time format is as follows:

2015-11-19T11:02:02.000000

Recently during a project of integrating systems together and creating XML files, I’ve had to create this in VBScript and SQL.

Here’s the code:

SQL

convert(varchar(50),getdate(),127)

VBScript:

Year(Now) & "-" & Right("0" & Month(Now),2) & "-" & Right("0" & Day(Now),2) & "T" & Right("0" & Hour(Now),2) & ":" & Right("0" & Minute(Now),2) & ":" & Right("0" & Second(Now),2) & ".000000"

As you can see SQL is a lot cleaner than VBScript!

Drop All Triggers from MS SQL Database

Recently I’ve had the scenario where I’ve had to drop all SQL Triggers from a Microsoft SQL database.

This could be done manually through the SQL Management Studio, but with a database with lots of triggers can be time consuming to do this.

The following SQL when ran against the database will drop all triggers from the said database automatically:

 USE [{database Name}]
DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig sysname
DECLARE @owner sysname
DECLARE @uid int

DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid
END

CLOSE TGCursor
DEALLOCATE TGCursor

Orphaned Windows Logins on SQL Server

Recently I’ve had to restore a SQL 2012 backup from a production machine to a test machine. Unfortunately as part of that restore process, logins to the database where not created.

As these users where “Orphaned” i.e. existed in the database, but not in the server, users where not able to login. These can be created manually, however is slow and time consuming where there are multiple users.

The following SQL (from Ted Krueger’s article) automatically created all the logins. The code works in SQL 2005, 2008, 2008 R2 and 2012. It may work in 2014 and 2016, however this is not tested.

SET NOCOUNT ON
USE AdventureWorks
GO
DECLARE @loop INT
DECLARE @USER sysname
DECLARE @sqlcmd NVARCHAR(500) = ''
 
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
 BEGIN
  DROP TABLE #orphaned
 END
 
CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))
 
INSERT INTO #Orphaned (UserName)
SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')
 
IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop < = (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
     END
     
    SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
    Exec(@sqlcmd)
    PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF

Selecting Text Between Characters in Taskcentre – Using VB Script

In Taskcentre, the text parser tool can be used to extract text between set characters.

A good example is if you have the following input data:

<Result>This is a test</Result>

And wish to extract the following text:

This is a test

Select String Between Characters Using VBScript in TaskcentreThis is a good tool, however doesn’t handle errors very well. For example, if the text is not found, it will throw an error message (unless you tell it to ignore errors), which may impact other steps. Additionally the extracted string may be needed in the middle of a Web Service call.

A solution is to use a VBScript function which will handle these errors gracefully. In the example, the function will return a space character if either of the characters are not found. but this can be changed to anything, e.g. “Error”, “String not Found” etc within the function

Using the function is very simple. The screenshot below shows example code:
Select String Between Characters Using VBScript in Taskcentre - Usage

In the example, the variable Result1 will be set to “This is a test”, whereas Result2 is not found, so is set to a space character.

Full download of the example is available below:
Select String Between Characters Example Script and Task

Competition – Free Training for Life

Firebrand Free Training For LifeFirebrand are doing the Free Training for Life competition again…

If you win, you can sit as many of their courses as you like over your lifetime, at no cost, so definately worth an entry. Courses range from Software Development (e.g. MCSD), to Ethical Hacking and Prince2. All carry some form of official certification at the end.

Note that your information most likely will be used for marketing purposes (Although I dont get much off them, just a few nudges every so often).

Enter Competition

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.

This software is designed to perform ETL operations, similar in which to Taskcentre does.

The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.

In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.

This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.

Creating Connections
ETL Tools Nav ConnectionFor this task, we’re going to need 2 connections as follows:

  • MS SQL Database – Connection to the Nav Database
  • SMTP – In order to send the email

After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.

Creating The Report
ETL Tools Report WizardOn right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:

  • Source Connection (In this case, it’s the connection we’ve created earlier)
  • SQL Query
  • Fields to add to the report
  • Groupings
  • Report Orientation
  • Report Layout (Rows or Columns)
  • Theme

After completing the wizard, this opens up in design view for further editing and design:
ETL Tools Report Designer

For this task, I’ve used all default options, plus the following SQL:

Select
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code],
   Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount
From
   [CRONUS UK Ltd_$Sales Header] Inner Join
   [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] =
   [CRONUS UK Ltd_$Sales Line].[Document Type] And
   [CRONUS UK Ltd_$Sales Header].No_ =
   [CRONUS UK Ltd_$Sales Line].[Document No_]
Where
   [CRONUS UK Ltd_$Sales Header].[Document Type] = 1
Group By
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code]

Create Package
After creating the report, we need to create a package to perform the following steps:

  • Run Report
  • Save PDF to Disk
  • Send Email with PDF Attachment

Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:
ETL Tools Package Design

The screenshots below shows the setup of these two steps:
Run Report
ETL Tools Package Design - Run Report

Send Email
ETL Tools Package Design - Send Email

Running Task
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.

In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:
ETL Tools Emailed Report

In due course I will do some further investigation into ETL Tools. Watch this space!

Microsoft Dynamics Nav 2013 R2 Action Image Library

Nav 2015 Action ImagesIn Nav, it’s possible to change the icon on a menu option, in order to make it clearer to the user. These are called “Action Images” in Nav 2009 or “Icon Collection” in Nav 2013 onwards. In the screenshot to the right I have highlighted an example of such an image.

Nav Action Images Object DesignerThese icons can be changed in “Object Designer” by a Microsoft Dynamics Nav Developer, within the properties for the action.

Depending on the version of Nav, it is either a free text entry, or a lookup. But regardless of the version of Nav, unfortunately there’s no image preview. Hopefully this will be included in a future version.

Luckily, the full library of options available are available on the Microsoft’s website:

Unfortunately for Nav 2013, these are split across multiple pages. This post is a lift of all the images from the site above, but all on a single page to make it nice and easy to view and select the perfect icon for your page.

For up to date images, I recommend viewing the Microsoft articles above.

0-9A

B

CA-CH

CH-CO

CO-CU

D

E

F

G

H

I

J

K

L

M

N

O

PA-PR

PR-PU

Q

RA-RE

RE-RU

SA-SH

SH-SY

T

U

V

W

X

Y

Z

Automating E-Mail Support Requests using FOAAS and Taskcentre

In this day and age, everyone is looking for prompt responses to e-mails and support requests. In this blog post I’m going to cover integrating FOAAS into e-mail using Taskcentre in order to provide prompt responses to support requests.

By responding quickly to emails, it can greatly improve satisfaction levels, plus also using FOAAS’s technology can also reduce the number of support requests*.

* by increasing the likelyhood of a P45.

In order to integrate the solution, we need to break it down into 3 major steps:

  • Intercept Email
  • Call Web Service
  • Send Response

Intercept E-Mail
FOAAS - Intercept E-MailIn order to intercept the email, we use a standard “SMTP” input trigger. This trigger stores the following into variables:

  • From E-Mail
  • E-Mail Subject
  • E-Mail Body

Additionally, we also need to use the “Text Parser” tool in order to extract the following from the “From E-Mail”

  • Sender Name
  • Sender E-Mail

Call Webservice
FOAAS - Web Service CallFOAAS is a REST based webservice. So I’ve built up a web service call, passing through the following parameters:

  • Sender Name
  • From Name

This web service returns two values:

  • message
  • subtitle

Additionally, the web service requires custom headers which have been amended using the tool

Send Response
FOAAS - Send ResponseA response is then sent to the inbound e-mail automatically, using the following steps:

  1. XML to Recordset (HTML Tool only recognises recordsets)
  2. HTML (To build up an email)
  3. Outbound SMTP

Example
Below you can see an inbound email from a used. As you can see, the email contains both a subject and a request for the support helpdesk. In my VM, the “From” name is “James – Demonstration”, as I use the VM for testing and demonstration purposes.
FOAAS - Inbound E-Mail

Here is the response to the email, including the automated response to the web service:
FOAAS - Outbound E-Mail

Source Task and Installation
Below is the download link for the example, plus also the web service connector. In order to install the task, the install needs to be done in the following order:

  1. Import Web Services Connector
  2. Import Task

FOAAS Web Service Connector
FOAAS Task

This task has been provided for humour/educational use only.