Having recently had Cataract Eye Surgery with Spamedica / FreedomVision, I’m pleased to say that you can receive £100 discount if you mention the name “Johnathan Revell” when booking.
I will receive a £100 “Love2shop” voucher and you will receive £100 discount.
However to ensure the money goes to good causes – I will ensure that £100 will be donated to charity in the tax year which I receive the voucher.
Microsoft SQL Server runs by default under the “NT Service\MSSQLSERVER” account. This is a virtual account, with no username nor password (its all managed by windows).
Occasionally you may wish to run a command using this virtual account. As you don’t know the password, one of the easiest ways is to run this via SQL Server directly, by enabling xp_cmdshell. You can then run the command under that user account.
For security, its recommended to disable the xp_cmdshell afterwards (unless you need it).
--Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
--Run Command
DECLARE @command NVARCHAR(1000);
SET @command = 'commandtorun.exe';
EXEC xp_cmdshell @command;
--Disable again
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
Business Central 25 added “Full Text” searches to a lot of it’s pages. (More info here) This enhances the speed of Business Central, as instead of lots of “LIKE ‘%Value%'” searches, it can use the full text index.
Unfortunately, there are very little tools to show what is happening behind the scenes. You cannot easily see when an index is rebuilding for example or populating for the first time. You may notice high CPU or disk usage on SQL Server, but very few (if any) running SQL statements.
To view the current status of all the Full Text Indexes, the following SQL is very helpful. This shows you the current status of each Full Text Index, plus also vital statistics:
SELECT
DB_NAME(ftsac.[database_id]) AS [db_name]
,DATABASEPROPERTYEX(DB_NAME(ftsac.[database_id]), 'IsFulltextEnabled') AS [is_ft_enabled]
,ftsac.[name] AS [catalog_name]
,mfs.[name] AS [ft_catalog_file_logical_name]
,mfs.[physical_name] AS [ft_catalog_file_physical_name]
,OBJECT_NAME(ftsip.[table_id]) AS [table_name]
,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'IndexSize') AS [ft_catalog_logical_index_size_in_mb]
,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'AccentSensitivity') AS [is_accent_sensitive]
,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'UniqueKeyCount') AS [unique_key_count]
,ftsac.[row_count_in_thousands]
,ftsip.[is_clustered_index_scan]
,ftsip.[range_count]
,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'ImportStatus') AS [import_status]
,ftsac.[status_description] AS [current_state_of_fts_catalog]
,ftsac.[is_paused]
,(SELECT
CASE FULLTEXTCATALOGPROPERTY(ftsac.[name], 'PopulateStatus')
WHEN 0 THEN 'Idle'
WHEN 1 THEN 'Full Population In Progress'
WHEN 2 THEN 'Paused'
WHEN 3 THEN 'Throttled'
WHEN 4 THEN 'Recovering'
WHEN 5 THEN 'Shutdown'
WHEN 6 THEN 'Incremental Population In Progress'
WHEN 7 THEN 'Building Index'
WHEN 8 THEN 'Disk Full. Paused'
WHEN 9 THEN 'Change Tracking'
END)
AS [population_status]
,ftsip.[population_type_description] AS [ft_catalog_population_type]
,ftsip.[status_description] AS [status_of_population]
,ftsip.[completion_type_description]
,ftsip.[queued_population_type_description]
,ftsip.[start_time]
,DATEADD(SS, FULLTEXTCATALOGPROPERTY(ftsac.[name], 'PopulateCompletionAge'), '1/1/1990') AS [last_populated]
FROM [sys].[dm_fts_active_catalogs] ftsac
INNER JOIN [sys].[databases] dbs
ON dbs.[database_id] = ftsac.[database_id]
LEFT JOIN [sys].[master_files] mfs
ON mfs.[database_id] = dbs.[database_id]
AND mfs.[physical_name] NOT LIKE '%.mdf'
AND mfs.[physical_name] NOT LIKE '%.ndf'
AND mfs.[physical_name] NOT LIKE '%.ldf'
CROSS JOIN [sys].[dm_fts_index_population] ftsip
WHERE ftsac.[database_id] = ftsip.[database_id]
AND ftsac.[catalog_id] = ftsip.[catalog_id];
Here’s what the returned columns actually mean:
Thanks to Basit for this 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')
The following link will give you £15 free when applying for the Platinum Cashback Everyday Credit Card
https://americanexpress.com/en-gb/referral/jOHNARF4oV?XL=MNMNS
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]
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:
With my problem, I went for the former.
Using this following link, you can get £25 cashback for a Wealthify.
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'
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;
