What is SpinTax?

SpinTax is a way of “spinning” text. It’s often used by somewhat spammy marketing programs to post different words, phrase, or articles to different web or social media sites. It is designed to create somewhat readable but yet random text, based on changing synonyms for various words. There are actually programs that will take articles and turn them into SpinTax. They try to substitute appropriate synonyms for nouns, verbs, and ajectives. Then when the text is re-run, your article should be fairly unique. However, sometimes, the spun articles turn out to be unreadable. You wouldn’t use this on your “money site”, but they are often used for Tier 1, 2, or 3 support sites, where you make SEO backlinks to your main site, or other tiers.

Here’s a quick example from the sample code below:

{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {Smith|Williams|Davis}!

This means to pick one of the three “greeting” words, followed always by the words/phrase “to you, “, then followed by one of the three salutations (Mr., Mrs, or Ms.) then followed by one of three last names. The program runs below runs the SpinTax in a loop 20 times, so you can see what it generates. It should be random, but occasionally a phrase will repeat. The above is a simple example; SpinTax can also be nested. When you get code from the internet, make sure it handles “nested Spintax.” You might have to run your own test to make sure.

Many programmers use RegEx to accomplish the same, for example here is a PHP code sample: PHP RegEx Spinner. I didn’t quite understand all the RegEx, especially the ?R. That code actually does a call back. I attempted in PowerShell, but had some issues. Perhaps that will be a topic of a future blog article.

I found the fast C# code below in this StackOverflow post.

But I wanted to be able to call it from PowerShell. I could have have compiled it in Visual Studio and made a .DLL, but for fun, I decided to include it “inline” in the PowerShell program. We do that by using the “Add-Type -TypeDefinition” cmdlet, which basically assembles the C# code, which was stored in a variable. The downside of this technique is that if you change the C# code, you must close and re-open PowerShell or ISE. Running Add-Type a second time will fail, not replace your old code. So I have code below that checks to see if the type already exists, before executing the Add-Type.

SampleCode

$csCode = @"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Spintax
{
    public class Program
    {
        public static Random rand = new Random();
        /*
        static void Main(string[] args)
        {
            string strSpinTax = "{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {{Jason|Malina|Sara}|Williams|Davis}";
            Console.WriteLine("strSpinTax=" + strSpinTax);

            for (int j=0; j < 20; j++)
                {
                   string strResult = SpinEvenMoreFaster(strSpinTax);
                   Console.WriteLine("result=" + strResult);
                }
            Console.ReadLine();
        }
        */


        static int[] partIndices = new int[100];
        static int[] depth = new int[100];
        static char[] symbolsOfTextProcessed = new char[100000];

        public static String SpinIt(String text)
        {
            int cur = SpinEvenMoreFasterInner(text, 0, text.Length, 0);
            return new String(symbolsOfTextProcessed, 0, cur);
        }

        public static int SpinEvenMoreFasterInner(String text, int start, int end, int symbolIndex)
        {
            int last = start;
            for (int i = start; i < end; i++)
            {
                if (text[i] == '{')
                {
                    int k = 1;
                    int j = i + 1;
                    int index = 0;
                    partIndices[0] = i;
                    depth[0] = 1;
                    for (; j < end && k > 0; j++)
                    {
                        if (text[j] == '{')
                            k++;
                        else if (text[j] == '}')
                            k--;
                        else if (text[j] == '|')
                        {
                            if (k == 1)
                            {
                                partIndices[++index] = j;
                                depth[index] = 1;
                            }
                            else
                                depth[index] = k;
                        }
                    }
                    if (k == 0)
                    {
                        partIndices[++index] = j - 1;
                        int part = rand.Next(index);
                        text.CopyTo(last, symbolsOfTextProcessed, symbolIndex, i - last);
                        symbolIndex += i - last;
                        if (depth[part] == 1)
                        {
                            text.CopyTo(partIndices[part] + 1, 
                                         symbolsOfTextProcessed, 
                                         symbolIndex, 
                                         partIndices[part + 1] - partIndices[part] - 1);
                            symbolIndex += partIndices[part + 1] - partIndices[part] - 1;
                        }
                        else
                        {
                            symbolIndex = SpinEvenMoreFasterInner(text, partIndices[part] + 1, 
                                          partIndices[part + 1], symbolIndex);
                        }
                        i = j - 1;
                        last = j;
                    }
                }
            }
            text.CopyTo(last, symbolsOfTextProcessed, symbolIndex, end - last);
            return symbolIndex + end - last;
        }


    }
}
"@

cls
if (-not ([System.Management.Automation.PSTypeName]'Program').Type)
{
    #include the C# code that exists in variable $csCode
    #you will get error if this already exists 
    Add-Type -TypeDefinition $csCode
}

#non-nested
#$spintax = '{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {Smith|Williams|Davis}!';

#nested:
$spintax = '{Hello|Howdy|Hola} to you, {Mr.|Mrs.|Ms.} {{Jason|Malina|Sara}|Williams|Davis}'
$spintax = '{{you|one|they|he|she} {will|may} need to|{one|you} {must|will need to}}'


Write-Host "spinTax: $spinTax"

#This code does seem to handle nested SpinTax.

for ($i=0; $i -le 20; $i++)  #loop 20 times 
    {
       $spinResult = [Spintax.Program]::SpinIt($spintax)
       Write-Host "$($i): result=$spinResult"
    }

Results:

spinTax: {{you|one|they|he|she} {will|may} need to|{one|you} {must|will need to}}
0: result=he will need to
1: result=she will need to
2: result=you will need to
3: result=you will need to
4: result=you must
5: result=they will need to
6: result=he may need to
7: result=you must
8: result=one will need to
9: result=you will need to
10: result=he may need to
11: result=they may need to
12: result=one will need to
13: result=they may need to
14: result=you must
15: result=one must
16: result=she will need to
17: result=one will need to
18: result=one will need to
19: result=you must
20: result=one must

It’s a fairly common practice to change the Deploy Server from a specific machine name to “.” or “(local)”. I’m at a new client, where the security isn’t all set up properly. So when I changed the server name to “(local”) I got the error:

No BizTalk Configuration database(s) found on server ‘(local)’


No_BizTalk_Configuration_Database_found_on_server

Since I’m not the security “god” here, I have to wait until the “gods” answer my security prayer. (Please refer to other discussions as to what security is needed).

So what can I do in the meantime. Even when I switch back to the server name, it still gives that error because it cannot enumerate the databases on that SQLServer box.

I opened my project.btuser.proj file and typed it in the XML like so:

Sample_btproj.user

Close your solution, edit the file, simply put a value in the <pre><ConfigurationDatabase> tag </pre>, save it, then re-open your solution.

 

For a while, I’ve been wanting to explore deeper or “hack” into the internals of the BizTalk Databases, i.e. BizTalkMsgBox and BizTalkDTA. When do records gets stored in these database, how long do they stay there, and when do they get “cleaned-up”.

So here is my first scenario.  I use a SQL command to Find out how many rows of each table in your BizTalk Databases (as per previous blog post).

I modified the query to store the data in a statistics table that I created so that I could use queries to identify any differences. Here’s what that table looks like:
BizTalk_Internal_Database_Stats

Now let me show you how I collected the data. I ran two queries, once against each of the two databases, captured the date/time to use the exact same time (for purposes of joining later) and store statistics. I change the label ‘Before’ to ‘During’ and ‘After’ later when I run the script again.
First run:

USE BizTalkMsgBoxDb

declare @WorkDateTime dateTime2 = getDate() 
declare @Label varchar(15) = 'Test' 
print @WorkDateTime
print @Label 
insert into StatSnap.dbo.TableRowCounts  
SELECT @WorkDateTime, 'BizTalkMsgDb', sc.name +'.'+ ta.name TableName , @Label, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa  ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc  ON ta.SCHEMA_ID = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

USE BizTalkDTADb 
insert into StatSnap.dbo.TableRowCounts  
SELECT @WorkDateTime, 'BizTalkMsgDb', sc.name +'.'+ ta.name TableName , @Label, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa  ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc  ON ta.SCHEMA_ID = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC





Once the data is collected, the following query shows the which tables changed in size. I manually entered the date/times to match-up one specific earlier run from any later runs.

use statsnap
--truncate table TableRowCounts
--select * from TableRowCounts
select TRCBefore.DBName, TRCBefore.TableName, 
    TRCBefore.Label, TRCBefore.RowCountStat, 
    TRCDuring.Label, TRCDuring.RowCountStat,
    TRCAfter.Label, TRCAfter.RowCountStat,
    TRCLater.Label, TRCLater.RowCountStat
from TableRowCounts as TRCBefore 
inner join TableRowCounts as TRCDuring on TRCBefore.DBname = TRCDuring.DBName and TRCBefore.TableName = TRCDuring.Tablename 
inner join TableRowCounts as TRCAfter  on TRCBefore.DBname = TRCAfter.DBName and TRCBefore.TableName = TRCAfter.Tablename 
where TRCBefore.DateTimeStat = '2015-01-27 09:16:42.2800000'
  and TRCDuring.DateTimeStat = '2015-01-27 09:19:55.6900000'
  and TRCAfter.DateTimeStat  = '2015-01-27 09:27:30.7900000'
  and (TRCBefore.RowCountStat != TRCDuring.RowCountStat
  or   TRCBefore.RowCountStat != TRCAfter.RowCountStat
  )

The resulting query combining all stats:

BizTalkDatabases_before_during_after_RowCounts

 

I created an orchestration that takes a simple messages from a file receive location, maps it, delays 3 minutes, then sends it to a file. I snapshot the stats ‘Before’ starting the orchestration, ‘During’ – while the 3 minute delay is happening, then again ‘After’ the orchestration has completed.

One of the most important columns is nState, the values are described in this StackOverflow post.

    1 : Started
    2 : Completed
    3 : Terminated
    4 : Suspended
    5 : ReadyToRun
    6 : Active
    8 : Dehydrated
    16: CompletedWithDiscardedMessages
    32: SuspendedNonResumable
    64: InBreakpoint

Instances table

BiztalkMsgBoxDB_Instances_data

It turns out that I had four suspended instances for a few weeks back, 2 not-resumeable (nstate=32), and 2 resumeable (nstate=4) .  You can actually see the error messages in the nvcErrorDescription column.  I terminated those, then ran the query again. After deleting them, the following remain. Note that one has nState = 8, which would be my dehydrated orchestration (in the 3 minute delay). By running the query frequently while the orchestration is running, you can watch the nState change from 2 to 8.  Sometimes it stayed with the value of 2 the whole 3 minutes.

BiztalkMsgBoxDB_Instances_data_2

:Later, I dropped four different messages, causing 4 orchestration instances to start.  Below you can see which GUIDs are the same, and one one change per instance

BiztalkMsgBoxDB_Instances_data_3

Surprises

BTS_Applications table

The bts_applications table contains hosts, not applications:

BizTalkMsgBoxDB_Applications

Host Tables

This was not a surprise to me, because I had seen it before, but someone new to the internal of the database might find it surprising. When you create a new “Host” using BizTalk Admin Console (or via Object Explorer, WMI, or Powershell Extensions), five SQL tables are created for each host.

BizTalkMsgBoxDB_TablesCreatedPerHost

 

As Expected

BizTalkMsgBoxDB_BizTalkDBVersion

I will continue with more observations and details in Part 2.

 

 

 

In this blog, I’ll be showing how a Microsoft cursor in T-SQL is similar to and different from the most similar cursor in Oracle.  Oracle actually has 3 or 4 ways to do “explicit” cursors, so in this blog, we will look only at the one that is most similar.

I tried to format the code of each as close as possible to the other, so that the compare program would do a nice side-by-side view.  I even named the variables and cursor names as similar as possible.  Oracle users typically prefix variables with a lower-case “L” (which stands for local vs global).

I must admit, the Oracle code is more graceful.  Unfortunately, Microsoft requires you to do the initial fetch, then continue to fetch in a loop.  With Oracle, you can do the fetch in the loop, then exit out when needed. That’s the major reason that the code doesn’t line up well.  I put a big red bracket around the corresponding loops in the picture below.

Cursor_Compare_Oracle_to_Microsoft_SQLServer

Output

They both produce the exact same results. But not that of course, in the real world, we don’t normally use “print” or DBMS_OUTPUT statements to report back data to any program or user. Had to do this to keep it simple.

Start
FirstName=Neal LastName=Neal RowCount=1
FirstName=John LastName=John RowCount=2
FirstName=Fred LastName=Fred RowCount=3
FirstName=Barnie LastName=Barnie RowCount=4
End

 

Point by Point Discussion of Major Code Statements

The Declare Statement

In Microsoft, I usually repeat the DECLARE for each variable, I think it’s cleaner.  In Oracle, there is an entire “DECLARE” section before the BEGIN/END block where your code goes.  In the Microsoft example above, I used one DECLARE for the variables, but had to add a second one for the cursor.  Variables in T-SQL begin with the @ sign.

The Cursor Definition (blue arrow)

The cursor in each example is called cur_get_employees, and each is reading the NEAL.EMPLOYEE table (schema name is Neal, table name is EMPLOYEE).  The “INTO” is required in Oracle, at least for this style of cursor.  In a future blog, I hope to show other types of cursors.

    --T-SQL
    PRINT 'FirstName=' + @l_firstname + 
	      ' LastName=' + @l_firstname + 
	      ' RowCount=' + convert(varchar(3),@l_rowcount)

    -- PL/SQL 
    DBMS_OUTPUT.PUT_LINE('FirstName=' || l_firstname || 
                         ' LasttName=' || l_lastname || 
                         ' RowCount=' || cur_get_employees%ROWCOUNT); 

 

The T-SQL “Print” command writes to the “Message” output area, and is similar the Oracle function DBMS_OUTPUT.PUT_LINE. Oracle uses double pipe symbols to concatenate, while T-SQL uses the plus sign.  T-SQL requires specific conversion of numbers to strings before they can be concatenated.
In Oracle, each cursor has a built-in system variable called %ROWCOUNT.  I haven’t discovered anything like that in SQL, so I just incremented my own counter.

The Open/Close/DeAllocate Cursor Statements

The open/close are actually identical.  SQL further has a Deallocate statement to free memory when done with the cursor.  My guess is that in Oracle, that is handled when the “END” of the block occurs.

The Fetch and Loop Statements

The fetch is actually identical as well.  The only big difference, as stated at the beginning of this article, is that Oracle doesn’t require the initial fetch before beginning the loop. NOTE: I made one bug in my code above and it’s correct in the samples below.  The “PRINT” statement only needs to be included once at the beginning of the WHILE loop.  My code actually caused the last row to be printed twice, and I didn’t notice until after I did the elaborate picture above.

Microsoft returns a status in a system variable called @@FETCH_STATUS.  As long as the value is zero, we need to keep looping.  Oracle sets a similar variable, which is the cursor name follwoed by %NOTFOUND, and Oracle’s PL/SQL provides a nice way to jump out of the loop “EXIT WHEN cur_get_employees%NOTFOUND”.

If you want to copy/paste the code and try it for yourself, here is the code.  See yesterday’s blog (Comparing “Create Table” between Oracle and Microsoft SQLServer) to get the “Create Table” and “Insert Employee” statements.

Microsoft T-SQL Code
use Employees 

DECLARE 
        @l_empid INT, 
        @l_firstname varchar(20),
        @l_lastname varchar(20), 
        @l_state varchar(2),
        @l_rowcount int =  0 ; 

DECLARE 
   cur_get_employees CURSOR FOR  
   SELECT EMPID, FIRSTNAME, LASTNAME, STATE 
      FROM NEAL.EMPLOYEE;


    PRINT 'Start'; 
    OPEN cur_get_employees;

    SET @l_rowcount = @l_rowcount + 1
    FETCH cur_get_employees 
       INTO @l_empid, @l_firstname, @l_lastname, @l_state; 




   WHILE @@FETCH_STATUS = 0  
     BEGIN  
        PRINT 'FirstName=' + @l_firstname + 
	      ' LastName=' + @l_firstname + 
	      ' RowCount=' + convert(varchar(3),@l_rowcount)
        SET @l_rowcount = @l_rowcount + 1; 
        FETCH cur_get_employees 
			 INTO @l_empid, @l_firstname, @l_lastname, @l_state; 
     END  
 PRINT 'End' 

CLOSE cur_get_employees  
DEALLOCATE cur_get_employees 
Oracle PL/SQL Code
SET SERVEROUTPUT OFF;
DECLARE 
   l_empid     EMPLOYEE.EMPID%TYPE; 
   l_firstname EMPLOYEE.FIRSTNAME%TYPE; 
   l_lastname  EMPLOYEE.LASTNAME%TYPE; 
   l_state     EMPLOYEE.STATE%TYPE; 
  
   CURSOR cur_get_employees IS
   SELECT EMPID, FIRSTNAME, LASTNAME, STATE 
      INTO l_empid, l_firstname, l_lastname, l_state
      FROM NEAL.EMPLOYEE;

BEGIN 
      DBMS_OUTPUT.PUT_LINE('Start');
   
      OPEN cur_get_employees; 
      
      LOOP
          FETCH cur_get_employees 
             INTO l_empid, l_firstname, l_lastname, l_state;
          EXIT WHEN cur_get_employees%NOTFOUND;          
          DBMS_OUTPUT.PUT_LINE('FirstName=' || l_firstname || 
                              ' LasttName=' || l_lastname || 
                              ' RowCount=' || cur_get_employees%ROWCOUNT); 
      END LOOP;        
      
      DBMS_OUTPUT.PUT_LINE('End');
      
      CLOSE cur_get_employees; 
END;

And finally, I thought I would include a screen shot of the two graphical interfaces:

Microsoft SSMS – SQL Studio Management Server

Cursor_Compare_Sample_Program_Microsoft_SSMS

Oracle – SQL Developer

Note: You have to click the green plus sign in the “Dbms Output (lower right)” pane, in order for the DBMS_OUTPUT.PUT_LINE statements to be captured and displayed. Then you have to click the little eraser symbol, if you don’t want each run to add to the prior run.
Cursor_Compare_Sample_Program_Oracle_SQL_Developer

For this blog, I create an employee table using the Oracle’s GUI tool called “SQL Developer”.  I then exported that syntax to a file.With a few changes I added to Microsoft SQL Server (after adding the schema first), and modified it to include a primary key. My only reason to port this over was to do some more blogs and comparison of how certain features work in Oracle’s PL/SQL, and how they work in Microsoft’s T-SQL; so stay tuned for more comparisons in future blogs.

I then reformatted it to make it “skinny” so I could so a side-by-side compare and squeeze it in the frame below.  Identity columns were not introduced into Oracle until release 12c, and I was using 11g for this demo.  Unfortunately, all the data types had to be changed.  Microsoft uses square brackets around table and column names (just in case they conflict with reserved words), while Oracle uses double quotes.  When I defined the table in Oracle, I should have probably made the EMPID of type PLS_INTEGER instead of NUMBER.

Create_Table_Oracle_SQLServer_Side_by_Side_Compare

Oracle also generated the insert statements for me. I had to correct the EMPID on “Barnie Rubble” from a 3 to 4, because with the primary key constraint, I couldn’t load the data into SQL. Had I put an index on Oracle with a unique constraint, that should have also solved the problem. Other than that issue, the “INSERT” statements were 100% compatible as they are using standard ANSI SQL.

Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Barnie','Rubble',null);

However, since I added an identity field, I had to turn identity_insert off/on while running the query.

set identity_insert NEAL.EMPLOYEE on
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (4,'Barnie','Rubble',null);
set identity_insert NEAL.EMPLOYEE off

Oracle Create Table

  CREATE TABLE "NEAL"."EMPLOYEE" 
   (	"EMPID" NUMBER, 
	"FIRSTNAME" VARCHAR2(20 BYTE), 
	"LASTNAME" VARCHAR2(20 BYTE), 
	"STATE" VARCHAR2(2 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 
  MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 
  FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


Microsoft SQLServer Create Table

CREATE TABLE [Neal].[EMPLOYEE](
	[EMPID] [int] IDENTITY(1,1) NOT NULL, 
	[FIRSTNAME] [varchar](20) NULL,
	[LASTNAME] [varchar](20) NULL,
	[STATE] [varchar](2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[EMPID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
) ON [PRIMARY]

GO

Below I have put together some of my favorite examples of how to use Join-Path.

One of the primary features it to be able to combine a folder and a filename, without caring if the folder ends with a trailing slash or not. That is the first thing illustrated below (Path1/Path2).

Path3 shows how to incorporate environment variables to get at various system paths, and shows that when you use -Resolve Join-Path can return an array, but in the first examples it returns only a string. The point being that you need to know what is being returned to know how to process it. In Example 4, I show how to loop through the array. The other cool thing about Example 4 is that it can find files in multiple directories (e.g. both “Program Files” and “Program Files X(86)” by using the * mask in the folder’s name.

Example 6 shows that Join-Path works on the Registry as well, i.e. it’s not just limited to disk paths.

Jeffrey Snover, the architect behind PowerShell, also gives a few more Join-Path examples.  Some of his examples involve reading a list of files or folders from a file, and prefixing and suffixing them with other folders or filenames.

Code Sample

cls
$folder1 = "\\server01\folder01"
$folder2 = "\\server01\folder02\"
$file = "abc.txt"
$path1 = Join-Path $folder1 $file
$path2 = Join-Path $folder2 $file
Write "path1=$path1"
Write "path2=$path2"

$path3a  = Join-Path -path (get-item env:\windir).value -ChildPath system32
#short way of doing same
$path3b = Join-Path -path $env:windir -ChildPath system32
#get "My Documents" of current user 
$path3c = Join-Path $env:USERPROFILE -ChildPath "My Documents" 
$path3d = Join-Path $env:USERPROFILE "*" -Resolve
Write "path3a=$path3a"
Write "path3b=$path3b"
Write "path3c=$path3c"
Write "path3c.GetType=$($path3c.getType())"  #Note it is a string and not an array 
Write "path3d=$path3d"
Write "path3d.GetType=$($path3d.getType())"  #Note it is an array of strings 

# this one is more obtuse = search both Program Files and Program Files x(86) 
# (and other other c:\Program files* directory)
# for any files/folders starting with A. 
# It returns an array of folders 
$path4 = join-path -path "c:\Program Files*" A* -resolve
Write "`nExample 4"
Write "path4=$path4 "
Write "path4.GetType=$($path4.getType())"

loopCounter = 0 
foreach ($folder in $path4) 
   {
      $loopCounter++ 
      Write-Host "$loopCounter $folder"
   }

Write "`nExample 5 - Find Windows Log Files"
$path5 = join-path c:\win* *.log -resolve
Write "path5=$path5"

Write "`nExample 6 - Registry Path"
set-location HKLM:
$path6 = join-path System *ControlSet* -resolve
Write "path6=$path6"

Write "`nExample 7 - xml files in a directory"
$path7 = join-path "c:\Program Files (x86)\Notepad++"  *.xml -resolve 
$loopCounter = 0 
foreach ($file in $path7) 
   {
      $loopCounter++ 
      Write-Host "$loopCounter $file"
   }


Results

path1=\\server01\folder01\abc.txt
path2=\\server01\folder02\abc.txt
path3a=C:\Windows\system32
path3b=C:\Windows\system32
path3c=C:\Users\NWalters\My Documents
path3c.GetType=string
path3d=C:\Users\NWalters\.oracle C:\Users\NWalters\.VirtualBox C:\Users\NWalters\Contact
s C:\Users\NWalters\Desktop C:\Users\NWalters\Documents C:\Users\NWalters\Downloads C:\U
sers\NWalters\EurekaLog C:\Users\NWalters\Favorites C:\Users\NWalters\Links C:\Users\nea
l.walters\Music C:\Users\NWalters\Oracle C:\Users\NWalters\Pictures C:\Users\NWalters\Ro
aming C:\Users\NWalters\Saved Games C:\Users\NWalters\Searches C:\Users\NWalters\Videos 
C:\Users\NWalters\VirtualBox VMs
path3d.GetType=System.Object[]

Example 4
path4=C:\Program Files\Application Verifier C:\Program Files\AuthenTec C:\Program Files (x86)\Adobe 
C:\Program Files (x86)\AppInsights C:\Program Files (x86)\Application Verifier 
path4.GetType=System.Object[]
1 C:\Program Files\Application Verifier
2 C:\Program Files\AuthenTec
3 C:\Program Files (x86)\Adobe
4 C:\Program Files (x86)\AppInsights
5 C:\Program Files (x86)\Application Verifier

Example 5 - Find Windows Log Files
path5=C:\Windows\DirectX.log C:\Windows\DPINST.LOG C:\Windows\DtcInstall.log C:\Windows\ENU-ie90.log
 C:\Windows\IE11_main.log C:\Windows\IE90-ENU.log C:\Windows\iis7.log C:\Windows\msxml4-KB954430-enu
.LOG C:\Windows\msxml4-KB973688-enu.LOG C:\Windows\PFRO.log C:\Windows\setup.log C:\Windows\setupact
.log C:\Windows\setuperr.log C:\Windows\TSSysprep.log C:\Windows\WindowsUpdate.log

Example 6 - Registry Path
path6=HKLM:\System\ControlSet001 HKLM:\System\ControlSet002 HKLM:\System\CurrentControlSet

Example 7 - xml files in a directory
1 C:\Program Files (x86)\Notepad++\config.model.xml
2 C:\Program Files (x86)\Notepad++\contextMenu.xml
3 C:\Program Files (x86)\Notepad++\functionList.xml
4 C:\Program Files (x86)\Notepad++\langs.model.xml
5 C:\Program Files (x86)\Notepad++\shortcuts.xml
6 C:\Program Files (x86)\Notepad++\stylers.model.xml

<h1>Estimated Completion Time in PowerShell</h1>

Progress Bars are all about giving the user feedback as to what the program is doing, and Powershell implements it with the Write-Progress cmdlet. It is also possible to predict the completion time by using a little math.

Possible uses for the progress bar include:

  1. Showing status of reading a large file
  2. Showing status of exploding a directory structure
  3. Showing progress of creating files or videos
  4. Showing status of processing a large number of database records
  5. Showing status of file upload or download

In my opinion, a status bar should not just show the progress, but also tell the user the estimated completion time (sometimes called ETA – Estimated Time of Arrival). This is done by computing a ratio of the word done to the work to be performed, and using that ratio along with elapsed time and TimeSpans to come up with the estimated completion time.

cls
$maxI = 250 
$startTime = get-date 
Write-Host "StartTime=$startTime"
for ($i = 1; $i -le $maxI; $i++ )
{
  write-host "`$i=$i"
  start-sleep -milliseconds 250  
  $elapsedTime = $(get-date) - $startTime 

  #do the ratios and "the math" to compute the Estimated Time Of Completion 
  $estimatedTotalSeconds = $maxI / $i * $elapsedTime.TotalSeconds 
  $estimatedTotalSecondsTS = New-TimeSpan -seconds $estimatedTotalSeconds
  $estimatedCompletionTime = $startTime + $estimatedTotalSecondsTS
  Write-Host "elapsedTime=$($elapsedTime.TotalSeconds) estimatedTotalSeconds=$estimatedTotalSeconds"
  Write-Host "estimatedCompletionTime=$estimatedCompletionTime"

  #I like to keep the width of the Write-Progress minimalized by creating three variables 
  #and substituting them into the Write-Progress statement. 
  $activityMsg = "Search in Progress"
  $percentComplete = $i / $maxI * 100 
  $statusMsg = "$percentComplete% Complete: $i out of $maxI Estimated Completion at $estimatedCompletionTime"

  write-progress -activity $activityMsg -status $statusMsg  -percentcomplete $percentComplete;
  Write-host "percent complete=$percentComplete "
}
$endTime = get-date 
$elapsedTime = $endTime - $startTime 
write-host "The End $startTime=$startTime endTime=$endTime elapseSeconds=$($elapsedTime.TotalSeconds)"

write-host "The End" 

Running in ISE, the progress bar looks like this (it actually seems to hide the first few lines in the output pane).

Powershell_Write_Progress_Bar

The command line version is not quite as pretty, but still effective:

Powershell_Write_Progress_Bar_CommandLine
WindowsClockI suggest you take this example, change the value of $maxI and/or change the sleep time, and see if you get an accurate prediction of the completion time.

In a prior blog, I demonstrated how to call a SQL Stored Proc from Powershell.  But that article did not discuss error handling.

In the stored proc, it stored two tables in a parent/child relationship.  If the parent “key” was not found, then the child row cannot be stored. The parent is the based on the customer “license” code.  So in the example below, I pass “bad license” in the $license variable.

Powershell detects the errors, shows it in red, and yet continues to run.

Results with error

Powershell_SQL_Error_In_SPROC

If you want to catch the error, handle it some special way, then continue or stop, you do that with the try/catch statement.

Sample Powershell Code with Try/Catch

CLS
#$license = "4F926ADB-8193-4824-9881-DF147B721889"
$license = "bad license"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4"
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()

try
{
  LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate
}
catch
{
   # Handle the error
   $err = $_.Exception
   Write-Host $err.Message
   #There could possibly be multiple inner exceptions but not in this example. 
   while( $err.InnerException ) 
     {
        $err = $err.InnerException
        Write-Host "InnerException: $($err.Message)" 
     }
}
finally 
{
     write-output "script completed"
}

Results with Catch error

Notice that all the text is white on blue, indicating we caught the error, and printed it in a regular Write-Host statement.

Powershell_SQL_Catch_Error_In_SPROC

The full code of the stored proc was included in the prior blog. Here are the lines of the Stored Proc that generated the above error:

	select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

	if @CustomerID is null 
		BEGIN	
			SET @ErrMessage = 'No customer license found for key=' + @License 
			RAISERROR (@ErrMessage, 11,1)
		END

I could have also put the catch error in the function itself, and returned a better $result variable.

Sometimes you call SQL just to store data, but even then, sometimes you need the key of the row just stored. This code will show you how to get back that single return value when calling a stored proc, even when it is an “ExecuteNonQuery” stored proc, that returns no row sets. It is typical to return either a key value or a status/result code in such as scenario.

This is a follow-up to an early blog How Call a SQL Stored Procedure from Powershell.

Powershell SQL Functions

This example also illustrates how to convert a Powershell boolean field from $true/$false to SQL bit value of 1 or 0.

function GetConnectionString()
{
  return "Server=myServer;Database=myDatabase;Integrated Security=False;User ID=myuser;Password=mypass"
}

function LogVideoComputerToSQL($license, $computerName, $CPUProcessorName, $GraphicsProcessorName, $IsLaptop, $NumLogicalProcessors, $MaxClockSpeed) 
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString #call little function above to get connection string 
 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogComputer"  #name of the stored proc here 
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure 

      #set each of the 7 parameters 

      $param1=$SqlCmd.Parameters.Add("@License" , [System.Data.SqlDbType]::VarChar)
      $param1.Value = $license 

      $param2=$SqlCmd.Parameters.Add("@computerName" , [System.Data.SqlDbType]::VarChar)
      $param2.Value = $computerName 

      $param3=$SqlCmd.Parameters.Add("@CPUProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param3.Value = $CPUProcessorName 

      $param4=$SqlCmd.Parameters.Add("@GraphicsProcessorName" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = [String] $GraphicsProcessorName 
      
      $param5=$SqlCmd.Parameters.Add("@IsLaptop" , [System.Data.SqlDbType]::bit)
      if ($IsLaptop) 
        {
           $param5.Value = 1
        }
      else 
        {
           $param5.Value = 0
        }

      $param6=$SqlCmd.Parameters.Add("@NumLogicalProcessors" , [System.Data.SqlDbType]::int)
      $param6.Value = [string] $NumLogicalProcessors 

      $param7=$SqlCmd.Parameters.Add("@MaxClockSpeed" , [System.Data.SqlDbType]::int)
      $param7.Value = [string] $MaxClockSpeed 

      #TWO LINES BELOW ARE WHAT YOU ARE LOOKING FOR 
      $paramReturn = $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
      $paramReturn.Direction = [System.Data.ParameterDirection]"ReturnValue"


    $SqlConnection.Open()
    $result = $SqlCmd.ExecuteNonQuery()   #call the stored proc here 
    #### LINE BELOW IS THE RETURN VALUE #### 
    $returnValue = [int]$sqlCmd.Parameters["@ReturnValue"].Value    #####<------ Here is the return value moved to var. 
    Write-Host "result=$result returnValue=$returnValue" 
    $SqlConnection.Close()
    return $returnValue
}

Code to call the above function

There is some interesting code here as well. It shows how to retrieve some information from WMI about the CPU and graphics card; that is what we are passing to the subroutine.  $computerID is the variable being returned.

    $license = "my-license-key"
    #Get-WmiObject Win32_Processor | Select-Object * 
    $Win32Processor =Get-wmiobject Win32_Processor  
    $ProcessorName        = $Win32Processor | select-object -expand Name
    $NumLogicalProcessors = $Win32Processor | select-object -expand NumberOfLogicalProcessors
    $MaxClockSpeed        = $Win32Processor | select-object -expand MaxClockSpeed 
    $ComputerName         = $Win32Processor | select-object -expand PSComputerName

    $isLaptop = isLaptop ## call function 

    $VideoController = get-wmiobject -class CIM_VideoController
    $GraphicsProcName = $VideoController | select-object -expand Name
    $VideoProcessor   = $VideoController | select-object -expand VideoProcessor
    $VideoProcessor2  = $VideoController | select-object VideoProcessor

    Write-Host "`n`nCalling SQL Function"
    $computerID = LogVideoComputerToSQL</strong> $license $ComputerName $ProcessorName $GraphicsProcName $IsLaptop $NumLogicalProcessors $MaxClockSpeed 
    Write-Host "computerID=$computerID"

The T-SQL Stored Proc code

I’m not going to bore you with the whole stored proc here, just the return statement. I’m basically doing an insert into a table with a primary key that is an integer and an identify field. The function “SCOPE_IDENTITY()” returns the identity key of the row just stored it. I can return it now to Powershell, so it can be used in other SQL calls to associate the other data that I’m storing back with the computer information.

   declare @ComputerID int 
   insert ... 	
   set @ComputerID = SCOPE_IDENTITY()  
   return @ComputerID 

Example of a row stored in SQL

ReturnValueFromSQLToPowershellExampleData

 

Cannot connect to Amazon RDS with SSMS

Amazon allows you to run an Relational Database Server (RDS) that runs several of the common editions of Microsoft SQLServer. After setting up your RDS server/instance and launching it, you may have issues connecting to it.

Cannot connect to SQLServer – Error Message

Microsoft SQLServer has a very general message when you cannot connect.

SQLServer_Connection_Failure

It reads as follows:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible…

Within the Amazon AWS console, there is a screen for setting up your Virtual Private Cloud (VPC). Within that, one of the options is for the “Security Groups”. By default, you RDS SQLServer is open only to certain spaces within the Amazon world. To open it up to an external program, such as SQL Server Management Studio (SSMS) on your PC, you will have to add a security group or change the existing one.

Amazon VPC Security Group Default

The screen shot below show the original value. The blue square “dot” at the top shows the security group that I currently have selected, and the bottom half of the screen gives the details on four different tabs. It’s the Inbound Rules that need to be adjusted. By default, the source, in my case, was sg-0ee39c6b which was the default security group. I’m still not 100% sure what it represents, but it definitely doesn’t include anything outside of the Amazon data centers. (By the way, in this screen shot I’m in “Edit” mode.)

AWS_VPC_SecurityGroup_Original

Amazon VPC Security Group Modified

The screen show below (not in edit mode), shows the change I made. I changed the “Source” from the security group-id to a CIDR of 0.0.0.0/0, which basically means any IP Address.  You are basically defining firewall rules here.  It would be best to only open port 1433, which is used by SQL, to avoid any other hackers trying to attack your server.  A common technique is to open everything first, make sure it works, then starting closing the doors to everything except the minimum that are needed to be left open.

AWS_VPC_SecurityGroup_Corrected

To open up for example, you home PC, use “What is my IP Address” in Google to get how the outside world views your IP Address, then convert it to a CIDR. Here is the URL of a handy online IP Address Range to CIDR converter. That utility only accepts a range of IDs (and the ending range has to be different from the starting range).