Microsoft have just released the latest version of the Microsoft assessment and planning toolkit, known as MAPS.
Version 9.4 was released on the 13th of August 2016 and still contains a bug I noticed a few releases ago.
I decided to contact the MAP product team and inform them of the Bug.
What is the BUG?
When performing a SQL Server inventory and exporting the reports it is not always possible to see the whole of the status text in the excel report, this is not great when trying to work out the collation of all of your databases so that you can consolidate.
What you end up seeing is the following:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=RESTRICTED_USER, Recovery=SIMPLE, Version=661, Collation=Latin1_General_CI_A
After some initial investigation it turns out that the issue is around the size of the Status in the database table being nvarchar(128).
In the Database the SQL Server status information is stored in:
[SqlServer_Inventory].[DataBaseProperties]
whereas the status text returned by SQL Server stored procedure sp_helpdb is nvarchar(600).
this means that we are truncating the last 472 characters.
The Fix
The resolution to this is to change the table definition to be able to contain the amount of data required.
Note: I have chosen nvarchar(max) to allow for flexibility as the MAP toolkit may either not get the data from sp_helpdb or may append some of it’s own data.
use [model]
go
alter table [SqlServer_Inventory].[DataBaseProperties]
alter column [Status] nvarchar(max)
NOTE: by changing the table in the model database this means that any subsequent databases created will already have the database fix applied and also allows the report to contain the full extent of the data. This fix will not apply to databases already created
[This is an edited version of a post that was originally published at andoius.blogspot.co.uk]
