Cheat Sheet–Installing In-Database R on SQL Server 2016

Installing R on SQL Server 2016 is simple. Microsoft’s documentation is great, but this cheat sheet is a handy reference.

Installing In-Database R requires four steps.

1 – Install R Services (In-Database) on the SQL Server instance in question.  This does require an acknowledgment that Microsoft Open R will be installed.

2 – Enable External Scripts

3 – Restart SQL Service in order for the enable external scripts change to take effect.

4 – Grant execute any external script permission to appropriate users or roles.

That is it, R is installed.  Microsoft provides a nice “Hello, World” example to verify the installation.

The link to Microsoft’s detailed documentation can be found below.

Posted in Database Administration | Tagged , , | Leave a comment

SQL Server Management Studio (SSMS) Cannot Script SQL Agent Jobs

I have been testing SSMS 2016 and I wanted to script out some SQL Agent jobs.  When attempting to do so, I ran across an error.

Discover dependencies failed.  Job is not supported in dependency discovery.


The description under additional information isn’t very helpful.  If it was labeled SQL Agent jobs do not support dependency discovery it would be better.  This error occurs when the Generate Scripts for dependent objects option is enabled under scripting. 

To disable it, do the following:

1. Select Tools, then Options…


2. Select SQL Server Object Explorer, then Scripting

3. Under Object scripting options, change Generate scripts for dependent objects to False


Posted in Database Administration, SQL Server Management Studio | Tagged , , | Leave a comment

Updated Kimball Group Reader

If you are student of Ralph Kimball, I am sure you had mixed emotions when he retired.  The good news is Margy Ross & Bob Becker are still going strong.  They are doing business at DecisionWorks Consulting  There is more good news.  They updated the Kimball Group Reader and the Kindle version is currently on sale.  The title is The Kimball Group Reader, Remastered Collection.

Happy Reading!

Posted in Data Warehouse | Tagged , , | Leave a comment

SQL Server 2014 TempDB Improvements

I am not sure how I missed this.  There is a performance improvement in TempDB in 2014. In summary, TempDB will be less aggressive writing to disk.  Here is the CSS page about it from April 2014.

Posted in Database Administration | Tagged , | Leave a comment

SQL Saturday #421 (Columbus, OH) Summary

SQL Saturday #421 was an excellent event. The Columbus session was held at a new venue.  I think the venue is better than the previous one.  All the rooms had desks which was nice for taking notes.  The rooms didn’t feel as  cramped even when they were standing room only.

Here are my takeaways:

  • There are stats changes in SQL 2012+.  Re-collecting stats will not necessarily invalidate the query plan. 
  • When virtualizing SQL Server, less is more when it comes to virtual CPUS.  Virtual CPU != physical CPU core. Understand NUMA nodes on the host hardware.
  • The SSAS tabular model is appropriate for small to medium size data sets.  Don’t use it for anything more than will fit in memory on the SSAS host. 
  • Power View is sexy, great at maps and time series but has limitations on what can be customized.
  • Azure Machine Learning is SSAS predictive models taken to the next level.
  • Last but not least, Jason’s Deli makes pretty good sandwiches.

What sets SQL Saturday apart from other events is the fact that everyone wants to be there.  Everyone is dedicated enough to the SQL Server community to spend a Saturday to learn and get better. 

See you next year!

Posted in Database Administration, Training | Tagged , , , , | 1 Comment

SQL Server 2016

I try to avoid simply posting a link in this blog but there are some significant changes coming in SQL Server 2016 so I am going to break my own rule.

The encryption feature is certainly timely but I am most excited about is running R algorithms in SQL Server.  SQL Server 2012 and SQL Server 2014 seemed to be ‘Enterprise Only’ releases.   This is a release that everyone should keep an eye on.

Posted in Database Administration | Tagged | Leave a comment

Finding SSRS Reports Without A Data Source

When deploying reports to different environments it is possible to have reports not link to a data source.  These reports are unusable and it can be time consuming to find them if there are a lot of reports.  I knew there had to be a query to identify these reports.  I thought I could search for ‘SSRS reports without a data source’ and find the queries.  Nothing came up in the first couple of pages of search results so that led to this blog post.

Case 1) Find reports where the data source doesn’t exist.  This could happen if a data source is deleted. Look for ItemIDs that exist in the catalog table but not the data sources table. 

SELECT Type, ItemID, Name, Path
FROM [ReportServer].[dbo].[Catalog]
WHERE  ItemID NOT IN (SELECT [ItemID]  FROM [ReportServer].[dbo].[DataSource])

On SQL Server 2012 the Type column is available to select only reports and shared data sets.

SELECT Type, ItemID, Name, Path
FROM [ReportServer].[dbo].[Catalog]
WHERE Type IN (2,8) AND ItemID NOT IN (SELECT [ItemID]  FROM [ReportServer].[dbo].[DataSource])

Case 2) Find reports without an un-linked data source.  Look for items in the Catalog and DataSource table with a null link. 

SELECT DISTINCT Catalog.Name, Catalog.Path
  FROM [ReportServer].[dbo].[DataSource],
  WHERE DataSource.ItemID = Catalog.ItemID AND Path NOT LIKE ‘%/Data Sources/%’ AND DataSource.Link IS NULL

Here is the SQL Server 2012 version using type instead of filtering on the Path.

SELECT DISTINCT Catalog.Name, Catalog.Path
  FROM [ReportServer].[dbo].[DataSource],
  WHERE DataSource.ItemID = Catalog.ItemID AND Type <>5 AND  DataSource.Link IS NULL

Posted in SQL Server Reporting Services | Tagged , , | Leave a comment