How to prevent firing of subscriptions when there is no data in Report?

Many times it is annoying to receive email alert when there is no data in the report. Generally such cases happen when you have scheduled an email subscription for a report which fires at the specified schedule and delivers the report. However as an end user you might be interested to receive an email alert…

Powershell script to script all the Jobs from the given instance of SQL Server

Following is the powershell script to script out all the Jobs in one go. param($sqlserver) [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver $srv.JobServer.Jobs | foreach {$_.Script()} You can copy the above code in a notepad and save it as .ps1 file in some location on server. Next from the Object Explorer in SQL Management…

Do we need a database property setting to control the threshold that triggers Auto Update Statistics?

In SQL Server, our Auto Update Statistics algorithm still relies on the default 20% modification on a given column to trigger update statistics. However different customers have different workloads and for some this threshold value triggers Update stats less frequently (e.g SAP databases) while for some workload the default 20% setting triggers Auto Update Stats…

|

Analytics for Report Server Execution Log using PowerPivot

As we all know, Report Server Execution Log plays a significant role and is the first place to look into in order to analyse & optimize the Performance of Reporting Services. Hence in each version of SQL Server, the ExecutionLog view in Report Server database is enhanced to gather more and more information. If you…

Unable to connect to SQL Analysis Services?

Real Quick Post on one of the issue I faced today while connecting to SQL Analysis Services on my own laptop. Today, when I tried connecting to named instance of SQL Analysis Services on my laptop using SQL Management Studio, I received following error TITLE: Connect to Server —————————— Cannot connect to *********sqlr2. —————————— ADDITIONAL…

A failure was detected for a previous installation, patch, or repair during configuration for features

Recently I was trying to apply a security update for one of my SQL instance and I encountered the following error in my summary log Note: For those who are not aware, summary.txt is located in C:Program FilesMicrosoft SQL Server100Setup BootstrapLog A failure was detected for a previous installation, patch, or repair during configuration for…

| |

Unable to use Calculated Measures in MDX inside Category Groups of Chart Report Item in Reporting Services

Recently, one of our customers came across an interesting issue which many of us face while working with Reporting Services which fetches the data from Analysis Services cube using MDX. So I thought I will share this in this blog post for the benefit of the community. We have a following MDX query with Calculated…

Steps to Failover and Failback a Log Shipping Database in SQL 2000

Recently for one of our customers we had to perform a DR drill activity. To give a background on the environment, they were using SQL 2000 Database as a backend of SAP Application and the SQL Database was log shipped to Remote DR server. We had also customized the Log Shipping to add compression/decompression logic…