One of my favorite things about SQL Server 2012 is the new T-SQL features, especially the new functions. These functions work similarly to some useful functions found in .Net languages and make it easy to do some things that are difficult or messy to do in T-SQL. I have spoken on the new features at many SQL Saturdays and other events over the past year. I have often wondered if these features are ANSI compliant, and wasn’t sure how to find out. This week, I stumbled upon the FIPS_FLAGGER ...
Read More
Hello BIDN family, I just want to share a link to download a free copy of Microsoft Press eBook ‘Microsoft SQL Server 2012 Pocket Consultant’. This could be a great eBook for you to learn all about how to manage SQL Server . Currently, Microsoft Press just posted the first two chapters within this free copy as Managing SQL Servers and Policy-Based Management. So, SQL Geeks !!! Grab your digital copy and learn all about how to manage SQL Server! I have already downloaded and have star...
Somehow I missed this gem that’s been around for quite a while so I thought I would share it in case anyone else has yet to see it. The Forward Engineer add-in is a really great open source project that allows Visio database diagrams you create to easily be converted to T-SQL that will create physical objects in a SQL Server database for you. If you’ve ever used Visio for visualizing existing databases into a diagram then you’ve likely used the standard Reverse Engineer option....
Partially due to the ease of carrying hundreds or thousands of books around with you easily today (Thank you Amazon for the Kindle!), I am reading more non-technical books. I recently read “ The Last Lecture ” by Randy Pausch. You may have heard about this lecture, available on youtube.com . Many college professors give a “last lecture” based on what they would like to say if it was actually the last lecture they would ever give. Randy was a computer science professor at ...
PowerPivot is a powerful new tool from Microsoft that has been improved even more in the 2012 release, which you can download for free here . In this blog I will give you a PowerPivot workbook that I created to connect to the Reporting Services logs and pull data. This gives you the ability to see the performance of your report server very easily. Report Example: Keep in mind that all the data in PowerPivot is pulled into memory so if you have a large reporting environment you may have to limit ...
If I am tasked with troubleshooting relational report performance there are a number of processes I will follow to identify exactly where the bottlenecks exist so I can focus my performance tuning on the areas that will have the most impact. I'd like to share these processes with the community and invite others to contribute their own tips in the comments section. I'll try to update this blog post with best suggestions as they come available. This post will focus on relational reports as opposed...
If you are looking for the slids and code for my St. Louis Days of .Net session, you can find them here .
I haven't logged in my Sql Express application for a week now because I've been in the Foundation Class offered by PragmaticWorks. When I decided to log in tonight I received the error that you see below: Cannot connect to TECH-PC\SQLEXPRESS. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (pro...
It was a tough week but a very good one also. I learned a ton in that week, way more than I thought was posible to learn in a week but I feel comfortable writing query's and doing reports. I don't know what is expected by I feel that I could go into a company and do SSRS with little problem, all I would need to know is a few things company specific but I feel very confident in my SSRS skills at this moment. The interviews went great, I actually have some of the recruiters say they would forward ...
In my journey to become better at writing T-SQL code, I began studying cursors this week. For anyone who is interested, I have included some of my notes from Murach's 2008 Developer Book. Enjoy! A cursor is a database object that points to a result set. You use a cursor to identify a row you want to retrieve from the result set. Cursors are most often used by application programs that work with the data in a database. In most cases, these programs use a standard API (application programming inte...
Today in the Foundation class we reviewed JOINS a lot more and started working in the GUI for the JOINS. I do like how the GUI can help you understand the relationships going on and can get your JOINS started but at the same time it is a pain in the gluteus maximus. When it creates the querry it is formatted in a very non-userfriendly format, so you have to go back through and set proper alias'. Then after you set up the joins you have to go back and set up the clauses and other information you ...
First, I want to thank Tony at pragmaticworks for his help and for sharing his story with me. Very encouraging words and good advice. Instead of writing a long blog detailing what I learned this week, I have decided to just insert some code from a finished product. The query below is a result of some of the awesome stuff I learned this week in the Foundation class! I know this sql statement is pretty basic but i'm excited with the results and how many different functions could be incorporated in...
My name is Mitchell Pearson and I'm currently in the Foundation class presented by PragmaticWorks. First of all I would like to thank Brian Knight and the team at PragmaticWorks for presenting this class. The class is very fast paced and covers a lot of material in a short amount of time. However the team members here have been extremely helpful and encouraging. Blogging: I decided last night that I was going blog about the difference between Primary Keys and Foreign Keys. I was having a little ...
I first heard about the Foundation through my teacher at SJRSC, Professor Holbert, last September and imediately was interested in it. I came down to the building in September but due to my current school schedule at the time it was an oppurtuned time to do it. So I waiting and now I am in Foundation and I'm loving it. I have never worked with Databases and T-SQL, but I have had classes in C++, Python, VisualBasic.NET, and HTML/CSS. A lot of the language is the same and the basic layouts are the...
Last week I covered how to add a Data Tap to the execution of a 2012 SSIS package . What I showed you will work fine for many packages, but how do you add a Data Tap to a child package? I spent quite a bit of time trying to figure out how to do this, and the only thing I found was this sentence from Books Online: If an execution contains a parent package and one or more child packages, you need to add a data tap for each package that you want to tap data for. I was this close to emailing a frien...
DTSxChange is a wonderful tool that I’ve had the privilege of using while working on multiple DTS conversion projects. Not only does it make converting DTS packages to SSIS packages much, much easier, it also does the job in less than half of the time with very minimal manual labor. One of the great features of DTSxChange is the Profiler. The Profiler profiles the DTS packages in your environment and finds all the information about your DTS packages for scoping of projects and estimating n...
BIxPress has a lot of cool features that makes SSIS development easy. One example of a tool that makes BIxPress great is the Quick Config Editor. This tool that allows you to make multiple changes to configuration files. You have a grid view and an XML view. If you’re comfortable with editing XML then more power to you. The grid view however gives you only the information you need. It gives you the configurable object name, property and value. Here you can change and save configuration pro...
Over the past year I’ve had the pleasure of creating/converting SSIS packages for clients. At the same time, I have also had the pleasure of using BIxPress to create configurations for packages using the SSIS Configuration Wizard. Whoa, whoa, whoa you might say. Really? Is he really talking about how much he has enjoyed creating configurations? Yes T.J. I am. While I have nothing against using the native Package Configurations Organizer which is straightforward and less robust, I have cert...
BIxPress simplifies SSIS and makes your life much easier as developer. BIxPress is a plug in for your BIDS environment and removes a lot of redundancy in everyday SSIS development. It can be launched within BIDS or as a standalone application. So the question is how can BIxPress help you? One way that this product can help is through rapid package development with the Package Builder Wizard. For instance, let’s say you had a SSIS package for loading a data warehouse. Wouldn’t it suck...
OLAP PivotTable Extensions is a really cool free add-in for Excel that’s been around for quite a while now. It allows you to have some exposure to what’s going on under the covers (Among many other features) while you browse a cube using an Excel PivotTable. This weekend there has been an updated version and you should check out the features and download it now! http://olappivottableextend.codeplex.com The new features in this release are: Support for Excel 2013 Upgrading to the AS20...
This totally killed me for longer than I'll ever admit. I sat with DBAs running profiler traces, looking at execution logs, and running various tests. Turns out it's Reporting Services 101 (someone else's words, not mine.) As soon as my colleage said the words "Interactive Page Size" I slapped myself in the forehead. Anyway, I wanted to share this so no one else goes through what I went through. Symptoms: Report takes too long to render or never renders. Data retrieval through stored procedure o...
SELECT * FROM…when administrators see that from developer code, we generally tend to cringe. Why? In a nutshell, it’s terrible on a few fronts. First, typically that SELECT * FROM that gets written (a lot of the times) lacks a WHERE clause. What’s the problem? Well, that pulls back every single row from the table. Sure, that may not be too bad on a table with a few hundred rows but what about on a table with millions? That could cause a lot of performance problems since you&rs...
I recently decided it was time to figure out how to set up a Data Tap, one of the new features of SSIS 2012. I found it wasn’t difficult to do at all so I thought I would share what I learned with you. First of all, let’s review just what a Data Tap is. A Data Tap is an output of the data from one of the data flow paths in your package. Data Taps are a lot like Data Viewers. You can add Data Viewers in design mode while working with the package inside Visual Studio (VS*) as a debuggi...
Color expressions in SSAS allow you to build an MDX expression to control the color of text displayed in a calculation. This property can be found in the Calculations tab of the cube editor when working in BIDS. Simply select a calcuation and look for the section labeled Color Expressions between the Display Folder and Font Expressions in the Additional Properties. Simply enter a condition in the box as shown above when the results of the condition being the colors you would like the text to be....
Hello BIDN Family, After long time gap, I just want to post a blog that might be helpful to the one who is going to learn, try and setup these great products of Microsoft as SQL Server 2012 and SharePoint 2010. If you are going to learn, try and install SQL Server 2012 integrated along with SharePoint 2010 then this blog might be very helpful. Some of the things you should be consider before installing and some steps for proper installation are as: Please read the requirements of your machine an...