Automating SQL Server Daily Health Checks with PowerShell

Last night I presented this PowerPoint presentation and Powershell script Demo to fwPASS members.

Automating sql server daily health checks from Dean Willson

Publish Documents to SAP Infoview using Powershell

SAP offers reasonable guidance for creating and publishing the various reports (Webi, pdf, Excel, Crystal Reports) using custom web or windows applications that consume their SAP .Net Development SDK.

Using those tools one can create user friendly applications for managing reports in Infoview, but in some cases you may need an easily customizable process for automating some of those tasks. System administrators everywhere are finding Powershell to be a ideal tool administration and automation.

Prerequisites/Assumptions

• SAP Business Objects 3.1 (BOXI 3.1)
• User account running Powershell has appropriate permissions to access the fileshare where the source pdf report is located
• Have account credentials with permissions to read/write files on the Bobj API server
• 32bit Powershell x86 engine
• Server or PC running the Powershell script has the SAP Business Objects .Net SDK installed

Powershell script

# -------------------------------------------------
# Must use x86 powershell client for BOXI 3.1 dll's
# -------------------------------------------------
[reflection.assembly]::LoadWithPartialName("CrystalDecisions.Enterprise")
[reflection.assembly]::LoadWithPartialName("CrystalDecisions.Enterprise.InfoStore")
[reflection.assembly]::LoadWithPartialName("CrystalDecisions.Enterprise.Desktop.Pdf")
[reflection.assembly]::LoadWithPartialName("CrystalDecisions.Enterprise.Framework")

#[CrystalDecisions.Enterprise.SessionMgr] | Get-member 

$username = "<cmsUsername>"
$password = "<cmsPassword>"
$cmsname = "<cms-servername>:6400"
$cmsAuthType = "secEnterprise"
$BOServerName = "<Bobj-servername>"

[CrystalDecisions.Enterprise.SessionMgr]$boSessionMgr = New-Object CrystalDecisions.Enterprise.SessionMgr
[CrystalDecisions.Enterprise.EnterpriseSession]$boEnterpriseSession = $boSessionMgr.Logon($username, $password, $cmsname, $cmsAuthType)
[CrystalDecisions.Enterprise.EnterpriseService]$boEnterpriseService = $boEnterpriseSession.GetService("", "Infostore")

$boInfoStore = New-object CrystalDecisions.Enterprise.InfoStore($boEnterpriseService)
$myInfoObjects = $boInfoStore.NewInfoObjectCollection()
$myPluginManager = $boInfoStore.PluginManager
$myPluginInfo = $myPluginManager.GetPluginInfo("CrystalEnterprise.PDF")

$myInfoObject = $myInfoObject = $myInfoObjects.Add($myPluginInfo)
$myPDF = [CrystalDecisions.Enterprise.Desktop.Pdf]$myInfoObject             
$myPDF.Title = "DeanPowershelling"
$myPDF.Properties.Add("SI_PARENTID", "4701")
$myPDF.Description = "Dean is testing uploading to Infoview with Powershell"
$myPDF.Files.Add("C:\Development\sapmnt\Reporting\Report-to-be-published.PDF")

$boInfoStore.Commit($myInfoObjects)

# clean up
$boEnterpriseSession.Logoff()
$boEnterpriseSession.Dispose()
$boSessionMgr.Dispose()
$boInfoStore.Dispose()

The example above is written for Pdf files, but the syntax for the other document types is very similar. Since this is Powershell, it would be a relatively simple task to convert the above to a Powershell Function that could be used by piping the results of some automation to the function to upload a collection of reports at a time, vs. the one-off approach above.

Troubleshooting

I initially assumed that because I had BusinessObjects installed on my development environment where I was testing, that I had the appropriate dll’s in the GAC to instantiate the BO objects in Powershell. That lead me down a rabbit hole of troubleshooting where I had difficulty determining if the issues I was having were related to the syntax I was trying to use in Powershell or the lack of the necessary BO assemblies.

I experimented with a combination of copying the dll’s from a working .Net web application to a local folder and using the Powershell Add-Type –path syntax to load the assemblies. I used Telerik JustDecompile to browse the SAP .Net SDK assemblies to make sure I wasn’t missing assemblies.

I downloaded and installed the SAP BusinessObjects .Net Development SDK’s. Some time during the troubleshooting I was successfully using the Powershell Add-Type –AssemblyName syntax, but I must have had the assemblies loaded from previous debugging attempts because after a restart of the development box, the script using Add-Type –AssemblyName to load the assemblies was failing to recognize the CrystalDecisions.Enterprise.SessionMgr which is actually a part of CrystalDecision.Enterprise.Framework which I was not loading in the PS1 script prior to the restart. The [reflection.assembly]:LoadWithPartialName was working with the above referenced assemblies even after a restart.

In order to step though and make sure my assemblies were referenced and loaded correctly, I used the above commented out syntax of BO object piped to the Get-Member commandlet to list the methods and properties on the BO objects:

[CrystalDecisions.Enterprise.SessionMgr] | Get-member 

If the methods were listed without error, I knew I had the assembly references correct and the assemblies were loaded.

This is important enough to repeat, make sure if you are using the BOXI 3.1 SDK assemblies, you use an x86 32-bit Powershell engine. 

Disclaimer: Make sure all interface with your SAP platform services is in compliance with your applicable SAP license agreements.

Visual Studio Productivity Tools

This is the shell of a presentation I created to highlight some Visual Studio extensions that I have found helpful for improving my coding productivity in Visual Studio 2012.

The extensions can be downloaded from NUGET using Tools – Extensions and Updates – Online – Visual Studio Gallery:

• Productivity Power Tools 2012 by Microsoft
• Web Essentials 2012 by Mads Kristensen

Visual Studio 2012 Productivity Tools from Dean Willson

Introduction to Powershell presentation

Powershell is a powerful scripting shell to do any number of administrative tasks. Below is the introductory level presentation I gave to fwPASS, the Fort Wayne chapter of the Professional Association for SQL Server on 10/23/2012.

Intro to Powershell from Dean Willson

Hyper-V Manager cannot connect–make sure RPC service is running

My Hyper-V Server 2008R2 lab environment is so stable I rarely fire up the Hyper-V MMC snap-in. The last time I opened it was four months ago when I switched from booting it from a USB stick to booting it from an IDE drive. I opened it today and it gave me the typical “Cannot connect to the RPC service on computer 'Servername'. Make sure your RPC service is running.” error. My quick troubleshooting steps follow:

1. I double checked to make sure the IP address and host name were still specified in the \etc\hosts file. It was.
2. I attempted to remote into the Hyper-V server directly using Remote Desktop and was met with the “Your password has expired” message. I reset the password, and voila – the Hyper-V manager MMC connected to my server and displayed the current state of my VM’s.

Blank Page when Publishing a Silverlight App

I just deployed a new Silverlight application to a Windows Server 2003 box as a new website and was surprised with a blank page when I launched the application. No error messages or warnings, just a completely blank page.

A quick web search and I realized I had been here before. You have to register the MIME types, .xaml and .xap, in IIS for the website. I added them for a previous website, but I didn’t add them at the server root.

Simple instructions to add the MIME types to IIS can be found on Jacqui’s Dev Blog under Deploying Silverlight Application - Why Blank?! Note the first blog post comment that suggests adding the additional .xap application/x-silverlight-app and .xbap application/x-ms-xbap MIME types. I didn’t need to add the .xbap to get my application working, but I did need the .xap.

Visual Studio Project – unavailable

I hadn’t opened a particular Visual Studio solution since getting a replacement workstation and rebuilding my developer environment. Upon opening, one of the projects in the solution would not open and showed up as “(unavailable)” as shown below.

The Output window displayed the not very useful message, “The project type is not supported by this installation.”

In order to find out why, I opened the LPM.csproj file using Wordpad, not in Visual Studio, and scanned through the properties looking for insight as to why the project was unsupported. It didn’t take long to find the issue.

This project was using MVC 3 and the Entity Framework 4.1 which were not installed on my new workstation. I installed EF 4.1 on my old workstation specifically so I could use the MvcScaffolding NUGET package with an existing database design for a product technology upgrade. See my previous posts detailing some of my experiences with MvcScaffolding and the ASP.NET MVC framework.

After installing both ASP.NET MVC 3 and EF 4.1, all the projects in the solution opened as expected. Another small victory!

 

photo credit: ruurmo / CC BY-SA 2.0