SSMS Database Diagram – backend version is not supported

‘…backend version is not supported…’ was a new one for me. But when you think about it, you can’t use an ‘older’ version of SSMS to create a database diagram of a database on an SQL Server Instance that is a newer version. One option would be to download the latest stand-alone version of SSMS (which will also automatically update itself btw!).

Download SQL Server Management Studio (SSMS)
https://msdn.microsoft.com/en-us/library/mt238290.aspx

SSMS Database Diagram - backend version is not supported








SQL Server 2014 Developer Edition is now a free download

As of the 31st of March, SQL Server 2014 Developer Edition is now a free download:
https://myprodscussu1.app.vssubscriptions.visualstudio.com/Downloads?pid=1682

SQL Server 2014 Dev Download VS

Quote from MSDN:

SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

Ohh….and this is also a nice one:

SQL Server 2016 Developer Edition, when released later this year, will also be free.

Read the full post on the SQL Server Blog: https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

 

Evaluation Editions:
Just want to download an evaluation edition?:
Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation

SQL Server 2014 Dev Download

 


 


 


 

SSRS: Render your reports in HTML4.0 instead of MHTML!

By default in SQL Server 2012 (Enterprise) when you create a new data-driven subscription (DDS), you’re able to select ‘HTML4.0‘ as a rendering format. But when you create a normal subscription, the option isn’t available.

SSRS 2012 HTML4 subscription

 

Here’s how you can enable the functionality of rendering in HTML4.0 format for a normal subscription. Please remove Visible=”false” for the HTML4.0 rendering extension in the RSreportserver.config file as follows:

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

To get the HTML4.0 to be included in the body of the email rather than as an attachment, look for this element in the RSreportserver.config file:

< EmbeddedRenderFormats>

And add this as an extra entry within that element:

<RenderingExtension>HTML4.0</RenderingExtension>

In my own file the whole thing now looks like this:

< EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
<RenderingExtension>HTML4.0</RenderingExtension>
</EmbeddedRenderFormats>

This works fine for me using SQL 2008 R2 and up.

Also for SQL Server 2016, the option for HTML5 is also available (same principle as above). But in SQL 2016 with HTML5 it was still send as an attachment instead of embedded. So somehow the embed-config doesn’t work properly yet?








Two handy queries which can help you find things in your database


--Search for a specific string in object-definitions:
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName









How to: Delete records from your MDS Entity table

Here’s an example script which you could use to delete records from your MDS Entity/table:

Delete records from your MDS entity table

 

Here’s the code:


--Start clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

--Retrieve CodeID's:
INSERT INTO stg.[Actuals_Leaf_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
SELECT
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20160310' AS BatchTag
,Code
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--Verify:
SELECT * FROM stg.[Actuals_Leaf_Leaf]

--Execute:
EXEC stg.[udp_Actuals_Leaf_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20160310'

--Verify results:
SELECT COUNT(*)
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--End clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

 

More info on: https://clinthuijbers.wordpress.com/2015/11/02/mds-how-to-truncate-an-entity-table/

 


 


 

Waste of digital real estate?

Some of you already know that I fancy Pie Charts. Indeed they are a waste of digital real estate, but it somehow brakes the static look and feel of dashboards with just tables and Line Charts. (Looking forward to the new types in SSRS 2016 btw!)

For now, anyone who would like the recreate this one: http://blog.hoegaerden.be/2009/10/25/pie-chart-techniques/

I do fancy Pie Charts















And what do you think about maps? A nice addition or a waste of digital real estate?

Geo Map - SSRS 2012 2014


















Two dashboards I’ve made for my clients, all of them are extremely happy! 🙂
(Both can be made on SSRS 2008R2, 2012 and 2014)

BumbleBI Dashboard example

 

BumbleBI Dashboard - SSRS 2012 2014