I recently helped Jeremiah Peschka's ( blog | Twitter ) in gap analysis of certification differences between 70-432 and 70-450. Jeremiah provided me an voucher to use but inturn set a deadline of 3/25/2010 i.e 14days from providing it. Since I was prepping for 432 for a while before I happened to interact with Jeremiah, i was able to pass it fairly confidently. I used a book authored by Denny Cherry ( blog | Twitter ) and Bret Stateham ( blog | Twitter ) name Real MCTS Prep Kit by Syngress. This...
Read More
Yesterday, I assisted a client who was trying to install a thrid party app to use in Visual Studio. He was using RDP to log on to a remote machine running Windows Server 2003 and run the msi. Each time he tried, he received an error message telling him he did not have access to the file. One quick fix for this is to right click on the file and select 'Unblock'. In this person's case, that option was not available. In that case, there is one other sure fire way of correcting this issue; you can r...
Have you been looking for a way to measure report performance? Want to know who accesses your reports most frequently? Well there are some free reports on http://msftrsprodsamples.codeplex.com/ that you may already have downloaded and don’t realize it that contains this information. When you download the AdventureWorks Report Samples for either 2005 or 2008 a script to create an RSExecutionLog database, an SSIS package to load it, and a set of auditing reports are available in the extremely long...
Occasionally there may be the need to remove the date from the end of a string. This can be accomplished by using PATINDEX along with SUBSTRING. We will use PATINDEX to find where the date begins and then SUBSTRING to remove the date from the end of the file. We will use ClientName03302010.csv and we want to get ClientName.csv from that to process. PATINDEX requires an expression to search for and an expression to search from that we will be returned a number, the location of the searched for ex...
For all the newbies to SSIS, I understand how hard it is to remember what each tasks' operation is for. You have control flow tasks, data flow tasks, sources, destinations etc. There is a lot to remember. So, I have created tables with the task names and definitions. Print it, cut it, fold it in half and you have flash cards to help study. Hopefully you will find this effective. See the link below. http://www.bidn.com/Assets/Uploaded-CMS-Files/5364fbaa-31e0-40f2-b112-c09afb307181SSISControl-Data...
When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load. For example, I have a set of files named: Abc.txt Mno.txt Rts.txt Wln.txt Xyz.txt If I want to skip the file that starts with “W” then I will need an expression in my For Each Loop to detect this file. Inside the For Each loop I am going to place a sequence container. This will give me a place to anchor my expression which I will place on the precedence constraint coming from...
It's very easy to link to an SSRS report from within another application and pass your report's parameters in the URL. To do this, simply add "&", then your parameter name, then "=", and finally the parameter value. You can follow this format for multiple parameters. Here's an example of what a link to a report with parameters in the URL might look like: http://ServerName/ReportServer/Pages/ReportViewer.aspx?%Report+Folder%2fReports%2fReport+Name&rs%3aCommand=Render&StoreID=12345&...
In our next lovely episode we will go further into the murky depths of programming. We will namely the biggest differences between the ideologies of transactional languages and programmatic languages. Once again I will primarily be using C#, this is mainly due to the scientifically proven fact that it isn’t a true computer language unless there are curly brackets, such as this example ‘}’ ! Okay, as we discussed early in the first part, are variables can contain more than just data, the can also...
My client had a desire to show that some numbers were “NULL” instead of either blank or zero (0) when they appeared on a particular report. I looked for a check box that might handle this but no thanks to SSRS I had to create an expression using the IsNumeric Function. Check it out: iif(IsNumeric(Fields!Quantity.Value),Fields!Quantity.Value,”NULL”) If the Quantity is a number, it returns the number. Otherwise the word “NULL” is returned.
As you can see we have two tables, with the same values. Joining on these tables is no problem at all. Simple, right? Well, take a look at what happens when we try to join on the column containing the NULL values. The results are the following: As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result set. The join statement does not deal with NULL values well when joining. So we can use ISNULL to repla...
Checksum vs. Hashbytes What is Checksum? Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an expression or column list. Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case where the update was conditional based on all of the columns being equal or not for a specific row. Without checksums it is a long process of using innerjoins to identify the exact row to u...
There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One : Use the built in stored procedure called sp_who2 Run the command exec sp_who2 This gives a list of users, database names, status, command, program with which they...
Often when designing reports you are required to apply the same formatting to multiple columns in one table or matrix. Since SSRS 2008 does not allow the developer to change the format of a column using Textbox Properties when multiple columns are selected, the developer must either: · Change the formatting one column at a time · Be familiar with the expression language. Neither of the aforementioned choices are difficult, but maybe time constraints eliminates both as viable options. Therefore, ...
Yesterday was the first day of the SQL Server 2008 R2 airlift. I attended a great session on Implementing a Fast Track Data Warehouse. Lots of really interesting information was discussed during the meeting. However, an offline discussion about switching partitioned data in from a stage table provided a good tip that could help reduce fragmentation. If you are constantly switching data into your partition table you could potentially introduce some unwanted fragmentation in your database. The fol...
Analysis Services calculations are great for storing formulas that your users need to see on a regular basis. They also have another little feature that adds just a little more wow when your end users browse the cube. By building a color expression on a calculation you can change either the fore color or background color of a measure value. Here you can write an MDX expression that will change the font or background color of a cell when a user selects the calculation. A very basic example is bel...
I was browsing the Microsoft Learning portal over the weekend and came across the following free or dicounted learning resources for SQL Server. Free Resources: Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform (1 hour) Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence (1 hour) Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development (1 hour) Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services...
Recently I was asked to provide a list of all sql jobs on a certain server running between 2 and 3 in the morning. We have quite a few jobs and it would be time consuming to look through each to determine the time of day each runs. So I used the sysjobs, sysjobsschedules tables to return the job name and times the jobs run. There are several jobs related tables in the msdb database: sysjobs sysschedules sysjobhistory sysjobsteps syscategories The table names are pretty self explanatory. The sysj...
When developing reports that use Analysis Services as a data source end user can sometimes be confused about some of the options they see in report parameters. Anytime you have a parameter that allows for multiple values to be selected then you will see a (Select All) option that Reporting Services adds that make for an easy way to check off each item in the parameter dropdown. If you followed most of the defaults when developing you dimensions in Analysis Services you likely also have an option...
Yesterday, I dealt with a client that was having issues converting his dts packages to SSIS 2008. He was using DTS xChange to convert the packages, but each time he tried to run the migration, he received the following error. "Can not load Tasks. Error : Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due ...
So I finally got through all the SSIS Class Labs including the Group Labs and decided to figure out why I didn't have AdventureWorks2008. I had previously tried to enable FILESTREAM according to some blogs I had read about it but had no success. Maybe I just understand all this a little better now but when I exactly followed these steps right out of the help file it worked for me: 1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and t...
Many times while working with SSIS I have had issues where I would like to add a temp table to a stored procedure that is a source for my SSIS dataflow. One work around for this is create a temp table variable instead. The issue with this is if many rows are in this temp variable the performance can be severely degraded. So the other day I was searching for workarounds and found this link http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/ . Here Michael Cape gives a Wack...
Package Configurations in SSIS are key to making your packaged portable across the entire development process. The problem you being to face with configuration files is the quantity that you collect. Recently I was working on a project where we had pacakge level configurations as well as a configuration for each connection. In order to to updates on this would have taken a very large amount of time, especially since I had to make configuration files for dev and production. I opted to go with con...
There are a number of ways that you can calculate dates using T-SQL. A common practice is to find the first day of the week which can be accomplished easily by using the following function. select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) However, you will notice that this returns the date for Monday as SQL Server uses Monday for the start of the week. If your week starts on Sunday or you need to get the date for the previous Sunday you can simply modify the DATEADD part of the query from adding ...
Today I figured I go back to basics with this blog since a majority of the visitors to this site are primarily DBA's and Database Guru's. This may turn into a series but for now we will discuss a bit of a feared power of sql server, and that is the fact that sql server in fact sits on top of the .net framework. This allows it to access its immense power and array of options that one just couldn’t do efficiently using traditional tools, in both the BI Stack and SQL’s T-SQL function library. This ...
During a T-SQL class that I was recently teaching I was asked if a PIVOT could be performed using a Common Table Expression (CTE). In other words, instead of using the method outlined in SQL Server Books Online that uses a derived table, replace the derived table with a CTE. I have never attempted this approach, but I was confident that it could be done. Using the AdventureWorks database I initially wrote a T-SQL PIVOT statement that uses a derived table, which is as follows: USE AdventureWorks ...