Tag Archives: SQL

Business Central / Dynamics NAV DateFormula SQL

The DateFormula DataType in BC / NAV allows calculates to date to be stored in Business Central. For example “5D” = “5 Days from date X”

These are not easily available in SQL due to how Microsoft stores the data.

Using this, I’ve created the following Function to convert BC/NAV’s DateFormula using SQL. This function allows you to pass in the date, plus the dateformula. This then returns the value using similar function to what is used in Business Central / Dynamics NAV.

CREATE FUNCTION dbo.CalcDate (@date date, @datefilter nvarchar(32))
RETURNS date

BEGIN

SET @datefilter = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@datefilter,  CHAR(1), 'C'), CHAR(2), 'D'),CHAR(3), 'WD'),CHAR(4), 'W'),CHAR(5), 'M'), CHAR('6'), 'Q'), CHAR('7'), 'Y')


declare @returneddate date = null;

declare @cleanedfilter nvarchar(35) = '';

SET @cleanedfilter = STUFF(REPLACE(REPLACE(CASE
  WHEN LEFT(@datefilter, 1) NOT IN ('+', '-') THEN '+'
  ELSE ''
END + @datefilter
, '+', '|+'
)
, '-', '|-'
)
, 1, 1, ''
) + '|||';


declare @p1 nvarchar(10) = '';
declare @p2 nvarchar(10) = '';
declare @p3 nvarchar(10) = '';


SELECT
  @p1 = LEFT(@cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) - 1)
 ,@p2 = SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, 0) + 1
  , (CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) - 1) - (CHARINDEX('|', @cleanedfilter, 0))
  )
 ,@p3 = REPLACE(SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) + 1
  , 999
  )
  , '|'
  , ''
  );

--Calculate Value

SET @returneddate = (SELECT
    CAST(v3.retp3 AS DATE) 
  FROM (SELECT
      @p1 AS p1
     ,@p2 AS p2
     ,@p3 AS p3) p
  OUTER APPLY (VALUES (
  CASE
    WHEN SUBSTRING(p1, 2, 1) = 'C'                -- <Prefix><Unit>
    THEN CASE SUBSTRING(p1, 3, 2)
        WHEN 'D' THEN @date
        WHEN 'WD' THEN @date
        WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        WHEN 'M' THEN CASE
            WHEN LEFT(p1, 1) = '+' THEN eomonth(@date)
            ELSE DATEADD(DAY, 1, eomonth(@date, -1))
          END
        WHEN 'Q' THEN DATEADD(DAY, CASE
            WHEN LEFT(p1, 1) = '+' THEN -1
            ELSE 0
          END, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0))
        WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        ELSE ''
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 1       -- <Number><Unit>
    THEN CASE
        WHEN RIGHT(p1, 2) = 'WD' THEN DATEADD(DAY, CAST(REPLACE(p1, 'WD', '') AS INT), @date)
        ELSE CASE RIGHT(p1, 1)
            WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(p1, 'D', '') AS INT), @date)
            WHEN 'W' THEN DATEADD(WEEK, CAST(REPLACE(p1, 'W', '') AS INT), @date)
            WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p1, 'M', '') AS INT), @date)
            WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p1, 'Q', '') AS INT), @date)
            WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p1, 'Y', '') AS INT), @date)
          END
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 0       -- <Unit><Number>
    THEN CASE
        WHEN SUBSTRING(p1, 2, 2) = 'WD' THEN DATEADD(DAY, RIGHT(p1, 1) - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) -
          CASE
            WHEN LEFT(p1, 1) = '-' THEN 1
            ELSE 0
          END, 0))
        ELSE CASE SUBSTRING(p1, 2, 1)
            WHEN 'D' THEN DATEADD(DAY, ABS(CAST(REPLACE(p1, 'D', '') AS INT)) - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'D', '') AS INT)) < DAY(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'D', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 0))
            WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, ABS(CAST(REPLACE(p1, 'W', '') AS INT)) - 1, datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'W', '') AS INT)) <= DATEPART(WEEK, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'W', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 1, 1))), 0)
            WHEN 'M' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'M', '') AS INT)) <= MONTH(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'M', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ABS(CAST(REPLACE(p1, 'M', '') AS INT)), 1)
            WHEN 'Q' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) <= DATEPART(QUARTER, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'Q', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ((ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) - 1) * 3) + 1, 1)
            WHEN 'Y' THEN datefromparts(ABS(CAST(REPLACE(p1, 'Y', '') AS INT)), 1, 1)
          END
      END
    ELSE ''
  END
  )
  ) AS v1 (retp1)
  OUTER APPLY (VALUES (
  CASE RIGHT(p2, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p2, 'W', ''), 'D', '') AS INT), retp1)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p2, 'W', '') AS INT) * 7, retp1)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p2, 'M', '') AS INT), retp1)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p2, 'Q', '') AS INT), retp1)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p2, 'Y', '') AS INT), retp1)
    ELSE retp1
  END
  )
  ) AS v2 (retp2)
  OUTER APPLY (VALUES (
  CASE RIGHT(p3, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p3, 'W', ''), 'D', '') AS INT), retp2)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p3, 'W', '') AS INT) * 7, retp2)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p3, 'M', '') AS INT), retp2)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p3, 'Q', '') AS INT), retp2)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p3, 'Y', '') AS INT), retp2)
    ELSE retp2
  END
  )
  ) AS v3 (retp3));

return
  @returneddate


END


GO

This function can be used as follows (today is the 9th January).

select dbo.calcdate(getdate(),'-6D')

Business Central – Objects List Extensions

I needed an easy way to view all the Extensions developed in a recent project. So knocked up this SQL. It is quite handy, as it will show you all the objects which are currently in use across all Extensions installed in Business Central.

To use, simply run the SQL, you can pick the object range you are looking at too.

DECLARE @StartID integer = 50000
DECLARE @EndID integer = 70000

SELECT
  P.Name
 ,P.Publisher
 ,CASE M.[Object Type]
    WHEN 1 THEN 'Table'
    WHEN 3 THEN 'Report'
    WHEN 5 THEN 'Codeunit'
    WHEN 6 THEN 'XMLPort'
    WHEN 7 THEN 'MenuSuite'
    WHEN 8 THEN 'Page'
    WHEN 9 THEN 'Query'
    WHEN 14 THEN 'PageExtension'
    WHEN 15 THEN 'TableExtension'
    WHEN 16 THEN 'Enum'
    WHEN 17 THEN 'EnumExtension'
    WHEN 20 THEN 'PermissionSet'
    WHEN 21 THEN 'PermissionSetExtension'
    WHEN 22 THEN 'ReportExtension'
    ELSE 'UNKNOWN'
  END AS [Object Type]
 ,M.[Object ID]
 ,M.[Object Name]

FROM [Application Object Metadata] M
INNER JOIN [Published Application] P
  ON M.[Runtime Package ID] = P.[Package ID]

WHERE M.[Object ID] BETWEEN @StartID AND @EndID
ORDER BY [Object ID]

Codeless Platforms (Taskcentre) Bug – 0x80040E14

Recently I’ve been receiving this error message when using the “Web Service Connector” and “External Lookup” tool.

Exception from HRESULT: 0x80040E14.

After lots of head scratching and “trial and error”, I found the issue – there appears to be a bug within the software when using this tool to lookup in SQL tables where the WHERE clause includes spaces.

You can work around this in 2 solutions:

  1. Change the lookup table structure to remove spaces from column names
  2. Create a SQL View which renames the columns (without changing original data structure).

With my problem, I went for the former.

Reimport / Refresh .NET SQL CLR After Windows Update

I’ve wrote a few .NET SQL CLR Routines in C# in the past. This allows you to call .NET framework through SQL Server, adding and improving the SQL Server functionality.

Occasionally Microsoft push out updates which break this. This can result in error messages such as:

<pre class="wp-block-syntaxhighlighter-code">Can't run XXXX(), A .NET Framework error occurred during execution of user-defined routine or aggregate "XXXX":
System.IO.FileLoadException: Could not load file or assembly 'System.IO.Compression, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. <a href='http://beauty-service-lg.de/43gaswcvc/'>ivermectin flea treatment for dogs</a>  Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at XXXX(SqlString XXXX, SqlString XXXX)</pre>

The solution is to refresh the loaded assembly into SQL. Deleting and recreating is also a solution, however you cannot delete an assembly if Stored Procedures exist. This is done by running a command such as:

 ALTER ASSEMBLY [System.IO.Compression] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.IO.Compression.dll'

SQL – Check When All Databases on Server Last Auto Grow

Running out of disk space? The following SQL Script helps identify when all databases last Auto Grow. (Script adapted from here)

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SELECT
  @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT
  @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT
  DatabaseName
 ,Filename
 ,(Duration / 1000) AS 'TimeTaken(ms)'
 ,StartTime
 ,EndTime
 ,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
 ,ApplicationName
 ,HostName
 ,LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d
  ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
ORDER BY t.StartTime DESC;

Full Text Index – Find Index Details

Recently I’ve had an issue with the primary key on a Full Text Index. This SQL query was helpful in highlighting the primary key which was being used:

SELECT tblOrVw.[name] AS TableOrViewName
	,tblOrVw.[type_desc] AS TypeDesc
	,tblOrVw.[stoplist_id] AS StopListID
	,c.name AS FTCatalogName
	,cl.name AS ColumnName
	,i.name AS UniqueIdxName
FROM (
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,tbls.[name]
		,tbls.[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.tables tbls ON tbls.[object_id] = idxs.[object_id]
	
	UNION ALL
	
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,[name]
		,[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.VIEWS vws ON vws.[object_id] = idxs.[object_id]
	) tblOrVw
INNER JOIN sys.fulltext_indexes fi ON tblOrVw.[object_id] = fi.[object_id]
INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = tblOrVw.[object_id]
INNER JOIN sys.columns cl ON ic.column_id = cl.column_id
	AND ic.[object_id] = cl.[object_id]
INNER JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN sys.indexes i ON fi.unique_index_id = i.index_id
	AND fi.[object_id] = i.[object_id];

Code initially from here, but has been tweaked slightly.

MS SQL – List Tables by Table Size

The following handy script will show the size of tables, sorted by size.

SELECT schema_name(tab.schema_id) + '.' + tab.name AS [table]
	,cast(sum(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb
	,cast(sum(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
FROM sys.tables tab
INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id
INNER JOIN sys.partitions part ON ind.object_id = part.object_id
	AND ind.index_id = part.index_id
INNER JOIN sys.allocation_units spc ON part.partition_id = spc.container_id
GROUP BY schema_name(tab.schema_id) + '.' + tab.name
ORDER BY sum(spc.used_pages) DESC

MS SQL – Normalise Case

Wow not wrote any notes for a very long time…!

Recently I’ve had an issue where some text was presented in a table, and I needed to normalise the text.

For example:

TESTiNG 123

Would need to become:

Testing 123

With thanks to Justin Cooney this was possible using the SQL below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION NormalizeCase (@InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OutputString VARCHAR(500)
DECLARE @Location INT

SET @Location = 1

-- Pre-set to a character string if the input string exists and has a length. otherwise the out string remains a NULL
IF DATALENGTH(@InputString) &amp;amp;amp;amp;gt; 0
BEGIN
SET @OutputString = ''
END

WHILE @Location &amp;amp;amp;amp;lt;= DATALENGTH(@InputString)
BEGIN
DECLARE @CheckCharacter VARCHAR(1)
DECLARE @PrevCheckCharacter VARCHAR(1)
DECLARE @OutCharacter VARCHAR(1)

-- Set the current character to lower case in case a capitalization condition is not met.
SELECT @CheckCharacter = LOWER(SUBSTRING(@InputString, @Location, 1))

SELECT @PrevCheckCharacter = SUBSTRING(@InputString, @Location - 1, 1)

-- Set the current letter to uppercase if the preceeding letter is a non-letter character
-- and the current character is a letter
IF @PrevCheckCharacter NOT LIKE '[a-z]'
AND @CheckCharacter LIKE '[a-z]'
BEGIN
SELECT @OutCharacter = UPPER(@CheckCharacter)
END
ELSE
BEGIN
SELECT @OutCharacter = @CheckCharacter
END

SET @OutputString = @OutputString + @OutCharacter
SET @Location = @Location + 1
END

RETURN @OutputString
END
GO

The code can be used as follows:

SELECT dbo.NormalizeCase('THIS IS a reaLLY GooD example')

With the result being:

This Is A Really Good Example

SQL Server – Identify Unused Indexes

Within SQL Server an index is used to optimise the reading of data from the database.

However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index.

Running the below SQL will highlight indexes which have not been used recently, allowing you to remove these indexes (if these are no longer required). This is sorted by the number of updates, as tables with high number of updates will most likely benefit from removal of this index.

SELECT TOP 200 o.NAME AS ObjectName
	,i.NAME AS IndexName
	,i.index_id AS IndexID
	,dm_ius.user_seeks AS UserSeek
	,dm_ius.user_scans AS UserScans
	,dm_ius.user_lookups AS UserLookups
	,dm_ius.user_updates AS UserUpdates
	,p.TableRows
	,'DROP INDEX ' + QUOTENAME(i.NAME) + ' ON ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (
	SELECT SUM(p.rows) TableRows
		,p.index_id
		,p.OBJECT_ID
	FROM sys.partitions p
	GROUP BY p.index_id
		,p.OBJECT_ID
	) p ON p.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
	AND dm_ius.database_id = DB_ID()
	AND i.type_desc = 'nonclustered'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
ORDER BY UserUpdates DESC

The following line can also be altered in order to include indexes which are rarely used (The code above is for unused), as there may be some indexes which are expensive to maintain, but only used once a day for example!

AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0

Example of result from this is shown below:

Azure SQL Database View Running Transactions / Blocking

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