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

Leave a Reply

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

Leave a Reply

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

Leave a Reply

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

This 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:

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

Leave a Reply

Firebrand Free Training For Life Firebrand 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

2 Replies

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
For 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
On 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:

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:

The screenshots below shows the setup of these two steps:
Run Report

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:

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

Microsoft Dynamics Nav 2013 R2 Action Image Library

Leave a Reply

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

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

URL Encode String in SQL

4 Replies

URL Encoding is the transformation of special characters into a form which can be passed over the internet. A good example being ” ” (space) which should be replaced by “%20%”.

The following SQL function can be used to URL Encode a string in SQL:

CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))
RETURNS NVARCHAR(3072)
AS
BEGIN
    DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
    SET @count = LEN(@url)
    SET @i = 1
    SET @urlReturn = ''    
    WHILE (@i < = @count)
     BEGIN
        SET @c = SUBSTRING(@url, @i, 1)
        IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
         BEGIN
            SET @urlReturn = @urlReturn + @c
         END
        ELSE
         BEGIN
            SET @urlReturn = 
                   @urlReturn + '%'
                   + SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
                   + ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
         END
        SET @i = @i +1
     END
    RETURN @urlReturn
END

In order to use the function, call it as follows:

SELECT dbo.UrlEncode('This is a test, *&%"£$')

This gives the following result:

This%20is%20a%20test%2c%20*%26%25%22%a3%24

Thanks to Peter DeBetta for the SQL.

Magento Web Service Calls Maintenance Mode – SOAP-ERROR: Parsing WSDL

Leave a Reply

I’ve had an ongoing issue with a Magento installation. When the maintenance mode is turned on (with access restricted to a few ip’s), web service calls using v2 SOAP API throws the following error:

SOAP-ERROR: Parsing WSDL: Couldn't load from 'http://**URL**/index.php/api/v2_soap/index/?wsdl=1' : failed to load external entity "http://**URL**/index.php/api/v2_soap/index/?wsdl=1"

The web service worked correctly when the site was not in maintenance mode.

After lots of research, managed to find the solution. Basically Magento itself was throwing the error as it uses the PHP SoapServer to create the API. This object needs access to Magento in order to function correctly. As maintenance mode was enabled, the SoapServer didnt have correct permission, so was getting the holding page and throwing the error.

In order to fix the issue, the IP address of the server needed adding to the exceptions for the maintenance mode. Full details on how to do this here – Magento Maintenance Mode

Once the IP was added to the white list, the web service calls started working again correctly.

Automating E-Mail Support Requests using FOAAS and Taskcentre

1 Reply

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
In 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 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
A response is then sent to the inbound e-mail automatically, using the following steps:

XML to Recordset (HTML Tool only recognises recordsets)
HTML (To build up an email)
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.

Here is the response to the email, including the automated response to the web service:

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:

Import Web Services Connector
Import Task

FOAAS Web Service Connector
FOAAS Task

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

JRevell.com

Johnathan Revell’s Personal Website

XML timestamptype using VBScript or SQL

Drop All Triggers from MS SQL Database

Orphaned Windows Logins on SQL Server

Selecting Text Between Characters in Taskcentre – Using VB Script

Competition – Free Training for Life

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Microsoft Dynamics Nav 2013 R2 Action Image Library

URL Encode String in SQL

Magento Web Service Calls Maintenance Mode – SOAP-ERROR: Parsing WSDL

Automating E-Mail Support Requests using FOAAS and Taskcentre