The Intransigent Data Blog – Page 3 – Getting data to toe the line

Data-Tier Application (dac) and SqlPackage.exe

Posted on 27/04/2016 by Andrew Ing03/05/2016

Can you install tools to export/deploy DacPac files without requiring a Visual Studio installation? Prerequisites. Install following from Microsoft SQL Server 2014 Feature Pack https://www.microsoft.com/en-us/download/details.aspx?id=42295 ENU\x64\SqlDom.msi ENU\x64\SQLSysClrTypes.msi Install Data-Tier Application Framework. (May 2015 version is here: https://www.microsoft.com/en-us/download/details.aspx?id=46898. This took some finding: there was … Continue reading →

Master Data Services 2012 Notes

Posted on 06/03/2016 by Andrew Ing06/04/2016

Notes from a first brush with MDS. Installation (2102) The ApplicationPool credential should be a member of MDS_Services (?) local user group. (not convinced that setup does this for you). The installation is not complete until you get the chance … Continue reading →

Parse SSRS Subscriptions Table

Posted on 26/02/2016 by Andrew Ing26/02/2016

Information about Report subscriptions is held in ReportServer.dbo.Subscriptions table. ExtensionSettings XML Fragment with root node <ParameterValues> Contains information about the format and destination of the report. Each <ParameterValue> element has child <Name> and <Value> nodes : named according to classical … Continue reading →

Duration Dimension

Posted on 21/02/2016 by adminUsr22/02/2016

For measuring short durations, such as telephone calls or handling times. Q1 Linear Grain? The question I haven’t answered is whether the dimension should be at the finest grain for all durations. The grain I am using is seconds but … Continue reading →

SSRS Linked Reports

Posted on 04/12/2015 by Andrew Ing09/02/2016

Is it worth the effort? Advantages Can encapsulate a set of defaults. Pre-configurations of a very general report, for example. Disadvantages Links between reports take users to the master source folder. (!) Dynamic parameter defaults don’t survive linking. Instead the … Continue reading →

SSRS Notes

Posted on 03/12/2015 by Andrew Ing30/03/2016

Display All Values on a Graph’s Axis Set Interval in Axis Options section to 1. Auto is normally too sparse for my liking on date axes. Setting an Interval expression disables auto-settings although the layout will still be adjusted automatically (staggered … Continue reading →

Sql Server Service Broker (SSSB)

Posted on 02/12/2015 by Andrew Ing03/12/2015

SSSB Endpoint Authentication Certificate Don’t forget to give this a long lifetime (if security policy allows) by using EXPIRY_DATE, otherwise the certificate expires after a year. CREATE CERTIFICATE [BrokerCertificateDec15] WITH SUBJECT = N’certificate_subject’, EXPIRY_DATE = N’2025-12-31′; Expiry of the certificate … Continue reading →

Configure SSRS Datasources to use Execution Account

Posted on 29/10/2015 by Andrew Ing29/10/2015

The SSRS Execution Account is a default Windows credential used by SSRS when running a report to access resources (e.g. file shares for image files) or databases for report datasources that do not require credentials and where Integrated Security is specified. … Continue reading →

Data Obfuscation

Posted on 30/07/2015 by Andrew Ing30/07/2015

Some functions for obfuscating data to protect personally identifiable data. Aims: fast enough to process a large table resulting data is “sufficiently” obfuscated resulting data retains enough characteristics that it makes some sense in context. transformations are repeatable so that … Continue reading →

Move Data Files

Posted on 04/05/2015 by Andrew Ing04/05/2015

Taking a database offline is probably less invasive than a detach-move files-reattach sequence. — Get file name and current location of files. SELECT name AS LogicalFileName, physical_name AS FileLocation, state_desc AS Status FROM sys.master_files WHERE database_id = DB_ID(‘RedgateMonitor’); USE MASTER — Alter … Continue reading →