SQL Server

Power BI Secure embed codes - allows easy sharing of Power BI reports. The public embedded codes - publish to web - has been out for a while that allows you share the Power BI reports that the anyone on the Internet (unauthenticated members of the public) should be able to see. The key difference is the secure part of the name. Users will need to authenticate, this does of course mean they will require either a Power BI Pro license or the report will be using the Power BI Premium capacity....

Another day, another Twitter awesome moment, Parikshit Savjani was explaining how backups are now faster in SQL Server 2017. How we made backups faster with SQL Server 2017 https://t.co/NNf5cLuBJx — Parikshit Savjani (@talktosavjani) November 23, 2017 For more info on how, read his post. TD;LR - The wait time before data is actually copied is reduced because the amount of buffers to scan is massively reduced. “…requires only 250 buffers to scan as opposed to 500 Million buffers with former algorithm…”...

One of those annoying things about the US is they use different paper sizes to us Brits. US Letter is 215.9 by 279.4 mm (8.5 by 11.0 inches) The UK equivalent, A4, is 210 by 297 mm (8.26 by 11.69 inches) One of our customers is based in the UK but has remote sales offices in the US, so their SSRS report are set to A4 and when they printed them in the US offices, the footer is cut off....

I’ve been getting errors with MSSQL server on Ubuntu on Windows 10. The error, Failed to connect to bus: No such file or directory dpkg: error processing package mssql-server (–remove): subprocess installed post-removal script returned error exit status 1 Processing triggers for libc-bin (2.23-0ubuntu5) … E: Sub-process /usr/bin/dpkg returned an error code (1) I managed to fix it in the end by Of course, you should try uninstalling correctly first, which is latter of the two...

Another error I hit Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox28.Paragraphs[0].TextRuns[0]’ contains an error: Overload resolution failed because no Public ‘/’ can be called with these arguments: ‘Public Shared Operator /(d1 As Decimal, d2 As Decimal) As Decimal’: Argument matching parameter ‘d2’ cannot convert from ‘TextBoxImpl’ to ‘Decimal’. C:\Projects\Reports\1. Report.rdl Thankfully Google found Qiuyun answer. I was missing the .Value at the end. So (wrong) =ReportItems!Textbox1 Fixed (working)...

In case you haven’t heard, I’ve started a new job and one of my first tasks was to speed up a SSRS report. One of the first issues I stumbled across was this: An error occurred during local report processing. The definition of the report ‘/1. Report’ is invalid. The Value expression for the textrun ‘Tx412.Paragraphs[0].TextRuns[0]’ contains an error: [BC30456] ‘RdlObjectModel’ is not a member of ‘ReportingServices’ Looking into it the issue it appears its expanded expressions when it got copied – it’s basically making the it fully qualified....

For the past 4 months I’ve been using Visual Studio 2015 rather then Visual Studio 2013 and yesterday, I hit a bug with SSRS (shows how much I love SSRS). The bug appeared when it came to deployment The error was deploying SSRS report Exception calling “CreateCatalogItem” with “7” argument(s): “The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting...

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job. The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps SSIS Server Operation Records Maintenance...

One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code....

So one of the really nice sponsors at SQL Saturday had a vending machine that was rigged up to release a treat when you sent a tweet. Like so I'm at #sqlsatcambridge home of @redgate #redgatetreat #red668 — Matt Smith (@matt40k) September 10, 2016 and give you… In mine had a SQL Prompt license. At first I was a bit disappointed, I hoping for SQL Test license or a full blown SQL toolbelt but the more I’ve been using it, the more I’m loving it....

https://twitter.com/matt40k/710468378050339000 It’s nice to see Microsoft fixing old bugs – the I noticed today they updated the MSDN article about Actions in Multidimensional Models. It now supports HTTPS! Woot!! https://connect.microsoft.com/SQLServer/feedback/details/692837/cannot-specify-https-in-an-analysis-services-report-action

Unfortunately this year I’ve not been able to make any of the pre-cons, however the (free) community day looks epic – the hardest part is going to be selecting which session to attend, the final one looking the worst. Aghhh!!! I’m going to have to roll a dice or something! CDC – Change Data Capture in Detail Uwe Ricken What’s new in the SQL Server 2016 for a BI Professional...

Back in SQL Server 2008 Microsoft introduced auditing, specifically the Database Audit Specification. It’s pretty good – despite the title I do actually think its nice feature, it pretty much works and doesn’t have much of a performance impact, my problem is not much love has gone into it since it was released in SQL2008. It claims to meet various regulations such as the EU data Protection Directive, HIPAA, PCI DSS and to be fair, I’m sure it does....

One of the annoy parts of building a warehouse is building the staging database, its an important first step. The staging database is replica of the source systems. BIML can provide away to accelerate this process, however its not perfect. BIML is designed to create SSIS packages – and it does this very, very, well. SQL database objects, not so well. SSDT does this well. The first step in the designing a warehouse is discovery, Azure Data Catalog is an excellent tool for doing the discovery, it allows you to connect to a wide array of data source types and gather the meta data that can be used to build the staging database....

Today my colleague had a problem opening our BI solution, the solution had multiple projects, including 3 SSDT projects. Although the project builds correctly on both my machine, the build machine and another colleague machine it refused to build stating that the reference to the object in another project was invalid. After thinking for a few moments, I remembered I had seem this before. The problem was a bug in SSDT....

Microsoft has released SQL Server 2016 Developer, for free. That’s right, zero, zip, nothing. Completely free. The developer edition is fully featured, it contains the same goodness as the $$$ Enterprise edition. The catch? Its not for production.

Another day, another reference to an old Jamie Thomson blog post – today it was getting the ConnectionString to output to the information. Admittedly I wasn’t looking for how to fire the connection string into an information event, just the ConnectionString, still another great idea and its useful for debugging.

This month saw the release of SQL Server 2016, which from a BI\Report point of view is huge. Once we had access to it yeah, already been on msdn. Can't believe we're being made to wait!!! — Dave Kerby (@davekerby) June 1, 2016 I spotted something odd. So whats the diff? Enterprise is only core licensed - https://t.co/2NgZG7siUD #sqlhelp @sqlserver @SQLServerBI pic.twitter.com/SP8OC5eLvD — Matt Smith (@matt40k) June 6, 2016 Now, in SQL Server terms there are two types of licensing...

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentation to feed the foreach loop....

So today I went to run a SSIS package on my new laptop and bam, error message. Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails. —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML....

I was at event that Simon Sabin arranged, SQL SERVER TOOLS and SSDT shape the future and one of the questions that was asked, sorry, I forgot who asked (joys of leaving it too long before blogging about it – I want to say Gavin) was, How does (Windows) Containers fit into SQL Server? This is basically Microsoft integrating the Docker technology into the Windows OS, so you in effect, ship a more complete solution....

Another day, another problem. Todays problem was incorrect column length which was causing a SSIS package to fail. Historically I use a freeware tool to compare the different environments schema, but lately I just don’t bother, I have continuous deployment so its easier just to hit the deploy button then it is to try and figure it out. Well today I decided I wanted to it old school – although I’ve been using the SSDT SQL compare in my deployments, I’ve not actually used it in terms of viewing the differences....

There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing. Today I am looking at the SSAS auditing – you can find out more about it on TechNet. Olaf Helper has published some TSQL code for querying the audit data -on the Script Center. But first, we need a table to store the data, here is a TSQL script for creating the table:

Just thinking out load (because its good to get feedback) Just to expand on my tweet. I’ve lot of SSIS packages for load and building my BI warehouse (SSAS cubes), now currently I have a SQL job that has multiple steps For example Load data from Source system into Staging Build ODS intermediate tables Build Warehouse tables And this works for DataMart where they are a denormalized copy of a single source system, but when the source system, or the intermediate tables, are used multiple times, you don’t want to run them multiple times....

I recently discovered the ability to reference other database project (I know, I know), anyway, this cause my lovely automate build\deployment process to fail. Investigating the error led me to, yes, you guessed it a Jamie Thomson’s blog (from 2012). Thankfully, I managed to figure out a clean solution, the DacPac solution on Octopus Deploy library has an option to pass Profile Name, which is the publish profile XML file. He is the cut-down version I’ve used, just the missing variables