Intro to the Internet of Things using Netduino

This was my presentation to the .Net Users of Fort Wayne (NUFW) on 3/8/2016.

Intro to the Internet of Things using Netduino from Dean Willson

Index Reorganization and Rebuilding for Success

On January 20, I presented an end-to-end process for SQL Server Index rebuilding/reorganization to NUFW (.Net Users of Fort Wayne). For people that manage small databases with less than a few million records per table, there are few risks and considerations. For those with large or VLDB databases such as ERP/MRP databases with hundreds of millions of records and Terabyte data file sizes, the process becomes more critical and time consuming. That is the situation for which this process was designed.

Index Reorganization and Rebuilding for Success from Dean Willson

Powershell IDE’s

There are a number of free Powershell Development environments out there. So far, my favorite is Dell’s (formerly Quest Software’s) PowerGui. My current IDE of choice is Idera’s Powershell Plus. There are a few things I still use Microsoft’s native Powershell ISE for, but it doesn’t have all the features I am accustomed to from the other IDE’s. A few of my observations are summarized below, but since they are all free tools, I am appreciative of the value they provide.

Windows Powershell ISE

I like that it has the screen wipe button to clear the console, but since I work more in PowerGUI and Powershell Plus, I am now accustomed to putting a CLS command at the top of my scripts to clear the screen between executions.

There is an x86 version and x64 version which makes working with older 32bit dll’s like the SAP Business Objects 3.1 .Net API’s easy.

In the versions I used there didn’t appear to be much if at all in terms of Intellisense (code completion options). Because I switch between editors for VB.Net, C#, Powershell, and SQL Server, Intellisense is a desirable feature to improve productivity.

Powershell Plus

It took me a while to find all the options to set up the environment the way I like it (more like the default PowerGUI interface). It has more bells and whistles than PowerGUI, but most of the time I don’t really need all that. Mainly I just want the code window, console output window, and the variables list.

I do like the debugging functionality which automatically steps one line at a time through the code pausing briefly so you can see what line it is currently executing.

I don’t like how the comment/uncomment works. It uses a paragraph style comment block vs. commenting individual lines which means I can’t selectively uncomment only a few lines in the bulk paragraph block. Also I can’t uncomment the whole block unless I select exactly the correct selection.

There are more options for Executing the scripts, but that seems to take more clicks to Execute in the manner I want to use while testing.

I am still relatively new to Powershell Plus so my productivity will improve as I become more familiar with its IDE design.

PowerGUI

This is my favorite, but my issue with it is it doesn’t appear they are actively developing it since Dell acquired it from Quest Software. It hasn’t had an update in a few months and the version I have installed won’t uninstall and I can’t install the newest release without error. The Intellisense is pretty good.

I like how the comment/uncomment functionality works. It works like Visual Studio and SQL Server where it will comment or uncomment exactly which lines are highlighted, even if only a part of the line is highlighted.

When typing a file path, the GUI makes it easy to click on the path and finish completing the path by navigating the folder structure with a Windows Explorer Browse like functionality.

I like the options for Execute All and Execute selection. There are buttons for them right on the top menu as well as the right click menu.

Links

Dell PowerGUI

Idera Powershell Plus

Windows Powershell ISE

SQL Server Daily Health Check page and Powershell script update

I recently created a new page for my SQL Server Daily Health Check Powershell script download and documentation. I updated the script to version v0.8.2 which refactored the original script into a function to eliminate repeating code and make it easier to customize with additional checks. I will add additional documentation and instructions as I get time.

Stop Main Page from Scrolling on iPad within Scrolling ModalPopup

Using ASP.Net ModalPopupExtenders can improve the user experience, but sometimes introduce some annoying side effects on a Mobile device. On a Mobile device like an iPad, if you have a ModalPopup that has more content than fits the screen, it can require having a scrollbar within the popup. If the main page is also too long for the screen, the main page can have a scroll bar also. The user experience degrades when on an iPad, the user is scrolling down the popup content and they reach the bottom of the popup, continued swiping up to scroll down will then start scrolling the main page down. If the user wants to scroll back up within the popup, they must repeatedly swipe down to first scroll the main page up until the top of the main page is reached at which point the popup content will start scrolling up.

There are a number of search results that “remove the scrollbar” from the main page while the modal popup is open by setting the html and body CSS tags to overflow:hidden, but many mobile devices don’t respect overflow:hidden alone. I found it was also necessary to set position:fixed also. When the popup is closed reset the position to static or inherit and set overflow to auto. I used jQuery to change the CSS tags to hide and show the main page scroll bars.

In my example, I had a div tag with id=”wrapper” in the Master Page surrounding the main page content. I had a ModalPopup window in the WebForm containing a Label (blank text) with a CssClass=”promoVisible” and an OK button with the CssClass=”popupOk”. On the WebForm with the Modal Popup, I used the following jQuery that does all the magic.

$(document).ready(function () {

//had to set position:fixed to work on iPad and other mobile    

   $('.popupOk').click( function(){

      $('#wrapper').css('overflow', 'auto');

      $('#wrapper').css('position', 'inherit');

      //  alert("ok clicked");

   });

   // if the popup is visible, fix the overflow so the

   // background doesn't scroll, only the popup window

   if($('.promoVisible').is(':visible')){

      $('#wrapper').css('overflow', 'hidden');

      $('#wrapper').css('position', 'fixed');

   } else{

      $('#wrapper').css('overflow', 'auto');

      $('#wrapper').css('position', 'inherit');

   }

)};

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.