JRevell.com

Gousto Meal Box – £25 off First Box

October 2, 2016PersonalLeave a Comment

A few weeks ago I visited a country fair and got talking to a rep from Gousto. Having taken up his time, I signed up for a reduced rate trial of their boxes…

The idea behind the boxes are to have fresh “meals” delivered to your door, (well the ingredients, ready for you to make at home), each containing an exact amount of food required to create a meal. For example if the recipe requires 10g of Ginger, then you receive exactly 10g of ginger.

The meals had good instructios to follow and only took 20-40 minutes to cook.

For my first box, we had:

Chicken Teriyaki with Sugar Snap Peas
Cheesy Baked Fish with Courgette Salad
Mexican Pork & Black Bean Rice

Overall I was very impressed with the first box I received. Inside the box, the perishables where stored in a biodegradable bag, complete with ice blocks to keep them cool. The meals are different to what I would normally eat, but this is good in a way as it’s exploring new meals and opening my mind to new dishes.

With the discount code JONAT161748, you can receive a £25 discount off your first box and give it a go..!

£25 off first Gousto box

Convert MS SQL Query to MySQL Automatically

September 28, 2016ComputersLeave a Comment

With a recent project, I’ve had to reproduce a table from a Microsoft SQL Database into a MySQL Database.

The table had lots of fields and would be very time consuming to write the SQL to create the table, but luckily I came across this tool to convert MSSQL to MySQL

MS SQL:

CREATE TABLE [dbo].[CRONUS UK Ltd_$Customer](
	[timestamp] [timestamp] NOT NULL,
	[No_] [varchar](20) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Search Name] [varchar](50) NOT NULL,
	[Name 2] [varchar](50) NOT NULL,
	[Address] [varchar](50) NOT NULL,
	[Address 2] [varchar](50) NOT NULL,
	[City] [varchar](30) NOT NULL,
	[Contact] [varchar](50) NOT NULL,
	[Phone No_] [varchar](30) NOT NULL,
	[Telex No_] [varchar](20) NOT NULL,
	[Our Account No_] [varchar](20) NOT NULL,
	[Territory Code] [varchar](10) NOT NULL,
	[Global Dimension 1 Code] [varchar](20) NOT NULL,
	[Global Dimension 2 Code] [varchar](20) NOT NULL,
	[Chain Name] [varchar](10) NOT NULL,
	[Budgeted Amount] [decimal](38, 20) NOT NULL,
	[Credit Limit (LCY)] [decimal](38, 20) NOT NULL,
	[Customer Posting Group] [varchar](10) NOT NULL,
	[Currency Code] [varchar](10) NOT NULL,
	[Customer Price Group] [varchar](10) NOT NULL,
	[Language Code] [varchar](10) NOT NULL,
	[Statistics Group] [int] NOT NULL,
	[Payment Terms Code] [varchar](10) NOT NULL,
	[Fin_ Charge Terms Code] [varchar](10) NOT NULL,
	[Salesperson Code] [varchar](10) NOT NULL,
	[Shipment Method Code] [varchar](10) NOT NULL,
	[Shipping Agent Code] [varchar](10) NOT NULL,
	[Place of Export] [varchar](20) NOT NULL,
	[Invoice Disc_ Code] [varchar](20) NOT NULL,
	[Customer Disc_ Group] [varchar](10) NOT NULL,
	[Country_Region Code] [varchar](10) NOT NULL,
	[Collection Method] [varchar](20) NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Blocked] [int] NOT NULL,
	[Invoice Copies] [int] NOT NULL,
	[Last Statement No_] [int] NOT NULL,
	[Print Statements] [tinyint] NOT NULL,
	[Bill-to Customer No_] [varchar](20) NOT NULL,
	[Priority] [int] NOT NULL,
	[Payment Method Code] [varchar](10) NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Application Method] [int] NOT NULL,
	[Prices Including VAT] [tinyint] NOT NULL,
	[Location Code] [varchar](10) NOT NULL,
	[Fax No_] [varchar](30) NOT NULL,
	[Telex Answer Back] [varchar](20) NOT NULL,
	[VAT Registration No_] [varchar](20) NOT NULL,
	[Combine Shipments] [tinyint] NOT NULL,
	[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
	[Picture] [image] NULL,
	[Post Code] [varchar](20) NOT NULL,
	[County] [varchar](30) NOT NULL,
	[E-Mail] [varchar](80) NOT NULL,
	[Home Page] [varchar](80) NOT NULL,
	[Reminder Terms Code] [varchar](10) NOT NULL,
	[No_ Series] [varchar](10) NOT NULL,
	[Tax Area Code] [varchar](20) NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
	[Reserve] [int] NOT NULL,
	[Block Payment Tolerance] [tinyint] NOT NULL,
	[IC Partner Code] [varchar](20) NOT NULL,
	[Prepayment %] [decimal](38, 20) NOT NULL,
	[Primary Contact No_] [varchar](20) NOT NULL,
	[Responsibility Center] [varchar](10) NOT NULL,
	[Shipping Advice] [int] NOT NULL,
	[Shipping Time] [varchar](32) NOT NULL,
	[Shipping Agent Service Code] [varchar](10) NOT NULL,
	[Service Zone Code] [varchar](10) NOT NULL,
	[Allow Line Disc_] [tinyint] NOT NULL,
	[Base Calendar Code] [varchar](10) NOT NULL,
	[Copy Sell-to Addr_ to Qte From] [int] NOT NULL,
	[Type of Supply Code] [varchar](10) NOT NULL,
	[Liq_ Payment Terms Code] [varchar](10) NOT NULL,
 CONSTRAINT [CRONUS UK Ltd_$Customer$0] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

MySQL:

CREATE TABLE `CRONUS UK Ltd_$Customer`(
	`timestamp` `timestamp` NOT NULL,
	`No_` varchar(20) NOT NULL,
	`Name` varchar(50) NOT NULL,
	`Search Name` varchar(50) NOT NULL,
	`Name 2` varchar(50) NOT NULL,
	`Address` varchar(50) NOT NULL,
	`Address 2` varchar(50) NOT NULL,
	`City` varchar(30) NOT NULL,
	`Contact` varchar(50) NOT NULL,
	`Phone No_` varchar(30) NOT NULL,
	`Telex No_` varchar(20) NOT NULL,
	`Our Account No_` varchar(20) NOT NULL,
	`Territory Code` varchar(10) NOT NULL,
	`Global Dimension 1 Code` varchar(20) NOT NULL,
	`Global Dimension 2 Code` varchar(20) NOT NULL,
	`Chain Name` varchar(10) NOT NULL,
	`Budgeted Amount` decimal(38, 20) NOT NULL,
	`Credit Limit (LCY)` decimal(38, 20) NOT NULL,
	`Customer Posting Group` varchar(10) NOT NULL,
	`Currency Code` varchar(10) NOT NULL,
	`Customer Price Group` varchar(10) NOT NULL,
	`Language Code` varchar(10) NOT NULL,
	`Statistics Group` int NOT NULL,
	`Payment Terms Code` varchar(10) NOT NULL,
	`Fin_ Charge Terms Code` varchar(10) NOT NULL,
	`Salesperson Code` varchar(10) NOT NULL,
	`Shipment Method Code` varchar(10) NOT NULL,
	`Shipping Agent Code` varchar(10) NOT NULL,
	`Place of Export` varchar(20) NOT NULL,
	`Invoice Disc_ Code` varchar(20) NOT NULL,
	`Customer Disc_ Group` varchar(10) NOT NULL,
	`Country_Region Code` varchar(10) NOT NULL,
	`Collection Method` varchar(20) NOT NULL,
	`Amount` decimal(38, 20) NOT NULL,
	`Blocked` int NOT NULL,
	`Invoice Copies` int NOT NULL,
	`Last Statement No_` int NOT NULL,
	`Print Statements` `tinyint` NOT NULL,
	`Bill-to Customer No_` varchar(20) NOT NULL,
	`Priority` int NOT NULL,
	`Payment Method Code` varchar(10) NOT NULL,
	`Last Date Modified` datetime NOT NULL,
	`Application Method` int NOT NULL,
	`Prices Including VAT` `tinyint` NOT NULL,
	`Location Code` varchar(10) NOT NULL,
	`Fax No_` varchar(30) NOT NULL,
	`Telex Answer Back` varchar(20) NOT NULL,
	`VAT Registration No_` varchar(20) NOT NULL,
	`Combine Shipments` `tinyint` NOT NULL,
	`Gen_ Bus_ Posting Group` varchar(10) NOT NULL,
	`Picture` blob NULL,
	`Post Code` varchar(20) NOT NULL,
	`County` varchar(30) NOT NULL,
	`E-Mail` varchar(80) NOT NULL,
	`Home Page` varchar(80) NOT NULL,
	`Reminder Terms Code` varchar(10) NOT NULL,
	`No_ Series` varchar(10) NOT NULL,
	`Tax Area Code` varchar(20) NOT NULL,
	`Tax Liable` `tinyint` NOT NULL,
	`VAT Bus_ Posting Group` varchar(10) NOT NULL,
	`Reserve` int NOT NULL,
	`Block Payment Tolerance` `tinyint` NOT NULL,
	`IC Partner Code` varchar(20) NOT NULL,
	`Prepayment %` decimal(38, 20) NOT NULL,
	`Primary Contact No_` varchar(20) NOT NULL,
	`Responsibility Center` varchar(10) NOT NULL,
	`Shipping Advice` int NOT NULL,
	`Shipping Time` varchar(32) NOT NULL,
	`Shipping Agent Service Code` varchar(10) NOT NULL,
	`Service Zone Code` varchar(10) NOT NULL,
	`Allow Line Disc_` `tinyint` NOT NULL,
	`Base Calendar Code` varchar(10) NOT NULL,
	`Copy Sell-to Addr_ to Qte From` int NOT NULL,
	`Type of Supply Code` varchar(10) NOT NULL,
	`Liq_ Payment Terms Code` varchar(10) NOT NULL,
 CONSTRAINT `CRONUS UK Ltd_$Customer$0` PRIMARY KEY 
(
	`No_` ASC
)
)

;

A simple find and replace in the new SQL to correct tinyint, and then the table can be easily created in the MySQL database.

SQL Remove Trailing Decimal Places

July 29, 2016ComputersLeave a Comment

Within a recent project, I’ve been extracting data from Microsoft Dynamics NAV using Taskcentre.

Within NAV, decimals are stored as decimal(38,20), complete will all 20 decimal places..!

The following SQL query is very handy at removing the un-required decimal places (but keeping the accuracy of the data)

SELECT CONVERT(DOUBLE PRECISION, 1.99990000000000000000)
	,CONVERT(DOUBLE PRECISION, 2.00000000000000000000)
	,CONVERT(DOUBLE PRECISION, 3.00005077000000000000)

The results of the above query is:

1.9999
2
3.00005077

SQL Server – Find Last Backup Date/Time

March 15, 2016ComputersLeave a Comment

With SQL server and 3rd party backup software, it can sometimes be difficult to tell when the last SQL backup has been taken and if the backup software has actually performed the backup or not.

There are 3 main backup types in SQL Server:

Full
Incremental
Transaction Log

The following SQL query will show vital statistics, including when the last backup took place for each backup type. Handy for debugging and checking backup strategies..!

This post is not going to go into the details of how each one works, or recommendations for strategies etc. There are lots of details on guides on the internet, such as this one – Microsoft Technet – Understanding SQL Backups.

 SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            d AS 'Last Full Backup' ,
            i AS 'Last Differential Backup' ,
            l AS 'Last log Backup'
    FROM    ( SELECT    db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        type ,
                        backup_finish_date
              FROM      master.sys.databases db
                        LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
            ) AS Sourcetable 
        PIVOT 
            ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup

For example:

TaskCentre SQL Performance – One way to improve performance

February 24, 2016ComputersLeave a Comment

When using the “ODBC” or “OLEDB” tools in TaskCentre, it may appear that a single query is executed on the database. In actual fact from trial and error, I have identified multiple queries are being called.

For example, if you run the following query (This query identifies all Item Ledger Entries, where the timestamp is odd):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

Notice, that the transaction isolation level is READ UNCOMMITTED. This means that the query will read the dirty data and not block other users. Also it is limited to 1000 rows.

However when the task is running, it appears that the following SQL queries are run against the database:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

and

SELECT COUNT(*)
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

The first query returns the data, the 2nd query returns the number of rows.

Notice in the 2nd query, the transaction isolation level is not declared… (In this case, will cause blocking). Also the TOP statement has been removed too, meaning that all records are counted – not good especially when there is a complex filter such as the above which can be time consuming.

Solution

A solution for this is to disable the counting of the records. One way I have identified how to do this is to UNION to a table returning no rows. (As obviously you don’t want to return rows to corrupt your SQL query).

In this example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
UNION
(SELECT NULL,NULL,NULL WHERE 1<>1)

I’ve identified that when UNION is used, Taskcentre does not count the number of records. This means that if you are using the data, the “RowCount” will return -1 instead of the actual data. If you need this for a further step (such as a decision), then you have to calculate it by looping round in VB as per this knowledgebase article.

Note and Disclaimer:
I don’t have knowledge of how the software actually works internally, so this document is only based on my experience and investigation, so is provided as-is.

Microsoft Dynamics Nav – Either the caller does not have the required permission or the specified path is read-only – Error

February 8, 2016Computers3 Comments

Today on Microsoft Dynamics Nav 2013R2, I kept getting the following error, which stumped me for a bit…

Either the caller does not have the required permission or the specified path is read-only.

After much investigation, I found a solution to the problem, delete the following folder and restart the service.

C:\ProgramData\Microsoft\Microsoft Dynamics NAV\71\Server\{Nav Instance Name}\users\default

Upon restarting the service, I was able to connect back to Nav without receiving the error.

Automatic T-SQL Formatting with Microsoft SQL Server Management Studio 2014

February 2, 2016ComputersLeave a Comment

Within MySql Workbench, there’s a feature called “Clean Up SQL” which automatically tidies up your SQL and the indentation automatically.

This is a very useful feature, as your messy SQL then becomes nice and neat. Unfortunately with Microsoft SQL Server Management Studio (SSMS), this feature is not standard.

Luckily I came across the following open source T-SQL formatter library, complete with an add-in for SSMS which adds this functionality – Poor Mans T-SQL Formatter. This plugin automatically formats your SQL, adding tabs and linebreaks etc in order to make it easy to read.

After installing the product, the following folder needs renaming in order to work with the 2014 version of SSMS:

%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\11.0

To:

%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0

This is due to the installer. The installer puts the files in the incorrect place for the 2014 version of SQL Management Studio. Once you have renamed the folder and restarted SSMS, it will automatically add the options to the Tools menu.

Not only does this plugin work for SQL Management Studio, there are also versions for:

Visual Studio
Notepad++
Command Line
WinMerge
Stand alone application

If you don’t wish to install the software, there’s an online version available.

FreeAgent Discount Code – 10% Discount Each Month – 454elo1w

January 13, 2016PersonalLeave a Comment

Free Agent is an online account package aimed at small users / businesses, including:

Sole Trader
Partnership / LLP
Limited Company

It offers the following features:

Overview Dashboard
Estimate Creation and Management
Invoicing
Personal Expense Management
Project Management
Time Tracking
Payroll
Backing
Accounting
VAT Declarations
Self Assessment Calculations
Corporation Tax Calculations
Dividend Vouchers

One of my favorite features is being able to automatically import bank statements from various banks into the package. Another is the dashboard which shows the expected Corporation Tax payment due, so this can be taken into account before Payroll is run etc.

I currently use this software for my own business.

In order to receive a 10% discount, simply quote the following code:
454elo1w

Or alternatively follow this link to receive your 10% discount off FreeAgent.

Dynamics NAV RTC – Page Slow to Load – Flowfields

January 11, 2016ComputersLeave a Comment

With Microsoft Dynamics Nav, it is possible to add a “FlowField” to a table. This flow field is then calculates an aggregate of an underlying table.

Typical uses are to perform the following calculations:

Sum
Average
Min
Max
Count

Additionally, the following non-aggregate functions are available:

Lookup
Exists

Typical values which can be calculated via flow fields include:

Inventory
Quantity on Sales Order
Quantity on Purchase Order

With some versions of Microsoft Dynamics Nav, opening a page can be extremely slow. If you compare it to an earlier version, say 2009 or 2013, the speed of the earlier version can be a lot quicker.

This is due to the FlowFields on the page. With some versions of Nav, the FlowFields are calculated when you open the page, which can be quite time consuming, especially where there are lots of records. Even if the FlowField is not visible on the page! On earlier versions of Nav, if you hide the field, the FlowField is not calculated, so the issue is not present.

Therefore if you have a page with a flowfield which is not used, instead of hiding it from the page, delete it completely.

Further information of this issue is available here on the Mibuso forum.

Magento Order Status Flow

January 9, 2016PersonalLeave a Comment

With Magento, the order status flow can be quite confusing.

The following diagram from MBS is useful in understanding the status flow:

It’s worth noting the following too:

An order can be “held” from any state/status in Magento
When “Unholded”, it goes back to the previous state/status
Once an order is “Cancelled” or “Closed”, it’s impossible to change state or status (except “Hold”)
An order cannot go to “Complete”, until everything is invoiced and shipped
An order cannot go to “Closed”, until all it’s items are refunded

JRevell.com

Gousto Meal Box – £25 off First Box

Like this:

Convert MS SQL Query to MySQL Automatically

Like this:

SQL Remove Trailing Decimal Places

Like this:

SQL Server – Find Last Backup Date/Time

Like this:

TaskCentre SQL Performance – One way to improve performance

Like this:

Microsoft Dynamics Nav – Either the caller does not have the required permission or the specified path is read-only – Error

Like this:

Automatic T-SQL Formatting with Microsoft SQL Server Management Studio 2014

Like this:

FreeAgent Discount Code – 10% Discount Each Month – 454elo1w

Like this:

Dynamics NAV RTC – Page Slow to Load – Flowfields

Like this:

Magento Order Status Flow

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: