SSAS– Resolving target machine actively refused

I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS.  Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance. With lot of excitement I tried...

Read More

SQL Server–Security Risk Assessment PMP Way

Recently I had a chance to speak my friend Ashish Sharma who is my colleague too. We were discussing Project Management topics (he recently passed PMP and ACP). Our discussion ended at very interesting topic – RISK Assessment. Definition: According to PMBOK, Projects can have unforeseen event or activity that can impact the project’s progress, result or an outcome in negative or positive way. Further, A Risk can be assessed using two...

Read More

MSDB– cleanup is necessary

Recently I have been asked to work on the task which is to reduce the size of MSDB database. The MSDB has grown to 20 Gigs. Well, you would say, what is the big deal with that? 20 gigs is not considered a big for database. Correct, but for MSDB, yes it is. So, the question is - why the size has grown this much, and what would be the adverse impact it would have on the performance on my system? I would say, there could me many reasons,...

Read More

Not so good news for MCM Aspirants

Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me. I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified....

Read More

SSRS – Download all RDL files from Report Server in one go.

Introduction People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once. Implementation SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls...

Read More

How to collect cluster Information using TSQL

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script....

Read More

SQL Server – Generate Calendar using TSQL

Introduction Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day. Showing the events in the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises...

Read More

SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script. DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted DECLARE @NoOfChars AS INT = LEN(@Value) DECLARE @Permutations TABLE (Value VARCHAR(20)) --Make sure the size of this Value is equal to your input string length (@Value) ; WITH NumTally AS ( --Prepare the Tally Table to separate each character of the Value. SELECT 1...

Read More

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve those records. Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process. I...

Read More