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








Advertisements

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

 


 


 

Gartner’s Magic Quadrant for Business Intelligence 2016

Gartner’s latest Magic Quadrant for Business Intelligence has been released and Microsoft Power BI has been named as a “Leader”, ahead of Tableau and Qlik.

 

Gartner Magic Quadrant for Business Intelligence 2016

 

Organizations seeking to stay ahead of the competition recognize the importance of investing in analytics and visualization tools to deliver insights from their data. They need a modern, powerful business intelligence (BI) platform that will stand up as an industry leader and a vision that will meet the needs of tomorrow.

Industry analysts have taken note of our efforts and we are excited to share Gartner has positioned Microsoft as a Leader, for the ninth consecutive year, in the Magic Quadrant for Business Intelligence and Analytics Platforms.* For the first time, Microsoft is placed furthest in vision within the Leaders quadrant.

The establishment of an updated modern BI and analytics platform definition for this year’s Magic Quadrant has significantly transformed the Leaders quadrant relative to is composition in previous years. In a market that is rapidly evolving with new innovation being introduced constantly, leaders in the BI market must demonstrate that they are focused not only on current execution but have a robust roadmap that will solidify its position as a future market leader, protecting the investment of today’s buyers.

The strong placement for Microsoft could only have been made possible through the hard work of our many engineers and testers who listened over the years to users of Excel and Power BI to expand the capabilities and drive new benefits. We also must thank the more than 90,000 organizations in 185 countries that are now using Power BI.

Just as businesses strive to find an edge against industry foes, Microsoft is determined to deliver modern BI and analytics solutions for all types of users and to remain a leader and visionary in a competitive BI platform environment. Microsoft and Power BI stand out — and the benefits pay off for users.

The technologies behind Power BI enable users to create and share insights in real time. With Power BI, anyone can develop rich and compelling stories that perfectly visualize data. And Power BI is continually upgraded by our community of engineers, partners and users, so the tools get better every day.

Read more about Microsoft’s standing in Gartner’s 2016 report and learn how businesses can make a competitive difference here. We also recommend reading Gartner’s report, “New Microsoft Power BI Is an Enhanced Offering at a Compelling License Price.

Source