Author Archives: JRevell

Azure SQL Database View Running Transactions / Blocking

Leave a reply

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests.

The following SQL is very useful as it shows details of all running transactions within the database, including highlighting if the transaction is blocked, or is causing blocking.

SELECT r.session_id
	,CASE 
		WHEN r.session_id IN (
				SELECT DISTINCT (blocking_session_id)
				FROM sys.dm_exec_requests
				)
			THEN 'Yes'
		ELSE ''
		END AS blocking
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,CASE r.transaction_isolation_level
		WHEN 0
			THEN 'Unspecified'
		WHEN 1
			THEN 'ReadUncommitted'
		WHEN 2
			THEN 'ReadCommitted'
		WHEN 3
			THEN 'Repeatable'
		WHEN 4
			THEN 'Serializable'
		WHEN 5
			THEN 'Snapshot'
		END AS transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
GROUP BY r.session_id
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,r.transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT
ORDER BY r.total_elapsed_time

Azure SQL Database Index Size

Leave a reply

Recently I’ve been doing some work on optimising indexes within Azure SQL Database. The following SQL from Daniel is very useful in showing the status of all the current indexes within a SQL Database. The SQL works for both SQL Server 2014 onwards, plus Azure SQL Database. I have not tested it on any other versions.

This code returns the following:

  • Object Type
  • Object Name
  • Index Name
  • Index Type
  • Partition (if any)
  • Compression
  • Data Space
  • Fill Factor
  • Rows
  • Reserved MB
  • In Row Used MB
  • Row Overflow Used MB
  • Out of Row Used MB
  • Total Used MB
SELECT --- Schema, type and name of object and index:
	       REPLACE(obj.type_desc, '_', ' ') AS objectType
	,       sch.[name] + '.' + obj.[name] AS objectName
	,       ISNULL(ix.[name], '') AS indexName
	,       ix.type_desc AS indexType
	,       --- Partition number, if there are partitions:
	      (CASE COUNT(*) OVER (
				PARTITION BY ps.[object_id]
				,ps.index_id
				)             
			WHEN 1
				THEN ''             
			ELSE CAST(ps.partition_number AS VARCHAR(10))             
			END) AS [partition]
	,       --- Storage properties:
	       p.data_compression_desc AS [compression]
	,       ds.[name] + ISNULL('(' + pc.[name] + ')', '') AS dataSpace
	,       STR(ISNULL(NULLIF(ix.fill_factor, 0), 100), 4, 0) + '%' AS [fillFactor]
	,       --- The raw numbers:
	       ps.row_count AS [rows]
	,       STR(1.0 * ps.reserved_page_count * 8 / 1024, 12, 2) AS reserved_MB
	,       STR(1.0 * ps.in_row_used_page_count * 8 / 1024, 12, 2) AS inRowUsed_MB
	,       STR(1.0 * ps.row_overflow_used_page_count * 8 / 1024, 12, 2) AS RowOverflowUsed_MB
	,       STR(1.0 * ps.lob_used_page_count * 8 / 1024, 12, 2) AS outOfRowUsed_MB
	,       STR(1.0 * ps.used_page_count * 8 / 1024, 12, 2) AS totalUsed_MB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON     ps.[partition_id] = p.[partition_id]
INNER JOIN sys.objects AS obj ON     ps.[object_id] = obj.[object_id]
INNER JOIN sys.schemas AS sch ON     obj.[schema_id] = sch.[schema_id]
LEFT JOIN sys.indexes AS ix ON     ps.[object_id] = ix.[object_id]
	AND     ps.index_id = ix.index_id
--- Data space is either a file group or a partition function:
LEFT JOIN sys.data_spaces AS ds ON     ix.data_space_id = ds.data_space_id
--- This is the partitioning column:
LEFT JOIN sys.index_columns AS ixc ON     ix.[object_id] = ixc.[object_id]
	AND     ix.index_id = ixc.index_id
	AND     ixc.partition_ordinal > 0
LEFT JOIN sys.columns AS pc ON     pc.[object_id] = obj.[object_id]
	AND     pc.column_id = ixc.column_id
--- Not interested in system tables and internal tables:
WHERE obj.[type] NOT IN (
		'S'
		,'IT'
		)
ORDER BY sch.[name]
	,obj.[name]
	,ix.index_id
	,p.partition_number;

Example Results:

Run Programs as a Domain User from None Domain Account

Leave a reply

Following on from my blog post for running Microsoft Dynamics Nav as a different user here, I have done some further investigation of the runas command.

Typically the RunAs command is used for local authentication, however there is a little known switch which bypasses local authentication and uses it for network authentication only.

This switch allows you to run the Microsoft Dynamics NAV Client, Development Environment, or any other tools as a domain user, from a none domain user pc.

Example syntax:

runas.exe /netonly /user:<DOMAIN>\<USER> "C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\finsql.exe"

For ease of access, it’s also possible to update your shortcuts as appropriate:

Firstly, within the shortcut, amend the target by prefixing with:

runas.exe /netonly /user:<DOMAIN>\<USER>

Also ensure you “Change Icon”, otherwise you will update the icon to a square (not good for users):

After running the application, it now asks you for the credentials, then runs the application as normal:

Delete Duplicate Records – Leave Only 1 Behind MS SQL

Leave a reply

Recently I’ve had to delete duplicate records from the Record Link table in Microsoft Dynamics Nav.

Using the following SQL, (which uses the OVER clause for partitioning) this deletes all duplicate records, leaving the first unique item behind.

The SQL can be updated easily to function for other tables / scenarios. Currently it uses the following fields to identify duplicates:

  • Description
  • URL 1

The SQL does not look at the Notes, which is stored as a BLOB.

DELETE
FROM [Record Link]
WHERE [Link ID] IN (
		SELECT [Link ID]
		FROM (
			SELECT *
				,ROW_NUMBER() OVER (
					PARTITION BY [Record ID]
					,[Description]
					,[URL1] ORDER BY [Link ID]
					) AS [ItemNumber]
			FROM [Record Link]
			WHERE [Note] IS NULL
			) a
		WHERE ItemNumber > 1
		)

Suggesting MS SQL Index Creations Using sys.dm_db_missing_index_details

Leave a reply

Within SQL Server, sys.dm_db_missing_index_details returns indexes which it believes are required by the Query Optimiser. Basically as queries are run in the background, the Query Optimiser makes a record of any optimisations it feels are necessary. Restarting the SQL server resets all these stats.

Using some SQL it is possible to identify potentially missing indexes, create sample SQL to build these indexes. I’ve found the following SQL useful:

SELECT mid.statement
	,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
		WHEN mid.equality_columns IS NOT NULL
			AND mid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,migs.*
	,mid.database_id
	,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

This produces results as follows:

Columns include:

  • statement – The table affected
  • improvement_measure – A generated measure of improvement (based on user cost, impact, seeks etc)
  • create_index_statement – A cut and paste friendly statement to add the index
  • group_handle – Identifies a group of missing indexes. This identifier is unique across the server.
  • unique_compiles – Number of compilations and recompilations that would benefit from this missing index group
  • user_seeks – Number of seeks caused by user queries that the recommended index in the group could have been used for
  • user_scans – Number of scans caused by user queries that the recommended index in the group could have been used for.
  • last_user_seek – Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
  • last_user_scan – Date and time of last scan caused by user queries that the recommended index in the group could have been used for.
  • avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group.
  • avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented.
  • system_seeks – Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for.
  • last_system_seek – Date and time of last system seek caused by system queries that the recommended index in the group could have been used for.
  • last_system_scan – Date and time of last system scan caused by system queries that the recommended index in the group could have been used for.
  • avg_total_system_cost – Average cost of the system queries that could be reduced by the index in the group.
  • avg_system_impact – Average percentage benefit that system queries could experience if this missing index group was implemented.
  • database_id – The ID number of the database
  • object_id – The ID number of the object

Microsoft Dynamics NAV – View All Active Sessions

Leave a reply

With Microsoft Dynamics Nav, there are various ways of viewing all the active sessions within the system. The easiest of which is the “Sessions” page within the software itself:

sessions-menu-option

sessions-window

The downside of this, is that it only shows active sessions on the tier which the user is connected. This is ok for solutions where the system has a single tier, however it is common to have multiple tiers in order to spread load. Checking this way is time consuming as you need to check multiple tiers in order to get a session count.

However using SQL can be an easy way to get the number of active sessions:

SELECT [User ID]
	,[Server Instance Name]
	,[Server Computer Name]
	,[Database Name]
	,[Client Computer Name]
	,[Login Datetime]
FROM [dbo].[Active Session]

It is worth nothing that Nav tidies up this table automatically, but in some cases it may be incorrect. For example if a middle tier crashes out, it could be left with orphaned records until the tier starts up again (or another tier prunes the records down).

Remembrance Sunday – Poppies at Conisbrough Castle

Leave a reply

In remembrance for 100 years of the Battle of the Somme, local people and businesses donated towards lighting up Conisbrough Castle.

The castle was lit up for 3 days, and raised well over £1500 for British Legion and the renovation of the local cenotaph/park.

It is estimated over 5000 people came to visit and view the display. The display even appeared on BBC News!

I took lots of pictures, here’s a selection of mine:

Convert MS SQL Query to MySQL Automatically

Leave a reply

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

Leave a reply

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.. لعب قمار اون لاين !

Dynamics NAV 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 Reduce Number of Decimal Places

SQL Server – Find Last Backup Date/Time

Leave a reply

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:

SQL Last Backup