I'm a Microsoft Community Contributor! :)

Hey, I've just received an email from the MCC team with a subject "Your online community contributions have been recognized by Microsoft"!

What is an MCC? According to its description at the site of the MCC program, "The Microsoft Community Contributor badge seeks to recognize notable contributions to Microsoft online communities such as TechNet, MSDN and Microsoft Community. The value of these resources is greatly enhanced by participants who voluntarily contribute their time and energy to improve the online community experience for others. Each day around the world, Microsoft Community Contributor recipients contribute to Microsoft online technical communities in a range of ways, including providing helpful answers, translating online resources into local languages and serving as moderators."

You can read more about the differences between the MCC and the MVP program in a TechNet Wiki page: Differences Between the MVP and MCC Awards.

If you're interested in becoming an MCC or an MVP, check the following TechNet Wiki page: How to Become an MVP or MCC.

Performing and Automating Microsoft BI Related Tasks with PowerShell

I just ran into PowerShell somehow. If you have some familiarity in using .NET, then it should be around 2 hours to pick it up. Of course, you will be on the same basic level like me, but these scripts will work as expected :)

I quicly wrote some scripts and shared them in the TechNet Galleries:

• Listing Enabled SQL Agent Jobs Executing SSIS Packages: there was a request from someone to write it, after they could list all their packages in MSDB with an earlier T-SQL script.
• List Elevated Permissions in an Analysis Services Database (PowerShell): this script is nice to have before starting some security audit activities on SSAS databases.
• Listing Not Processed Objects on an SSAS Instance (PowerShell): this is for those who have a lot of databases, and would like to check if something should be processed.
• Bulk Report Deployment with PowerShell: this is useful for those who have a nice report folder structure, and would like to deploy easily.

If you need some changes to the scripts, please let me know on the Q and A sections of these contributions. If you don't need any changes, and the scripts are OK, then please don't forget to rate them :)

T-SQL script for refreshing all views in a database

There are scenarios when SELECT * is used in views - it's not uncommon in a relational data warehouse.

If WITH SCHEMABINDING is not specified when the view is created, then changing the tables behind the views might corrupt the mapping of the table columns to the output columns, which can have a very confusing result.

A stored procedure called sp_refreshview can be called to correct the mappings. But if you don't have time to look for the corrupted one, you can fabricate a script to loop through all of them.

Or, you can download my one and simply run it. I posted a script to TechNet Galleries, which refreshes all views in a database, regardless of their schema.

Basically it's a cursor solution, calling sp_refreshview for each view in the database.

You can find it here.

SSIS Transformations Wiki: Pivot

Again, a new page for TechNet Wiki, now about the Pivot transformation.
I introduced it through a small example.

SSIS Transformations Wiki: Unpivot

A new page to TechNet Wiki, about the Unpivot transformation.
Of course, I updated the List of Transformations page as well to refer to this one.

Sample: Automatically Incrementing Columns in SSIS

There are cases when you don't have a chance to add an auto-incrementing column to a source of a data flow. Sometimes it's not possible (for example, using a flat file source), sometimes it doesn't make a sense (when the data will be resorted or filtered in the data flow later).

I just uploaded a small example to TechNet Gallery how you can add an auto-incrementing column in the middle of a data flow. It's an SSIS 2008 R2 package - if you need another version, let me know via the Gallery. The package adds two columns to the data flow with Script Components - the sample codes are the same, just written in C# and VB.NET as well.

The package also shows how to use a recursive CTE (in the data source) to generate 500 rows of data. You will need to have a tempdb on your localhost, or to edit the connection manager to refer to any accessible SQL 2008 database.

Ratings, comments and questions are always welcome.

SQL Server 2012 exams and certifications

The changes arrived with SQL Server 2012 are reflected in the related exams and certifications as well.
The most important change is that two MCITP-level certifications will be introduced: the Data Platform and the Business Intelligence certifications. Yeah, the administrator and the developer certifications had been merged.

As the official MS Learning site writes, you'll have to pass five exams to achieve a Professional level (MCITP) certification. Nothing had been shared yet regarding the Technology Specialist level. If you already hold an MCITP certification for SQL Server 2008, it will be enough to pass three of them only, as an upgrade. More details will be provided in mid-April. But if you're planning to clear an MCITP exam on SQL Server 2008 - holding an MCTS SQL Server 2008 certification already -, you can save the fee of one exam by choosing the version upgrade path from MCITP 2008 to MCITP 2012.

It's just a guess from me, but since three of five exams are the same on the Data Platform and on the BI track, I presume these 3 will be needed for the MCTS level.

The common exams for both tracks will be:
70-461: Querying Microsoft SQL Server 2012
70-462: Administering Microsoft SQL Server 2012 Databases
70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

The MCITP level Data Platform exams will be:
70-464: Developing Microsoft SQL Server 2012 Databases
70-465: Designing Database Solutions for Microsoft SQL Server 2012

The MCITP level exams for Business Intelligence will be:
70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

There are some seats announced for a beta exam program, but these are filling up. The exam codes start with 71 in this case.

Good luck!

You can find BIDS features in SQL Server Data Tools for SQL Server 2012

The development environment for business intelligence (and database) solutions - earlier Business Intelligence Development Studio - is called SQL Server Data Tools in SQL Server 2012.
It can be installed using the SQL Server 2012 installation kit.

It's a Visual Studio 2010 Shell in Integrated Mode. Check out my earlier post regarding VS modes, BIDS, and SSMS.

HUG-MSSQL website launched today

Some of you might already know that I organize the Hungarian User Group of Microsoft SQL Server (HUG-MSSQL).

Today we launched its website! There will be blogs, news, and events posted regarding everything about MSSQL and MSBI.

It will be in Hungarian, but some of you already translated this blog with free online tools anyway :)

How to install SQL 2012 on Windows 8

During the weekend, I was playing around with Windows 8 Customer Preview.

First I tried to install SQL Server 2012 RC0 as I had the installer already, but I ran into an issue (missing system CLR types). I decided not to handle this, since SQL Server 2012 RTM is available now. So I tried to install that one instead.

A smaller issue came up again. But Andre Ziegler saved my day with his post in one of the Windows 8 Developer Preview forums.

So, it's better running the script before you start the installation.
You'll have to run cmd as administrator, insert your Windows install disc and type:

dism.exe /online /enable-feature /featurename:NetFX3 /Source:d:\sources\sxs /LimitAccess

Don't forget to check the drive letter of the source and replace it if necessary.

SQL Server 2012 is installed and running now on Windows 8. Thanks Andre :)