SQL Sever 2016 Reporting Services Cookbook has arrived!

 

SQL Server 2016 Reporting Services Cookbook by [Priyankara, Dinesh, Cain, Robert C.]I’m proud to announce my latest book, the SQL Server 2016 Reporting Services Cookbook, has been released! This was a real labor of love, it consumed most of my summer and well into the fall.

This book was published via Packt Press, and my coauthor was Dinesh Priyankara (blog | twitter).

In this book we cover recipes for almost all aspects of SQL Server Reporting Services. What’s inside? Just take a look:

Chapter 1 – Getting it Ready – Configuring Reporting Services.

Chapter 2 – Authoring Reports with SQL Server Data Tools

Chapter 3 – Advanced Report Authoring with SQL Server Data Tools

Chapter 4 – Authoring Reports with Report Builder

Chapter 5 – Improving User Experience – New Designing and Visualization Enhancements

Chapter 6 – Authoring Reports with the Mobile Report Publisher

Chapter 7 – Consuming Reports – Report Access Enhancements

Chapter 8 – Reporting Solutions for BI – Integration

Chapter 9 – SharePoint Integration

Chapter 10 – Administering and Managing Reporting Services

Chapter 11 – Securing Reports in Reporting Services

Chapter 12 – Custom Programming and Integration to .NET applications

That’s a lot of great material, over 500 pages of Reporting Services fun.

You can get the book through the publisher site:

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

or use this shortcut: http://bit.ly/ssrscook

You can also get it on Amazon.

https://www.amazon.com/Server-2016-Reporting-Services-Cookbook-ebook/dp/B01HY3TC68/ref=tmm_kin_swatch_0?_encoding=UTF8&qid=1480527666&sr=8-6

Or use the shortcut: http://bit.ly/ssrscookbook

Note as of this blog post Amazon has the Kindle version ready, the print version still shows as a preorder, but that will be out shortly. If you want the print version consider going to the publisher site as you can get both the print and e-book version for one low price.

I want to thank my coauthor, Dinesh, who did a great job on his half of the book, as well as in designing the overall contents. Also a shout out to our editor, Amrita, who kept us in line and on track.

Enjoy!

PowerShell Book Suggestions from IT/Dev Connections

In my session today at IT/Dev Connections in Las Vegas, several people asked me for book recommendations on PowerShell in general, and DSC (Desired State Configuration) in particular.

The book I’ve used for a while is from APress, Windows PowerShell Desired State Configuration Revealed.

I found a second DSC book today, Learning PowerShell DSC. To be honest I just found it, but from looking over the table of contents it looks pretty nice. I’ll report back later when I’ve had time to go over it in more depth. (Disclaimer, the publisher this book is also the publisher of my upcoming book, otherwise I don’t have any other affiliation).

A good all around yet deep book is PowerShell In Depth. Two of the authors, Don Jones and Jeffery Hicks, are here with us at IT/Dev Connections. Note the link is to the paper version, if you want the Kindle / iBooks / PDF version you’ll have to go directly to the publishers website. 

Finally, if you are seeking something that would be a fast read but still provide great value, I’d suggest “Learn PowerShell in a Month of Lunches”. The next version will be out in December of this year, but if you can’t wait, you can find the current version here. As with the previous recommendation, you can get the electronic version from the publishers website.

ArcaneCode–Headed your way!

I’ve not done much blogging, as I’ve been swamped with other activities. In addition I’ll be doing quite a bit of speaking, so let me catch you up.

Recently I did a webinar for Pluralsight, “Why you should invest in PowerShell”. If you missed it the recording is now up, take a look, it’s free!

http://go.pluralsight.com/C0010781

Next, I just completed the first draft of my fifth book, SQL Server 2016 Reporting Services Cookbook. I’m coauthoring with another MVP and great guy, Dinesh Priyankara. The book is available from PACKT Publishing in Alpha form.

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

I’ll make my debut appearance at IT/Dev Connections next week. I’ll be doing two sessions, the first is on October 11th, 2016: Zero to Hero with PowerShell and SQL Server. We’ll begin with a quick overview of PowerShell, then dive into using it with SQL Server. You’ll see examples of using it for both maintenance and development tasks.

The next day is my second session is “So You Think MDX is Hard?”. This is for people who are new to MDX, and want to learn. You’ll see how to start from no knowledge all the way to building calculated members and sets.

If you’ll be at IT/Dev Connections feel free to come by and say hi, would love to meet as many as possible.

As if that’s not enough, on Saturday October 15th 2016 I will be at the DevSpaces Conference in Huntsville AL. At 4pm I’ll be presenting “High Class PowerShell: Objects and Classes in PowerShell”. You’ll see how to create your own classes using PowerShell. We’ll cover techniques valid in PowerShell versions 3 and 4, as well as see how the new class types in PowerShell version 5 work.

As they say on TV, but wait! There’s more!

On November 1st 2016 I will be coming to Atlanta to the Atlanta SQL Server BI user group. My presentation “Shiny and New: SQL Server 2016 Reporting Services” should be a lot of fun, and introduce you to the new features in SQL Server 2016.

Whew! There’s two more Atlanta based events in Nov/Dec it looks like I’ll be at, once those are finalized I’ll let everyone know.

I’ve also established a GitHub repository for my various samples. You’ll find it at https://github.com/arcanecode. As I move forward I’ll keep this repository updated.

Zero to Hero with PowerShell and SQL Server–Precon at DevDataDay

{dev = data} DAY Birmingham 2016Are you looking to learn about PowerShell? Do you use SQL Server? Then boy have I got a bargain for you!

I’m doing a precon for our upcoming devdataday event. The precon will take place on Friday, August 19th, the day before devdataday.

This is designed to be a course for the PowerShell novice. No prior knowledge required! I’ll start the day with an introduction to PowerShell, basic usage and cmdlets. Next we’ll dive into programming scripts in PowerShell, starting with the basics of loops, conditional logic, and functions. We’ll move into advanced topics, including creating your own reusable modules and testing them.
The real meat of the day comes next, learning how to interact with SQL Server from PowerShell. Both the SQL Provider and SMO (SQL Management Objects) DLL library will be covered in full. Most importantly you’ll learn how to read the online SMO library documentation and how to convert the examples into PowerShell syntax. The day will culminate by building a reusable module for working with many SQL Server common tasks, such as T-SQL code generation, or performing health checks on your servers.
The day will wrap up with a look at Pester, the new open source PowerShell testing tool. You’ll see how to test all the code you generated during the day. Don’t be left behind, learn how to leverage the power of PowerShell in your SQL Server environment.

To register, just go to https://devdatadaypowershellprecon.eventbrite.com/

Updating and Sorting the Microsoft Word QuickStyle Gallery with PowerShell

 

Introduction

I’m currently working on my fifth book, this one is for the folks at PACKT Publishing. It’s still in the early stages so I can’t say too much, but if you like SQL Server Reporting Services, you’ll enjoy this book.

All book publishers use their own set of styles in Microsoft Word to allow them to control the layout of the book when it goes to print. There will be special styles for italics, bold, code listings, and more. The techniques here though are applicable to more than just book or magazine publishers.

You’re company may have a limited set of styles it approves for use in internal documents. Perhaps you need to adjust your set of QuickStyles based on what type of document you are authoring. One set for company memos, another for technical documentation, and yet another set of styles for love letters.

Using the PowerShell script in this post can make it easy for you to setup your QuickStyle gallery based on the type of document you are creating.

When working on the book it’s been convenient to clear out the list of built in styles in the QuickStyle gallery, and replace them with the ones I need for my publisher. Doing it manually though is a time consuming process.

Unfortunately I experienced further oddities. Every time I got a chapter back from my editor, my curated list of styles had been replaced. I don’t think it’s anything my editor did deliberately, she’s a really nice lady. I believe it’s some quirk of Word, related to the fact we appear to be on different versions. I knew I couldn’t keep sucking up time adding, removing, and sorting these each time I got a chapter back. I needed a way to automate.

I did some looking online, but couldn’t find a total solution that fit my needs. I found some VBA code for adding and removing styles from the QuickStyle gallery, but nothing on how to sort items in the gallery.

Being the geek I am means I love PowerShell. So I rolled up my sleeves and dove into the Microsoft Word object model. Specifically, I focused in on the Style object.

I’m a firm believer in the adage that you should completely understand any script you cut and paste off the web. So while I’ll place the complete script at the bottom of this post for easy copy / paste, I’ll step through it first to explain the various components.

Before I proceed, one important note: updating the QuickStyle gallery using the code below only updates one specific document. Other existing Word documents, or new documents you create, won’t be affected by this script.

1. Set a variable to hold the file name to update.

$wordFile = 'C:\Book\Chapter02.docx'

I start by setting a variable to hold the file name of the Word document I want to update. At some point I’ll probably turn this into a module and make this a parameter to the main routine, but for today this gets the job done and is easy for people who also want to use the script.

2. Back up the file.

$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

 

The next thing I do is make a backup. First, if something happens I want to be able to go back. Second, you should always backup before doing something like this, so making it built in prevents issues (as well as avoiding nasty comments on this post). Winking smile

3. Load an array with your desired styles.

$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

Next up, I load a list of the styles I want to place in my QuickStyles. Also, the array should have the styles in the order you wish them in the gallery. This list is specific to the styles I use most frequently on this project. To see the complete list of available styles within your document, open up Word and load your document. On the bottom right of the QuickStyle gallery is a little button that will bring up the style list.

SNAGHTML3fa2582

Once open, you’ll see a list of all the styles available in this document. Here’s the top of the list, scrolling down will reveal a LOT of styles.

image

The names that appear in here are the ones that you’d use in the array. Just type them into the array declaration exactly as they appear in the Styles list.

4. Load up style type enumerations.

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

When we set the property to place something in the QuickStyles gallery, we have to be careful as only certain types of styles may appear in the QuickStyles area. We check this using a property of the Style object called Type, which has several enumerations. While I probably could have loaded the enumerations from the class, sometimes simple is good. Since the values for the enumerations were easily found in MSDN, I just replicated them as variables in PowerShell.

In this script we only use two of them, but for completeness I listed them all should I (or you) ever want to reuse this script as a basis for other Word work with PowerShell.

5. Create a new instance of Word.

$word = New-Object -ComObject Word.Application

The next step is pretty simple. All we do is create a new object of type Microsoft Word, and put a reference to it in the $word variable. Creating the new Word object is akin to opening up Word without a document in it.

6. Make Word visible.

$word.Visible = $true

This next step is optional. By default, when you create a new Word object it is not displayed to the user. If you are applying this to a whole batch of Word documents, I’d omit this step as it will slow down the process. For just one document though it’s not much of an impact. And to be honest, it’s pretty cool to watch Word as it removes then adds the styles.

7. Open the Word document to update.

$doc = $word.Documents.Open($wordFile)

This is probably an obvious step, but we need to load the document we want to update into our Word object. The Open method returns a new variable of type Document (MSDN). The $word variable represents Microsoft Word, the $doc variable represents the specific DOCX file (or DOC) that we want to update.

8. Reset all styles to remove them from the QuickStyles; additionally reset their priority.

foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

This next section is important to understand. The document has a collection of Style objects, stored in the Styles collection. We start by looping over each Style in the document.

Each Style object has a property named QuickStyle, which has a value of true or false. This flag sets whether a style should appear in the QuickStyles gallery (true) or not (false). If you recall from the enumerations section above, there are some styles that cannot be in the QuickStyle area. If we were to attempt to set the QuickStyle property on these, we would get an error (even if setting it to false). Hence the need to check the Type property and only attempt to set the QuickStyle property for those Styles who can appear in the QuickStyle list.

The real challenge in terms of investigation came in ordering the styles within the QuickStyle area. To order them manually, start by clicking the lower right button on the styles dialog.

image

When the Manage Styles window appears, click on the Recommend tab.

image

 

To the left of each style name, you see a number or the word “last”. This number represents the order in which it will recommend styles to you. In other words, the order in which they will appear in the QuickStyles gallery.

With the buttons at the bottom you can rearrange the order in which they appear. Note, it is possible to have multiple styles with the same value. If that happens Word will arrange all styles with the same recommended value alphabetically. The word “last” simply tells Word to put these styles at the very end of the QuickStyles gallery.

It took a lot of research, but I finally figured out the “Recommended” value is contained in a property called “Priority” within the Style object. The word last is represented by the value 100, giving a valid range of 1 to 100 for Priorities.

In the above code I am setting all styles, even the styles built into Word, to 100 which is the equivalent of ‘last’. For my situation this is fine, as I’m only going to be using this specific set of styles in this specific document so I don’t really care about the built in ones. Even if I changed my mind later, all I would need to do is add the built in styles (or any new ones I create) to my array and rerun the script.

At this point then we’ve removed all styles from the QuickStyles gallery, as well as set their priorities to ‘last’. Now it’s time to add the styles we want and order them.

9. Add our desired styles to the QuickStyle gallery and order them.

$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

With this next code, we are going to add our list of styles as well as sort them. It starts by setting the starting point for our Priority (the order) to the value 1, our starting point.

We then enter a foreach loop, where we iterate over each style in the array we assembled back in step 3. Within the loop, we set the QuickStyle property to true. Naturally I’ve only placed style names into the array that are allowed to be in the Style gallery. If you have doubts you could add a check against the Type property, as we did in Step 8, before setting the QuickStyle property.

Note that the Styles collection allows us to address a style by passing in the name of the style. When you assemble the array then, it is important to match the name that appears in Word exactly so it can find it in the array.

Next up is setting the Priority. Again, the Priority is the order in which styles are displayed in the gallery. After assigning the value I use the ++ operator to increase the value for the next iteration of the loop.

Note that since I have far less than 100 items, I don’t have any error checking for exceeding the 100 mark. If you have more than 100 styles for your QuickStyles you’ll need to modify this code. Although quite frankly if you have more than 100 styles in your QuickStyles, it’s not really that quick and you should probably rethink your editing habits. Winking smile

10. Save your work.

$doc.Save()

As the next to the last step, we’ll have the script go ahead and save the document.

11. Close Word (optional).

$doc.Close()

If you modified this script to work in a big loop so you could batch updates, or perhaps you will just want to apply the fix and go on, you could have the script go ahead and close Word. On the other hand, if after applying the updates to the style gallery you’ll immediately want to start editing, just comment out this last step. 

Summary

A final note to close this out, I’ve tested the script with Word 2016 and PowerShell 5.0. From the documentation in MSDN the same techniques also apply to Word 2013. I’ve not tried it on previous versions of Word, but in theory it should work all the way back to the version in which the QuickStyle gallery was introduced. If you’ve tried it on an older version let us know the results by posting a comment below.

Additionally, while I’ve written this on PowerShell 5.0 I’m not doing anything new or unusual, it should work as far back as PowerShell 3.0.

Below is the script, complete with comments to act as a reminder of the explanations above. Just copy and paste, modify the $wordFile and the $myStyles array, and you should be good to go.

 


The Script

<#-----------------------------------------------------------------------------
  Updating and Sorting the Microsoft Word QuickStyle Gallery, ArcaneCode style!

  Author: Robert C. Cain | @ArcaneCode | arcane@arcanetc.com
          http://arcanecode.com
 
  This script is Copyright (c) 2016 Robert C. Cain. All rights reserved.
  No warranty or guarantee is implied or expressly granted. Use at your own
  risk. 

  This script may not be reproduced in whole or in part without the express
  written consent of the author. 
-----------------------------------------------------------------------------#>

# Set the path / file name of the file you want to update
$wordFile = 'C:\Book\Chapter02.docx'

# Make a backup before we do changes. Note if the BAK exists it will be overwritten
$bakfile = $wordFile + '.bak'
Copy-Item -Path $wordFile -Destination $bakfile -Force

# Create an array of the styles you wish, in the order you want 
# them to appear in the QuickStyle area 
$myStyles = @( 'Normal [PACKT]',
               'Numbered Bullet [PACKT]',
               'Screen Text [PACKT]',
               'Code In Text [PACKT]',
               'Code Within Bullets [PACKT]',
               'Code listing [PACKT]',
               'Italics [PACKT]',
               'Figure [PACKT]',
               'Chapterref [PACKT]',
               'Bold [PACKT]',
               'Heading 1,Heading 1 [PACKT]',
               'Heading 2,Heading 2 [PACKT]',
               'Heading 3,Heading 3 [PACKT]',
               'Tip [PACKT]',
               'Layout Information [PACKT]',
               'Figure Caption [PACKT]',
               'Part Heading [PACKT]'
             )

# Load up the style type enumerations.
$wdStyleTypeParagraph = 1  # Paragraph style.
$wdStyleTypeCharacter = 2  # Body character style.
$wdStyleTypeTable = 3      # Table style.
$wdStyleTypeList = 4       # List style.

# Create a new instance of Word
$word = New-Object -ComObject Word.Application

# This is optional, if set it will display Word and let you watch the fun
$word.Visible = $true

# Open the document you wish to reset the styles for
$doc = $word.Documents.Open($wordFile)

# First, reset all styles to not be Quick Styles, 
# and set the priorty to 100 (which will be 'last' in Word)
foreach ($sty in $doc.Styles)
{
  # Only these two types can be QuickStyles
  if ( ($sty.Type -eq $wdStyleTypeCharacter) -or ($sty.Type -eq $wdStyleTypeParagraph) ) 
    { $sty.QuickStyle = $false }
  
  $sty.Priority = 100
}

# Now set the styles like we want 'em!
$priority = 1
foreach ($mySty in $myStyles)
{ 
  # Setting to true will make the style appear in the QuickStyle gallery
  $doc.Styles($mySty).QuickStyle = $true
  # The priority is an integer which determines the sort order within the QS gallery
  $doc.Styles($mySty).Priority = $priority++
}

# Save the document. 
$doc.Save()

# Close up word (Optional, if you want to start editing 
# right away you can comment this out)
$doc.Close()

I have to give a shout out to the folks at Sapien for their PowerShell Studio tool. It has a great feature, Copy HTML, which takes pieces of PowerShell code, copies it to the clipboard and in the process adds the appropriate HTML tags to do the nice coloring.

Simple Way to Fix SQL Server Page Level Corruption

I’ve decided to try an experiment, and allow the occasional guest blogger. This will give new bloggers additional exposure, and draw eyes to both our sites.

Our first guest blogger is Andrew Jackson. Andrew is a SQL DBA and SQL Server blogger too. He likes to share about SQL Server and the problems related to it as well as their solution, handling database related user queries, server or database maintenance, database management etc.

You can find Andrew online at:

Facebook: https://www.facebook.com/people/Andrew-Jackson/100008825676608

Linkedin: https://www.linkedin.com/in/jackson-andrew-401147a5

Twitter: https://twitter.com/jacksonandrew32

Editor & Blog Contributor at: http://www.sqlmvp.org/

Without further delay, here is Andrew’s first contribution, Simple Way to Fix SQL Server Page Level Corruption.


 

Database corruption is the worst situation for any Database Administrator. The SQL database stores very crucial data, the occurrence of corruption make it inaccessible which causes huddle in workflow in any organization or business. Whenever the corruption issues are discovered, it must be resolved on time by the admin before the issue spread through the data infrastructure. Therefore, it is very necessary to take proper steps to protect the data from such damage. This article explains the causes, troubleshooting, and fixing SQL server page level corruption.

Page Level Corruption

Pages are the most basic unit of data stored in database, all .mdf or .ndf files in a database are logically divided into pages numbered from 0 to n. Whenever the pages of database file are infected by the corruption, DBA will repair each damaged page in isolation. Repairing and restoring the few pages individually is faster than repairing the entire file.

Causes:

· Hardware malfunction, most of database corruption occurs due to hardware failure.

· Issues in SQL server itself, sometimes when there are internal issues in SQL server results corruption

· Unplanned shut-down when database is opened

· Virus intrusion

· Updating the SQL server in newer version also results corruption

How to Fix Page Level Corruption

Moreover, Admin have their eventual plans to tackle SQL Server database corruption issue, there are strategies like replication, database monitoring, disaster recovery plans etc. However, there are the conditions when admin do not have such solution. In that case, admin need a solution to handle this problem, In SQL 2005 and later version there is a feature called Page Checksum that creates checksum value to detect the scale of the damage in database.

If the problem in SQL database is unreadable by SQL server, then it requires the complete restoration of the database. On other hand if damage is not that severe and lesser pages are infected then DBA can use DBCC utility.

DBCC CHECKDB Command for Handling Page Level Corruption

DBCC CHECKDB is a command that fix the corruption issue in SQL database. It fixes the inconsistency in SQL server database by performing Database Consistency Checks.

It is a combination of DBCC CHECKCATALOG, CHECKALLOC and DBCCCHECKTABLE.

The syntax of DBCC CHECKDB command is given below:

clip_image003

Other Possible Solutions

There is also an alternative solution to handle Page level corruption in SQL database, user can opt a third party tool namely that can easily tackle this corruption. It can recover highly corrupted page from both primary and secondary database. It supports all the versions of SQL Database Server. Using these defined ways; user or DBA can easily recover the page level corruption.

 


Thanks Andrew for your contribution. If you are interested in becoming a guest blogger, just email me info <@> arcanetc.com.

SQL Saturday 498 Chattanooga

Do you like PowerShell? SQL Server? Are you anywhere close to Chattanooga TN? Then don’t miss this SQL Saturday, June 25th 2016.

I’m giving two sessions (yes two for the price of one!). The first session will be PowerShell 201. It covers advanced concepts you need to know, such as debugging, remoting, security, and code signing.

The second session will educate on using PowerShell with SQL Server. We’ll cover the use of the SQL Provider (SQLPS) as well as the more advanced SQL Management Object library (SMO).

You’ll find the full schedule here, as well as links to register, directions, and the like.

http://www.sqlsaturday.com/498/Sessions/Schedule.aspx 

Best of all, my demos are already uploaded, so you can download early and play along during the presentation!

I’ll mention Chattanooga is a great vacation town, so bring your whole family. There’s a huge aquarium, discovery museum, Lookout Mountain, and tons of attractions to keep them occupied while you’re having fun at SQL Saturday.