ScriptPosse

A Question was posed on Stackoverflow.  How do you create a folder structure from a created DacPac or BacPac.  This article is how I went about doing this with PowerShell.

The first means was to try and find someone who’d tried this before. The best I could find searching was this article: Deploy DACPACs with PowerShell.  This Script does an excellent job of showing how you can use the SQL 2014 DLL’s to create a Script that can than be deployed to your database.   This did not answer the question though.

Thankfully one of the participants in the question was kind enough to show how to do this very thing in C# so I took their pseudo code and turned it into PowerShell Code.

using (TSqlModel modelFromDacpac = new TSqlModel(dacpacPath))
{
 IEnumerable<TSqlObject> allObjects = model.GetObjects(QueryScopes);
 foreach (TSqlObject tsqlObject allObjects)
 {
 string script;
 if (tsqlObject.TryGetScript(out script))
 {
 // Some objects such as the DatabaseOptions can't be scripted out.

// Write to disk by object type
 string objectTypeName = tsqlObject.ObjectType.Name;
 // pseudo-code as I didn't bother writing.
 // basically just create the folder and write a file
 this.MkdirIfNotExists(objectTypeName);
 this.WriteToFile(objectTypeName, tsqlObject.Name + '.sql', script;
 }
 }
}

Starting at the top of the script I need to translate the using Statement into a New-object in PowerShell. In order to do that I needed to find what Dot net class TSQLModel was in.  Based on that research I found that I needed to add the type to my session(Microsoft.SqlServer.Dac.Extensions.dll).  Once the type was added I was then able to get the model from my DacPac.

<br>

add-type -path 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.Extensions.dll'

$model =[Microsoft.SqlServer.Dac.Model.TSqlModel]::new(((get-item ".\$dacpac").fullname))

Now that I have the Model of my dacpac I need to figure out how to make this into a PowerShell piece of code IEnumerable<TSqlObject> allObjects = model.GetObjects(QueryScopes);

I know my return type is IEnumerable Tsql object.. Now the question is how do I query my model and get that return object.  Based on the C# code I need to call GetObjects. GetObjects expects you to pass an object type (QueryScopes variable) and optionally you  can pass the Object Identifier ID or the Object type.   The queryScopes is an enumeration that has the following values(All, Builtin, Default, None, SameDatabase, System, UserDefined). I chose the All so I could see what this method would return.

$returnObjects = $model.GetObjects([Microsoft.SqlServer.Dac.Model.DacQueryScopes]::All)

Now the next step is to step through the return result and test each item to see if it can be scripted.  When calling the TryGetScript it has an output so we must declare a variable before we call this method.

<br>

$s = ''
foreach($r in $returnObjects)
{
 if ($r.TryGetScript([ref]$s))
 {
 $objectTypeName = $r.ObjectType.Name;
 $d="c:\temp\db\$objectTypeName"
 if(!(test-path $d ))
 {
 new-item $d -ItemType Directory
 }
 $filename = "$d\$($r.Name.Parts).sql"

 if(! (test-path $filename))
 {new-item $filename -ItemType File}
 $s | out-file $filename -Force
 write-output $filename
 }

}

I found when I ran this that it would error on creation of a directory.

dactest.ps1 (28, 10): ERROR: At Line: 28 char: 10
ERROR: + $s | out-file $filename -Force
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : OpenError: (:) [Out-File], NotSupportedException
ERROR: + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand
ERROR:

In order to find this exception I decided to put a Try Catch around the act of creating the file:

Try
 {
 new-item $filename -ItemType File
 }
 Catch
 {
 "Filename error $filename"
 }

What I found after putting the Try catch in place was that the exception was be cause the object names were a Url:

Filename error c:\temp\db\Service\http://schemas.microsoft.com/SQL/Notifications/EventNotificationService.sql

In order to fix this I implemented a test to see if the item name was a url

[system.uri]::IsWellFormedUriString(‘http://schemas.microsoft.com/SQL/Notifications/EventNotificationService.sql’, [system.uri]::IsWellFormedUriString)

Now that I know that the filename is a uri I can parse and get the last item in the uri for the filename:

$url = "$($r.Name.Parts)"
 if ([system.uri]::IsWellFormedUriString($url, [system.urikind]::Absolute))
 {
 $u = ([uri]"$url").Segments[-1]
 $filename = "$d\$u.sql"
 new-item $filename -ItemType File -ErrorAction Stop -Force
 }

Example output

Directory: C:\temp\db

Mode LastWriteTime Length Name
—- ————- —— —-
d—– 3/5/2018 7:10 PM Assembly
d—– 3/6/2018 9:04 AM Contract
d—– 3/5/2018 7:18 PM DataType
d—– 3/5/2018 7:19 PM Endpoint
d—– 3/5/2018 7:19 PM Filegroup
d—– 3/6/2018 9:04 AM MessageType
d—– 3/5/2018 7:20 PM Queue
d—– 3/5/2018 7:20 PM Role
d—– 3/5/2018 7:20 PM Schema
d—– 3/6/2018 9:04 AM Service
d—– 3/5/2018 7:20 PM Table
d—– 3/5/2018 7:20 PM User
d—– 3/5/2018 7:20 PM UserDefinedType
-a—- 3/5/2018 7:06 PM 0 [Microsoft.SqlServer.Types].sql

Directory: C:\temp\db\Schema

Mode LastWriteTime Length Name
—- ————- —— —-
-a—- 3/6/2018 9:11 AM 54 dbo.sql
-a—- 3/6/2018 9:11 AM 76 db_accessadmin.sql
-a—- 3/6/2018 9:11 AM 82 db_backupoperator.sql
-a—- 3/6/2018 9:11 AM 74 db_datareader.sql
-a—- 3/6/2018 9:11 AM 74 db_datawriter.sql
-a—- 3/6/2018 9:11 AM 70 db_ddladmin.sql
-a—- 3/6/2018 9:11 AM 82 db_denydatareader.sql
-a—- 3/6/2018 9:11 AM 82 db_denydatawriter.sql
-a—- 3/6/2018 9:11 AM 64 db_owner.sql
-a—- 3/6/2018 9:11 AM 80 db_securityadmin.sql
-a—- 3/6/2018 9:11 AM 58 guest.sql
-a—- 3/6/2018 9:11 AM 84 INFORMATION_SCHEMA.sql
-a—- 3/6/2018 9:11 AM 54 sys.sql

The entire script is posted in a gist:

Last night we had some technical difficulties with our user group and getting the broadcast and the speaker setup and going.    So we had to make up something good to talk about in the user group.

So what we ended up doing was what I like to call a round table.  In this discussion everyone relayed some of their successes with PowerShell.   This article is just a “glimpse’ of some of the tidbits I was able to capture during the meeting.

If you are anything like me you like things that you can do to help you remember a command you last typed or you like to bring back a command from history and modify it slightly and try again.   One of the users last night showed us this wonder full trick in powershell the #(tab).

The best way to show this feature is to tell you to use the Get-History cmdlet in PowerShell to show your last few Items you’ve typed.

PSGit:\> Get-History

Id CommandLine
-- -----------
1 $env:COMPUTERNAME
2 h
3 $env:PSModulePath
4 $env:PSModulePath -split ';'
5 h
6 ($env:PSModulePath -split ';')[0]
7 ($env:PSModulePath -split ';')[1]
8 ($env:PSModulePath -split ';')[2]
9 h
10 get-process
11 h
12 get-service

I only show the history so there is context around what this little gem of a tip does. if I type #PS and then hit tab I’ll get each item from history that contains PS.  Demonstrated below:

Now for another tip that I found useful as well.  Have you ever wanted  to create a variable and have the output of the variable on the screen as well.  This can be done with Tee-Object but there is a much shorter method that one of the users in the AZ Powershell User group demonstrated:

PSGit:\> ($var = ($env:PSModulePath -split ';')[0])
C:\Users\crshn\Documents\WindowsPowerShell\Modules

PSGit:\> $var
C:\Users\crshn\Documents\WindowsPowerShell\Modules

Simply enclose your command in parens and you get the output in your variable and to your screen.

We had a great discussion about this post Merging hashtables. This spurned a discussion on a very cool means to copy your object intact to another object.  The participant in the user group informed me that he’d share his code with me.  When I receive it I’ll add to this Post.

Lastly another user demonstrated how they use data from the perfmon reliability counters that every windows machine has.   you can view those reliability counters through a simple command at your prompt:

perfmon /rel

Turns out these counters are part of WMI and you could drill into these to help you with diagnosing problems in your infrastructure through Powershell and WMI.

Here is a post from Richard Siddaway on how he used some of the items in the class that this provides:

https://richardspowershellblog.wordpress.com/2015/09/29/win32_reliabilityrecords-class

It was a very fun Users group. If you are online or in Phoenix area Drop by and we’ll entertain you with a speaker or any of the great folks that attend.

Until then

Keep Scripting

Thom

If you’ve  ever had to troubleshoot issues with IIS you’ll known that you are often drawn to looking at IIS Logs.    This post is about a script that I modified to search IIS logs for specific errors.

To begin with I found this article on StackOverFlow that got me started on this path.

To begin with we need to get the webadministration module so we can get the name of the website we want to get the log file for:

Import-Module WebAdministration

$site = Get-Item IIS:\Sites\$website
$id = $site.id
$logdir = "$($site.logfile.directory)\w3svc$id"

Now that we have the  The $logdir (log directory) we can now put the rest of the file name together by getting the date:

$File = "$logdir\u_ex$(((get-date).adddays(-$days)).ToString("yyMMdd")).log"
s

Assuming you are using a logfile per day the name of the log file is:

u_ex(yyMMdd)  which should be something similar to this: u_ex170824.log

Now that we have our log file we need to strip off unnecessary lines.  Specifically the First three lines that start with #S, #D, or #V which ends up being the Sofware, Version and Date items at the top of the log.

Then we’ll need to Build the columns based on the #fields value in the Log. That way each field in the results can be arranged into columns so we can sort our data based on what the item in the field is.

The additional column we’ll add is the name of the log file.

$Log = Get-Content $File | where {$_ -notLike "#[D,S-V]*" }

$Columns = (($Log[0].TrimEnd()) -replace "#Fields: ", "" -replace "-","" -replace "\(","" -replace "\)","").Split(" ")

$Columns += 'LogFile'

$Count = $Columns.Length

Now that we have the column titles now it’s time to filter out the log and only get the lines that have the value we want.  In my case I was searching for the Error code of 500.

$Rows = $Log | where {$_ -like "*$errorType 0 0*"}

Now that we have all the data we want in the row’s variable we can now construct a table of Columns and rows.

$IISLog = New-Object System.Data.DataTable "IISLog"

foreach ($Column in $Columns) {
$NewColumn = New-Object System.Data.DataColumn $Column, ([string])
$IISLog.Columns.Add($NewColumn)
}
# Loop Through each Row and add the Rows.
foreach ($Row in $Rows) {
$Row = $Row.Split(" ")
$AddRow = $IISLog.newrow()
for($i=0;$i -lt $Count; $i++) {
$ColumnName = $Columns[$i]
if($ColumnName -eq 'LogFile')
{$AddRow.$ColumnName = $file }
else {$AddRow.$ColumnName = $Row[$i]}
}
$IISLog.Rows.Add($AddRow)
}

$IISLog

Full function is below:

  function get-ErrorLogs
  {
  param($website = 'myWebSite', $errorType = '500',[int] $days =0)

    Import-Module WebAdministration

    $site = Get-Item IIS:\Sites\$website
    $id = $site.id
    $logdir = "$($site.logfile.directory)\w3svc$id"

    $File = "$logdir\u_ex$(((get-date).adddays(-$days)).ToString("yyMMdd")).log"
    $Log = Get-Content $File | where {$_ -notLike "#[D,S-V]*" }
    $Columns = (($Log[0].TrimEnd()) -replace "#Fields: ", "" -replace "-","" -replace "\(","" -replace "\)","").Split(" ")
    $Columns += 'LogFile'
    $Count = $Columns.Length
    $Rows = $Log | where {$_ -like "*$errorType 0 0*"}
    $IISLog = New-Object System.Data.DataTable "IISLog"
    foreach ($Column in $Columns) {
      $NewColumn = New-Object System.Data.DataColumn $Column, ([string])
      $IISLog.Columns.Add($NewColumn)
    }
    foreach ($Row in $Rows) {
      $Row = $Row.Split(" ")
      $AddRow = $IISLog.newrow()
      for($i=0;$i -lt $Count; $i++) {
        $ColumnName = $Columns[$i]
        if($ColumnName -eq 'LogFile')
        {$AddRow.$ColumnName = $file }
        else {$AddRow.$ColumnName = $Row[$i]}
      }
      $IISLog.Rows.Add($AddRow)
      }

    $IISLog
  }

Now that I have this in a full function I can just call it like this:

get-ErrorLogs -website “Website2” -errorType 500 -days 4

And get results similar to this:

I hope this helps someone
Until then keep Scripting
Thom

Recently I’ve been working on some code for Querying schedules for SSRS.  I discovered the way that PowerShell passes a null to another function isn’t what the SSRS method expected.

So this started me on what the Heck is PowerShell null really set to?

Based on this blog article we can see I’m not the only one that has this question.   Cody Konior uncovered other ways to declare null:

If you test each of these against PowerShell’s null you get a false:

If we use some of the other comparisons maybe we’ll get to what $null is really set to:

PS ps:\> $b = $null
PS ps:\> [string]::IsNullOrWhiteSpace($b)
True
PS ps:\> [string]::IsNullOrWhiteSpace($b)
True
PS ps:\>

These evaluate the value you’d expect all $true.   I for sure don’t know the language as well as Kirk Munro (@Poshoholic).  He pointed me to a class that I used to compare to  PowerShell’s Null and it came up true:

[System.Management.Automation.Internal.AutomationNull]::Value

In a Blog post about a issue around null it’s explained this way by Jason Shirk (@lzybkr):

Now I can test for PowerShell’s null, and this explains why $null is not Equal to the C# equivalent.

$Null -eq [System.Management.Automation.Internal.AutomationNull]::Value

True

Yet more detail on the why $Null is different in PowerShell (a more detailed example).

Moral of the story if you are calling a method that expects a $null make certain you get the Right $null for the Method you are calling.