Upcoming Conferences 2012: PASS SQLSaturday in Munich, SQLCon in Mainz, PASS SQLRally in Copenhagen, PASS Summit in Seattle, PASS Camp in Darmstadt

..ough
After I already launched the SQL Server 2012 together with Microsoft at Cologne this February, this year’s second half I will be speaker at 5 Conferences almost in a row:

 

SQLSaturday #170 - Munich 2012

 

Basta! 2012 Speaker

 

SQL PASS Rally Nordic

 

  • From October 22nd – 25th I will hold several sessions on:
    AlwaysOn and ReadOnly Routing”, “Data Corruption Survival with CHECKDB”,  “Security” and “Tracing with Extended Events
    in the track
    SQL Server 2012 Toolbelt for DBA’s and Developer
    in Seeheim, close to Darmstadt, Germany at the PASS Camp

 

SQL PASS Summit 2012

 

what a year..!

I hope to see you around at some of those places.

“Brought to you this summer”: Microsoft Certified Master SQL Server

(en)
as some of you already may have heard: this June I passed my Lab Exam for the Microsoft Certified Master SQL Server 2008.
- almost exactly 3 months after my first attempt. In fact the Exam has already taken place in the middle of May. - Unfortunately this time I had to wait more than 30 days until I received my result, and it took another couple of weeks, until my name made it onto the official MCM-List: http://www.microsoft.com/learning/en-us/mcm-certification.aspx

The MCM is the highest technical certification that Microsoft offers (http://www.microsoft.com/learning/en/us/certification/master-sql.aspx), and unlike the MCITP or even MVP the MCM is only awarded for a so called knowledge exam plus a practical lab exam of approximately 5 1/2 hours practical problem solving on a SQL Server box.
In fact, as of September 2012, I am the only Microsoft Master for SQL Server in Germany outside of Microsoft and affiliates. :-)

At TechNet’s Master Blog you can now also find a short introduction of myself (Thank you, Bob Taylor): http://blogs.technet.com/b/themasterblog/archive/2012/08/20/introducing-our-latest-sql-server-2008-microsoft-certified-master-andreas-wolter.aspx

Thanks to all the people that believed in me, and to those that made me pursue it even more determined :-)
Among them: Paul Randall and Kimberley Tripp from SQLSkills - my number one teachers, in general terms, since several years, Jonathan Kehayias and Bob Beauchemin who added valuable knowledge in SQL OS and Developing, Robert Davis, who graded my first Lab within hours, and last but not least: Ralf Dietrich, my long term sparring partner for SQL Server and uncertified Master in Germany ;-)

Also I have to apologize to all my customers for often letting them wait long because I made my studies number one priority. It will be made up by even better and more effective consulting skills :-)

 

(de)
wie viele sicher schon gehört haben, habe ich diesen Juni das Lab Exam für den Microsoft Certified Master SQL Server 2008 bestanden.
- fast genau 3 Monate nach meinem ersten Versuch. Tatsächlich war das Exam schon Mitte May. – Leider musste ich diesmal mehr als 30 Tage auf meine Ergebnisse warten, und es dauerte weitere Wochen, bis mein Name es auf die offizielle MCM-Liste machte: http://www.microsoft.com/learning/en-us/mcm-certification.aspx

Der MCM ist die höchste technische Zertifizierung, die Microsoft anbietet (http://www.microsoft.com/learning/en/us/certification/master-sql.aspx), und anders als der MCITP oder sogar MVP wird der MCM nur nach einem sogenannten knowledge exam plus einem praktischen lab exam von rund 5 1/2 Stunden praktischem Problemlösen an einer SQL Server box verliehen.
Bislang (Stand Oktober 2012) bin ich sogar der einzige von Microsoft unabhängige Master für SQL Server in Deutschland. :-)

Auf dem Master Blog von TechNet finden sie eine kurze Vorstellung über mich (Thank you, Bob Taylor): http://blogs.technet.com/b/themasterblog/archive/2012/08/20/introducing-our-latest-sql-server-2008-microsoft-certified-master-andreas-wolter.aspx

Mein Dank geht an alle, die an mich glaubten, und die, welche mich das Ziel noch entschlossener verfolgen ließen :-)
Unter ihnen: Paul Randall und Kimberley Tripp von SQLSkills – meine, im weitesten Sinne, Nummer eins Tutoren seit einigen Jahren, Jonathan Kehayias und Bob Beauchemin, die wertvolles Wissen in SQL OS und Developing beisteuerten, Robert Davis, der mein erstes Lab innerhalb von wenigen Stunden benotete, und last but not least: Ralf Dietrich, mein langjähriger Sparring Partner für SQL Server und uncertified Master in Deutschland ;-)

Bei meinen Kunden muss ich mich entschuldigen, dass ich sie oft lange warten ließ, da meine Studien oberste Priorität bei mir wurden. Das wird durch noch bessere und effizientere Consulting-Skills wettgemacht werden :-)

  MCM 2008

Andreas Wolter

CEO | Sr Technical Consultant & Architect Databases & BI

SARPEDON Quality Lab

Certification-Status as of Oct. 2012 (SQL Server Version in brackets):

MCM (2008), MCT (2005 – 2012), MCSA (2008), MCITPDD (2005 - 2008), MCITPBID (2008), MCITPDA (2005-2008), MCDBA (2000), MCSA (Win 2003), MCTS (2005 – 2008) 

Andreas Wolter

Geschäftsführer | Sr Technical Consultant & Architect Datenbanken & BI

SARPEDON Quality Lab

Zertifizierungs-Status Stand Okt. 2012 (SQL Server Version in Klammern):

MCM (2008), MCT (2005 – 2012), MCSA (2008), MCITPDD (2005 - 2008), MCITPBID (2008), MCITPDA (2005-2008), MCDBA (2000), MCSA (Win 2003), MCTS (2005 – 2008)

 

Sessions auf der SQLCon 2011

Auch dieses Jahr bin ich wieder mit bis dato zwei Sessions auf der SQLCon 2011 – 26. – 29. September in Mainz vertreten.

Update (09/2011): Den Vortrag “Reporting Services in SQL Server Denali” habe ich zugunsten eines mir noch mehr am Herzen liegenden Themas gestrichen. (Außerdem werden die Reporting Services selber kaum viele Neuereungen in Denali erfahren)
Dafür halte ich eine Session zu den Sicherheits-Features & Techniken von SQL Server für Entwickler:

“Schutz gegen SQL Injection sollte mittlerweile zum Repertoire jedes Entwicklers gehören. Jedoch gibt es noch andere Wege, an sensible Daten zu gelangen oder sie zu manipulieren. In dieser demointensiven Session (Achtung: Code, Code) werden wir uns vor allem anderen Techniken widmen, die man im Repertoire haben sollte, die Sicherheit seiner Daten zu stärken. Dazu gehören Basics wie Schema-Design für Security, Besitzerketten und ihre Fallstricke, Codesignierung und Verschlüsselung für die kritischsten Daten.“

”Wer Berichte mit Reporting Services erstellt, wird feststellen, dass ganz schnell nach weiteren verlangt wird. Und früher oder später kommt der Ruf nach einem einheitlichen Aussehen. Die Unterstützung dafür out of the box ist eher schwach. Dennoch kann man mit geschickter Kombination der zur Verfügung stehenden Möglichkeiten eine starke Effizienz-Steigerung beim Erstellen neuer Berichte erreichen.“

 

Ich würde mich freuen, den einen oder anderen persönlich, “offline”, begrüßen zu können.

Andreas Wolter

Sarpedon Quality Lab

Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 3: The Style-Template

In the first part we learned how to integrate a Layout-Template into BIDS. The second technique in this series we are looking at will be using the “Style-Template”.

What is a Style-Template?:

Style-Templates are a set of definitions such as font size, font type, font color, background color, border styles, etc., which is being used from within the “Add New Report” wizard inside Business Intelligence Development Studio.

Report Layout-Templates are shown in the Project-Dialogue “Add“ – “New Item“ .

image

This is where you usually chose among those beautiful styles, that Microsoft provides you with (Slate, Forest, Corporate, Ocean, Bold, Generic) Open-mouthed smile.

image

But: you can include your own style definition!

And this is how we can create a Style-Template:

1) Open the file StyleTemplates.xml from the following location (watch for your locale specific folder at the end) using your favorite XML Editor (notepad is sufficient):

for Reporting Services 2005

•%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\en

for Reporting Services 2008 x64

•%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\en

for 32-bit Reporting Services 2008

  • C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\en

2) I strongly advice you create a backup of StyleTemplates.xml before you edit it:

3) Look for a complete “StyleTemplate”-node like “<StyleTemplate Name="Slate"> … </StyleTemplate>” and copy it to the end, just before the Generic-Style (<StyleTemplate Name="Generic">)

4) Rename the copied definition

5) Now you can start changing colors, border styles, fonts etc.

You will find out, that not everything will be accepted. There is no documentation and so far this is what I can give you as a set of rules I found out the hard way – hope it helps saving you time:

Rules of engagement for producing a Style-Template:

  • Backup StyleTemplates.xml before editing it!
  • Watch out for whitespaces and line breaks. Otherwise you will get Error messages such as “Red is not a valid color”.
  • Not all Attributes are supported and give you an error when running the wizard.
    • I.e. <TopBorder> etc.
  • Some fail silently like “<BorderWidth>”
  • Color values have no spaces between words
    • “Dark Olive Green” is coded “DarkOliveGreen”
  • Font names can include spaces
    • <FontFamily>Times New Roman</FontFamily>
  • TextAlign does not work in "Table Header" – sometimes align in header rows is out of order for no reason
  • default font for table does not work here either

Sample Style-Template code:

<StyleTemplate Name="Pizza_Time">
    <Label>Pizza_Time</Label>
    <Styles>
        <Style Name="Title">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>20pt</FontSize>
            <Color>Red</Color>
            <TextDecoration>Underline</TextDecoration>
        </Style>
        <Style Name="Page Group Level 1">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>14pt</FontSize>
        </Style>
        <Style Name="Page Group Level 2">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>12pt</FontSize>
        </Style>
        <Style Name="Page Group Level 3+">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>10pt</FontSize>
        </Style>
        <Style Name="Table"></Style>
        <Style Name="Matrix"></Style>
        <Style Name="Table Header">
            <BackgroundColor>Tomato</BackgroundColor>
            <FontFamily>Tahoma</FontFamily>
            <FontSize>11pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>White</Color>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
        <Style Name="Group Level 1">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>11pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>DarkOliveGreen</Color>
            <BackgroundColor>Wheat</BackgroundColor>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
        <Style Name="Group Level 1 Textbox">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>11pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>DarkOliveGreen</Color>
            <BackgroundColor>Wheat</BackgroundColor>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
        <Style Name="Group Level 2">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>10pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>DarkOliveGreen</Color>
            <BackgroundColor>LightSalmon</BackgroundColor>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
        <Style Name="Group Level 2 Textbox">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>10pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>DarkOliveGreen</Color>
            <BackgroundColor>LightSalmon</BackgroundColor>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
        <Style Name="Detail">
            <FontFamily>Tahoma</FontFamily>
            <FontSize>10pt</FontSize>
            <FontWeight>Bold</FontWeight>
            <Color>White</Color>
            <BackgroundColor>LimeGreen</BackgroundColor>
            <BorderStyle>
                <Default>Solid</Default>
            </BorderStyle>
            <BorderColor>
                <Default>DimGray</Default>
            </BorderColor>
        </Style>
    </Styles>
</StyleTemplate>

You can of course include several sets of Style-Templates.

How to test?

Just run the “Add new Report”-wizard from inside BIDS, and you will see your own style appear in the list. Unfortunately there will be no picture-preview for user defined styles, so use a descriptive name.

image

And again, just like the Layout Templates, you could xcopy deploy this folder to all your Developers machines.

How about “centralizing”?

  • Just as Report Layout Templates you can “xcopy deploy” this folder regularly to all your Developers machines.
  • But again: once the reports are created, and you make a change to the originating template, existing reports will not be touched.

How about “standardizing”?

  • If you “xcopy deploy” this folder regularly to all your Developers machines, all developers can easily use the same colors and fonts for newly created tables & matrixes.

In short: What Style-Templates can be used for:

  • If you like using the „New Report“-wizard for quickly starting a report:
  • Have the first Table or Matrix finalized with the corporate-design colors and font-styles
  • Includes a report-title and body-color

Note:

  • Does NOT apply to elements added thereafter
  • Once the report is created, the style is hard-coded

 

References:

the only reference with code samples on the web is this post from 2004 here by a Microsoft guy:
http://www.ms-news.net/f3037/styletemplates-xml-tags-2920674.html#post9344712

The second link I’ll provide just gives a general overview over the two methods “Report (Layout) Template” and “Report Style Template”:
http://blogs.microsoft.co.il/blogs/yanivy/archive/2010/08/03/create-custom-templates-and-styles-for-ssrs-2008.aspx

 

In the next Part of the series, I will show how to use custom code for managing style.

Andreas

Sarpedon Quality Lab

Standardizing and Centralizing Report Design (or: creating style sheets for reports) Part 2: The Layout-Template

As a first technique in this series we will look at what I will call “Layout template”.

I chose the Term “Layout-Template” for two reasons:

first: the main thing that they can be used for is, to define general report sizing, footer and header elements – in other words, the overall layout of the report.

secondly: in the next part of this series we will see a different type of template which will actually be called “Style-Template”.

 

So, what is a Layout-Template?:

A Report Template can be as simple as a standard report with a certain page width and height, certain colors, a standard company logo etc., which can be copied over and over again.

Here the Problem is, how to avoid to overwrite the defined Report Template unintentionally, and, how to make it easily available.. even (report-)project-independent.

And this is where “Layout-Templates” come at hand.

 

Report Layout-Templates are shown in the Project-Dialogue “Add“ – “New Item“ .

image

There you can choose a Template and it will create a copy local to your project – and not overwrite the Rdl at its original location.

And this is how we create a Report Layout-Template:

1) Simply create a regular report, configure size, header and footer and so on. You could create a data region, but it would make little sense – just as a sample maybe.

2) Then you put the .rdl-file in the following location:

for Reporting Services 2005

  • %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

for Reporting Services 2008 x64

  • %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

for 32-bit Reporting Services 2008

  • C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

3) and when you right-click at the reports-node in your project, click “Add“ and then chose “New Item“, your Report Templates will appear in the “Add New Item” box:

image

That’s it. Very simple.

How about “centralizing”?

  • You can “xcopy deploy” this folder regularly to all your Developers machines.
  • But: once the reports are created, and you make a change to the originating template, existing reports will not be touched.

How about “standardizing”?

  • If you “xcopy deploy” this folder regularly to all your Developers machines, all your reports should have the same Layout in terms of sizing, header&footer.

In short: What Layout-Templates can be used for:

  • Having predefined report size, (i.e. landscaped and portrait version)
  • Include report-header and footer with corporate design and navigational elements
  • Can even include sample data-regions with appropriate fonts and colors

 

In the next Part of the series, you will see how to create and use a Style-Template.

Andreas

Sarpedon Quality Lab