MSBI - Business Intelligence

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure. Let us create one very simple CLR where we will print current system datetime. 1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project 2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference. 3) If you have selected existing reference skip to next step or a...

When you create and schedule a Data Driven Subscription for a SQL Server Reporting Services (SSRS) report a job is created with a very meaningless name.    The name is actually a uniqueidentifier and is stored as such in the dbo.Schedule table in the ReportServer database.    I was recently asked if it was possible to use the Job Name to identify the Report associated with the job.    After a little digging I was able to produce the following query, which associates the Job Name with the Report Name: SELECT              s . ScheduleID Job_Name ,              su . Description  Subscription_Description ,              c . Name Report_Name FROM  dbo . Schedule s INNER   JOIN  dbo . ReportSchedule rs         ...

Detailed Overview SSIS Transformations

In the SSRS Reporting Environment, you may never encounter this error and you live a happy and easy life. We all know an SSRS report can easily ping your server to 100% CPU if you decide to export a 120 page report, to PDF,  with charts, etc. etc. You may get a complaint that performance suffers in another application while running the reports and you want to fix it, but do not know how. Then one day you or someone at your firm has decided to do your SQL Server a favor and separate out SSRS from the SQL Server. This sounds like a great idea. You build a new SSRS box (lets call it SSRSPROD), run some performance optimizations (http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx) and you are set. You remote onto the new server, deploy all of your reports, open up IE on your server and check the reports; they run and life is good. You give the all clear to your users and suddenly your inbox is flooded with "My Reports do not work,...

Been awhile since I had posted anything and what better way to restart this blog than with some Timeout Issues. Now this list is designed for SSRS 2005, but you can put a few of these tips to use in 2008. Consider these steps in the below order. 1) Set the HttpRuntime ExecutionTimeout value to a greater value (ie 3 hours) on the Report Server How do you do this? Simple! a.       Open the ReportServer’s Web.config file by going to Start -> Administrative Tools -> Internet Information Services. b.      From there, expand Web Sites -> Default Web Site, and click on ReportServer. On the right-hand pane, right-click ‘Web.Config’ and select ‘Open’. c.       Locate the HttpRuntime parameter. If it doesn’t exist, you will have to create it within the <System.Web> section. d.      Set the executionTimeout value to ‘10800’ (3 hours) as shown below: <system.we...

Ques: What is .LDF – SQL Server? Ans:  I was quite surprised to see search engines throwing very less information on this one, yet it’s a frequently asked question. LDF acronym stands for Log Data File (though could have been called – Transactional Log Data File). This is a must to have file, and is created at the same time the database file (.mdf) is created by SQL Server. So why do you need it?  For keeping .mdf consistent. To cut short I will copy these lines straight from  wiki  : “SQL Server ensures that any change to the data is ACID-compliant, i.e., it uses transactions to ensure that any operation either totally completes or is undone if fails, but never leaves the database in an intermediate state. Using transactions, a sequence of actions can be grouped together, with the guarantee that either all actions will succeed or none will. SQL Server implements transactions using a write-ahead log. Any changes made to any page will update the in-memory cache of ...