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 →
For ease of maintenance all object permissions should be granted to SQL Server roles and users simply made members of one role or another. If you’re using SSDT, roles assigned to users don’t work at all well. I find it … Continue reading →
Column Naming Convention I like lower case with underscores: company_id, first_name, telephone_nbr, booking_ref This is Uncle Joe Celko’s convention as stated in SQL Programming Style (Morgan Kaufmann, 2005), and I accept his justification that Invoices.invoice_nbr is more readable than Invoices.InvoiceNbr in a dense melee of … Continue reading →