Snippets of code to create date-range parameter defaults for SSRS reports. PrecedingWeek The seven days up to and including yesterday. /* Yesterday = Yesterday’s date. SevenDaysAgo = Seven days before today: i.e. a week up to and including yesterday. 12/08/2014 … Continue reading →
Time Dimension creation script DDL for a time dimension table. Create table DimTime ( time_key smallint NOT NULL, time_type varchar(20) NOT NULL, full_time time(0) NULL, hour_of_day tinyint NULL, minute_of_hour tinyint NULL, hourly_segments varchar(13) NULL, minute_15_segment_key tinyint NULL, minute_15_segments varchar(13) NULL, … Continue reading →
Interpreting data in ReportServer database Observations from SSRS 2008 R2 Subscriptions table dbo.Subscriptions. InactiveFlags There’s no way to disable a subscription from SSRS UI though articles suggest setting InactiveFlags to 1 will achieve this. 0 : schedule is active. 8 … Continue reading →
Introduction Avoid all use of the Performance Monitor interface by using typeperf.exe (the oddest of names). Brent Ozar’s Tutorial on SQL Performance Monitoring – using performance monitor manually to gather SQL-relevant counters. Shaun J Stuart Baselining SQL Server Part 1 and … Continue reading →
Requirement: to use the a member Key in a report rather than the display name or Value properties. Problem the SSRS query builder doesn’t allow selection of properties to return. Reference to Fields!MemberName.Key returns Null in reports. One Solution: Locate following … Continue reading →
What are minimal permissions required for certain operations? Run Profiler ALTER TRACE SSDT Import a database schema into project VIEW DEFINITION VIEW SERVER STATE If working with multiple databases use VIEW ANY DEFINITION run on master. SSDT Make schema updates … Continue reading →
Problem Table contains two or three columns that should be output as a single column over two or three rows. Such as [Telephone1], [Telephone2], … columns in a table. Use Table Values constructor in CROSS APPLY clause Use the table … Continue reading →
Exporting data to Excel, but to named spreadsheet(s). These notes are taken from an exercise using SSIS 2008R2. Create the Data Flow and export data (in required format) to an Excel Destination (creating an Excel Connection Manager in the process). … Continue reading →
Getting Table and Column details When you want to generate T-SQL code (views, procedures, triggers) automatically you need a list of tables and their columns. It’s useful to have these in temporary tables or table variables because it is … Continue reading →
What is the most efficient, least intrusive, way of getting table row counts (say, for keeping periodic statistics)? Select(*) is accurate. Using index statistics is instant but the statistics could be out of date. dm_db_index_physical_stats Dynamic management view. http://technet.microsoft.com/en-us/library/ms188917.aspx … Continue reading →