I’m building a little app that is driven by an XML configuration file. I need to substitute variables from the XML with a random name or a keyword from another source, so I used $Keyword and $MaleName and $FemaleName.

Look at this code, and see if you can spot the bug. Every single text field that I passed was showing that it contains a characterName (i.e. either $MaleName or $FemaleName.

Function IsTextViolation($text) 
{
    $isViolation = $false 
    $containsCharacterName = $false 
    if ($text.Contains("$MaleName") -or $text.Contains("$FemaleName")) 
       {
          $containsCharacterName = $true 
       }
     Write-Host "containsCharacterName=$containsCharacterName" 

     if ($text.Contains("$Keyword") -and $containsCharactername) 
        {
           $isViolation = $true 
        }
      return $isViolation 
}
cls
Write-Host "1 $(IsTextViolation('Test 1 $Keyword') )"
Write-Host "2 $(IsTextViolation('Test 1 $MaleName') )"
Write-Host "3 $(IsTextViolation('Test 1 $FemaleName') )"
Write-Host "4 $(IsTextViolation('$MaleName Test 1 $Keyword') )"
Write-Host "5 $(IsTextViolation('Test 1 $FemaleName $Keyword') )"

So here is the issue… I chose to use $ sign as prefix to my variable, I could have used % or @ or any other symbol, but with Powershell on the brain, I chose the dollar sign. Well, when you put $MaleName in double quotes, it was substituting the value of $MaleName, which is apparently null ($null). And thus the match was always true. I had two solutions, either 1) use single quotes instead of double quotes, or 2) prefix with the escape character (the grave accent mark like this – (“`$MaleName”). I chose the first, and here is the corrected code.

Corrected Code:

Function IsTextViolation($text) 
{
    $isViolation = $false 
    $containsCharacterName = $false 
    if ($text.Contains('$MaleName') -or $text.Contains('$FemaleName')) 
       {
          $containsCharacterName = $true 
       }
     Write-Host "containsCharacterName=$containsCharacterName" 

     if ($text.Contains('$Keyword') -and $containsCharactername) 
        {
           $isViolation = $true 
        }
      return $isViolation 
}


cls
Write-Host "1 $(IsTextViolation('Test 1 $Keyword') )"
Write-Host "2 $(IsTextViolation('Test 1 $MaleName') )"
Write-Host "3 $(IsTextViolation('Test 1 $FemaleName') )"
Write-Host "4 $(IsTextViolation('$MaleName Test 1 $Keyword') )"
Write-Host "5 $(IsTextViolation('Test 1 $FemaleName $Keyword') )"

[Reflection.Assembly]::LoadWithPartialName("System.Messaging")

cls

$queueName = '.\Private$\NealTest'
$queue = new-object System.Messaging.MessageQueue $queueName
$utf8  = new-object System.Text.UTF8Encoding

$msgs = $queue.GetAllMessages()
 
write-host "Number of messages=$($msgs.Length)" 

foreach ($msg in $msgs)
  {
      write-host $msg_.Id
      write-host $utf8.GetString($msg.BodyStream.ToArray())
  }


Depending on how many times you wrote messages to the queue, the output will look something like this:

MSMQ_Read_Results

First make sure MMSQ is installed, and then for now, manually create your queue using the Server Manager MMC (Microsoft Management Console) GUI (Graphical User Interface). Perhaps in an upcoming blog I will demonstrate the creation of the queue itself in Powershell.

NOTE: MSMQ can only hold messages up to 4MB!

MSMQ_New_PrivateQueue1

Select whether you want your queue to be transactional or not (for help in making this decision, see this link: http://msdn.microsoft.com/en-us/library/ms704006%28v=vs.85%29.aspx
MSMQ_New_PrivateQueue2

Here is the sample code. I have created two functions, one which supports transactional queues, and one non-transactional queues. Note, if I wanted to make the routines more flexible, I could allow the encoding type to be passed as a parameter; the code below assumes UTF8 encoding.

[Reflection.Assembly]::LoadWithPartialName("System.Messaging")


function WriteMessageToMSMQTrans($queueName, $message, $label)
  {
     $queue = new-object System.Messaging.MessageQueue $queueName
     $utf8 = new-object System.Text.UTF8Encoding

     $tran = new-object System.Messaging.MessageQueueTransaction
     $tran.Begin()

     $msgBytes = $utf8.GetBytes($message)

     $msgStream = new-object System.IO.MemoryStream
     $msgStream.Write($msgBytes, 0, $msgBytes.Length)

     $msg = new-object System.Messaging.Message
     $msg.BodyStream = $msgStream   
     if ($label -ne $null)
       {
         $msg.Label = $label
       }
     $queue.Send($msg, $tran)

     $tran.Commit()
     Write-Host "Message written"
  }

function WriteMessageToMSMQNonTrans($queueName, $message, $label)
  {
     $queue = new-object System.Messaging.MessageQueue $queueName
     $utf8 = new-object System.Text.UTF8Encoding

     $msgBytes = $utf8.GetBytes($message)

     $msgStream = new-object System.IO.MemoryStream
     $msgStream.Write($msgBytes, 0, $msgBytes.Length)

     $msg = new-object System.Messaging.Message
     $msg.BodyStream = $msgStream
     if ($label -ne $null)
       {
         $msg.Label = $label
       }
     $queue.Send($msg)

    Write-Host "Message written"
  }

#-------------------------
#Test the above functions 
#-------------------------
cls  #clear screen (junk from prior runs) 
$queueName = ".\Private$\nealtest"

$message = "This is my first test message"
WriteMessageToMSMQNonTrans $queueName $message $null

$message = "This is my second test message"
WriteMessageToMSMQNonTrans $queueName $message "MyLabel"

Write-Host "Completed"

 

Now we can go view the two messages written to the queue.

MSMQ_in_Server_Manager

 

You can right-click “Properties” on either message.  Below, I’m showing the “General” tab with the date the message was written, then the “Body” tag which shows the hex and text of the message.

 

MSMQ_Properties_Tab_General

 

MSMQ_Properties_Tab_BodyText

 

 

The question today:  Should BizTalk Administrators be granted/given the “SysAdmin” role (authority) in the SQL database that supports BizTalk.

Generally, my answer is yes (at least on the BizTalk machine that is running SQL to support BizTalk, and here’s why.

1) The BizTalk Admins typically have to deal with the SQL Agent Jobs.  BizTalk installs about 14 jobs, and you’ll need SysAdmin at a minimum while doing the install.

2) BizTalk Admins sometimes need full power such as creating new databases, backing-up and restoring databases, attaching and detaching databases.

NOTE: Most companies should not be running the BizTalk database an their production databases on the same machine.  Most everyone separates the two for performance reasons.  Thus, giving the BizTalk Administrator the SysAdmin rights is only required on the BizTalk machine (or the SQL machine that supports BizTalk).  In larger BizTalk installation, even BizTalk and the BizTalk that supports SQL are separated onto two physical (or logical/virtual) machine names.

BizTalk 2010 Default SQL Agent Jobs Installed

BizTalk 2010 Default SQL Agent Jobs Installed

In some bigger companies, duties might be separated, such that there is really a person in the BizTalk Admin role, and that person handles the installs and the management of the SQL Agent Jobs.  In most companies, the BizTalk Developers play somewhat of a BizTalk Admin role as well (at least in the development, if not the production environment).

Further, in some companies, the developers are allowed to create SQL Agent Jobs related to the development database, while in other companies only the DBA will have that authority.

The corrollary of the above question would be this:  Why can’t I see the BizTalk SQL Agent Jobs when I open SSMS (SQL Studio Management Studio).  The answer is that you need the SysAdmin SQL role to see the Agent Jobs.

NOTE: If you want to be more granular, instead of granting SysAdmin, there are three roles specific to SQL Agent: SQLAgentUserRole (users get access only to jobs owned by them), SQLAgentReaderRole  (can view but not change all jobs, but also get update on their own jobs as per the prior role),  SQLAgentOperatorRole (everything in prior role, but can execute enable/disable any job in system, just still can only modify their own jobs).

<a target=”_blank” href=”https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/”>https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly</a>

Adding the SysAdmin role is demonstrate in both the GUI mode and the command mode here:

<a target=”_blank” href=”http://stackoverflow.com/questions/14814798/how-to-add-sysadmin-login-to-sql-server”>http://stackoverflow.com/questions/14814798/how-to-add-sysadmin-login-to-sql-server</a>

 

 

This looks like a great tool for building an HTML based Help system:  http://www.helpndoc.com

I discovered it when looking at the new help for BTDF (BizTalk Deployment Framework), and wanted to shared it with you, and file it away in case I need it in the future.

 

helpNDoc1

It’s free for personal use, but I don’t think its Open Source; the free version will apparently put banners in the various types of help documents created. The price is British Pounds for the standard and professional editions.

 

 

Subversion is a popular open-source source control system.  In my last blog “Intro to SVN“, I gave some examples of how the SlikSVN commands work from the Windows Command Line.  ToirtoseSVN is a window shell menu and GUI for SVN.  After installing TortoiseSVN, you simply right-click on the file or directory, and the TortoiseSVN command appear.

Instead of using Windows Explorer, I use a handy tool called “Total Commander” which makes life easier and faster, so in the screen shots below, that is what you will be seeing.

I changed a file, then right-clicked on it, and selected “SVN Commit”.

Tortoise_SVN_Commit

 

Below, I’m showing the code in my short Powershell program, along with the TortoiseSVN Commit screen.  I typed in the reason for my commit (this is the same as doing SVN Commit -m “Add ABCTest to force future conflicts”.  I have a C:\Code\Demo copy of the source programs, and a C:\Code\Demo2 copy.  I needed two copies so I could simulate two people updating the same program, in order to simulate a conflict.

Tortoise_SVN_Commit_Message

 

The results of the commit are shown below:

Tortoise_SVN_Commit_Finished

 

Now below, I try to do an “SVN Update” in the C:\Code\Demo directory. “SVN Update” is similar to “Get Latest” in TFS. It pulls the new code and code changes from the SVN database/library to your local disk/workspace.

 

Tortoise_SVN_UpdateConflict

 

The update fails because I had changed line 6 of C:\Code\Demo\Sample1.ps1 to conflict with the changes made and commited from C:\Code\Demo2\Sample1.ps1.  Thus the code in C:\Code\Demo\Sample1.ps1 needs resolution, usually by a Merge process.

 

Tortoise_SVN_UpdateConflictDetected

 

I did a right-click, chose “Tortoise SVN” then “Merge” (menu selection not shown here).  Forgot to save that one.

I was kind of surprised by what it did.  It actually created several files in the C:\Code\Demo directory.

 

Then, in the directory, it preserved my code as Sample1.ps1.mine, and then added the last two releases (or commits) of that program, suffixed with “.r##” where the version number was 7 and 8 in my scenario.

Tortoise_SVN_UpdateConflictAfter

the file called Sample1_Merge.ps1 looks like this:

Tortoise_SVN_MergeResult1

At this point, you can use any external merge tool, such as WinCompare or the merge tool built into Total Commander.  Or, to use the tool built-in to TortoiseSVN, right click the source code file, select “TortoiseSVN” then “Edit Conflicts” (not Merge).   NOTE: if you use an external merge tool, then after you rmerge is done, you must select “TortoiseSVN” and then “Resolve”.

 

Tortoise_SVN_EditConflicts

 

In my case, the built-in merge tool shows Sample1.ps1.r8 (release 8 from the repository) on the left, and Sample1.ps1.mine on the right.

 

Tortoise_SVN_EditConflicts_Compare

Highlight the code you want, then click “Use Blocks” and select “Mine” or “Theirs” or the desired choice (screen shot not shown).

Then at the bottom, you see the results of the merge.

 

Tortoise_SVN_EditConflicts_MergeResultsAtBottom

When happy with the results, click “Save” , then you have an option to “Mark as Resolved” or “Leave as Conflicted”.  If you are really done with the merge, click “Mark as Resolved”, and the files will be cleaned up (as shown in the following screen shot.  Perhaps you only had time to merge 1/2 the file, and you want to save it and continue tomorrow.  In that case, you could select “Leave as conflicted”.

Tortoise_SVN_EditConflicts_SaveResolve

When you “Resolve”, notice that the .r7, .r8. and .mine files are removed.

Tortoise_SVN_EditConflicts_FilesAfterResolve

Enter your commit comments…

Tortoise_SVN_EditConflicts_Commit2

And now see that the version went form revision 8 to revision 9 – and that completes the process.

Tortoise_SVN_EditConflicts_Commit3

 

 

My client uses SVN (Subversion) instead of TFS; so something new to learn. In the past, I have had clients that used AccuRev and Borland’s StarTeam. So it would seem that 20% or more of my clients have not used TFS for source control.

I downloaded SlikSVN from https://sliksvn.com/download/, and used the free doc here to learn the basics. The doc is available both in PDF format and HTML.  In this blog, I will be sharing my “Introduction to SVN”, which is basically just trying some of the simple commands with a couple of small code files (I chose Powershell instead of BizTalk to keep it super simple.)

The first thing I had to do was to create a “Repository”. I chose the File System repository, which seems to be recommended. It’s also called an “FSFS-backed repository.”

C:\&gt;svnadmin create SVNRepository
</code>

When dealing with with multiple users, you will also have to setup a web server and a URL. But for single machine, single user access, you can simply refer to the respository I created as file:///C:/SVNRepository.

I had two Powershell programs that I was using as demos. For example, Version 1 looked like this:

# Neal Version - SVN learning and exercises 
# Date Created: 12/22/2014 

Write-Host "Hello World " 
Write-Host "The End"
</code>

 

Then I added the date, and for small changes just changed the text inside the last "Write-Host" statement.

# Neal Version - SVN learning and exercises 
# Date Created: 12/22/2014 

$showDate = get-date  #added 12/22/2014 
Write-Host "Hello World $showdate" 
Write-Host "The Demo2 Change End" 


 

 

 

c:\Code&gt;svn import c:/Code/Demo file:///c:/SVNRepository -m "Initial Checkin"
Adding  (bin)  Demo\Sample1.ps1
Adding  (bin)  Demo\Sample2.ps1

Committed revision 1.

c:\Code&gt;svn checkout file:///c:/SVNRepository 
A    SVNRepository\Sample1.ps1
A    SVNRepository\Sample2.ps1
Checked out revision 1.

c:\Code&gt;svn status
svn: warning: W155007: 'C:\Code' is not a working copy
c:\Code&gt;cd demo

c:\Code\Demo&gt;svn status
svn: warning: W155007: 'C:\Code\Demo' is not a working copy


 

The problem above was that I left off the Path from the Checkout statement. The doc shows an example with no Path, so I found it rather confusing. Here is the corrected checkout:

c:\Code&gt;svn checkout file:///C:/SVNRepository C:\Code\Demo2
A    Demo2\Sample1.ps1
A    Demo2\Sample2.ps1
Checked out revision 1.


 

"SVN status" won't show anything until you change a file. I opened Sample1.ps1 with notepad, changed it, then ran the command. "SVN status" runs totally on the client without accessing the repository.

c:\Code\Demo2&gt;svn status 
M       Sample1.ps1 


 

One important note, when creating the Powershell programs using the "ISE" utility, it encoded them as UTF-16. This caused SVN to see them as binary, and I had to follow the advice of this StackOverflow post to solve the problem.

I changed the text of the final "Write-Host" statement in Demo2 and checked-it in using the "Commit" subcommand. The -m is required to specify the description of your changes.

c:\Code\Demo2&gt;svn commit -m "Demo2 update Write-Host"
Sending        Sample1.ps1
Transmitting file data .
Committed revision 5.


You can add the -u parm to the "Status" subcommand, and it will show you the files that are in need of update (with the *), i.e. this command now contacts the repository. Since I commited Sample1.ps1 in Demo2, I now switch back to my Demo directory, and see that Sample1.ps1 is out-of-date and in need of update from the repository.

c:\Code\Demo&gt;svn status -v -u
        *        3        3 neal.walters Sample1.ps1
M                4        4 neal.walters Sample2.ps1
                 3        3 neal.walters .
Status against revision:      5


So then I changed the same line of code in C:\Code\Demo\Sample1.ps1 and tried a commit:

c:\Code\Demo&gt;svn commit -m "Test conflict"
Sending        Sample1.ps1
svn: E155011: Commit failed (details follow):
svn: E155011: File 'C:\Code\Demo\Sample1.ps1' is out of date
svn: E160028: File '/Sample1.ps1' is out of date


I should have noticed above that "*" told me that Sample1.ps1 was out of date, and so I need to do an "SVN Update":

c:\Code\Demo&gt;svn commit -m "Test conflict"
Sending        Sample1.ps1
svn: E155011: Commit failed (details follow):
svn: E155011: File 'C:\Code\Demo\Sample1.ps1' is out of date
svn: E160028: File '/Sample1.ps1' is out of date

c:\Code\Demo&gt;svn update
Updating '.':
C    Sample1.ps1
Updated to revision 5.
Conflict discovered in file 'Sample1.ps1'.
Select: (p) postpone, (df) show diff, (e) edit file, (m) merge,
        (mc) my side of conflict, (tc) their side of conflict,
        (s) show all options: m
Merging 'Sample1.ps1'.
Conflicting section found during merge:
(1) their version (at line 6)         |(2) your version (at line 6)
--------------------------------------+--------------------------------------
Write-Host "The Demo2 Change End"     |Write-Host "The End - Some other Demo
--------------------------------------+--------------------------------------
Select: (1) use their version, (2) use your version,
        (12) their version first, then yours,
        (21) your version first, then theirs,
        (e1) edit their version and use the result,
        (e2) edit your version and use the result,
        (eb) edit both versions and use the result,
        (p) postpone this conflicting section leaving conflict markers,
        (a) abort file merge and return to main menu: 2
Merge of 'Sample1.ps1' completed.
Select: (p) postpone, (df) show diff, (e) edit file, (m) merge,
        (r) mark resolved, (mc) my side of conflict,
        (tc) their side of conflict, (s) show all options: r
Resolved conflicted state of 'Sample1.ps1'
Summary of conflicts:
  Text conflicts: 0 remaining (and 1 already resolved)


Above, I selected option (2) for "use your version", causing my change to override the change made by the other "fake" person (i.e. the other directory where I changed the code differently. Then I had to further select "R" for "mark Resolved". After the merge and resolve, the commit works fine:

c:\Code\Demo&gt;svn commit -m "Update text in final msg"
Sending        Sample1.ps1
Sending        Sample2.ps1
Transmitting file data ..
Committed revision 6.


If all the above looks antiquated, I find it that same.Working with the command line utilities to me seems going backward, but yet I know some people love it. Hopefully, in the next blog, I'll show you the similar results by using TortoiseSVN, a graphical user interface which installs as a shell into the Windows right-click facility of Windows Explorer.  It is plug compatible with your existing repository, and you can switch back and forth between the command line and the Tortoise GUI anytime.

I’m at a new client, and here’s the scenario.  They have good Hosts and Host Instances set up in production, and maybe the QA environment (I don’t have access yet).  But in the development environment, they have been using the default host instances.  I still can’t imagine how they did their binding files correctly.  One of my first goals is to get them to use the BizTalk Deployment Framework.  The second goal is to make the host instances the same in the development environments (and also on the VMs my co-worker and I are running).

So as soon as they give me a list of the Production Host-Instances I want to re-create them in the 4 Dev Environments.  Obviously, a script is needed. The script below does the job for the Hosts and Host Instances.  What I have left to do is to script the setup of the adapters that should be associated with the Host Instances.

I recently read this blog by Sandro Pereira: https://sandroaspbiztalkblog.wordpress.com/2013/09/05/powershell-to-configure-biztalk-server-host-and-host-instances-according-to-some-of-the-best-practices/  I’m not sure why he called WMI directly, maybe he wrote that before the Powershell Extensions became available on CodePlex: http://psbiztalk.codeplex.com  I had some errors getting it to work.  Further, his logic goes and builds the “best practices”.  I’m not yet ready or able to change the clients practices in Production.  (It’s funny how some clients give you the “keys to the kingdom” on the first day, and others keep you in more of a “pen”.  It depends on the size of the company, their security and deployment practices, whether or not you have to support and fix production issues, and what exactly you were hired to do.

The Script below, as short as it is, took me several hours to get working. The documentation link on the Powershell Extensions was broken on CodePlex, so I could not download any documentation, so it was sort of a trial and error approach. What I love, and borrowed from Sandro Pereira’s code above, is the ability of Powershell to prompt for the password, and save it in a $credentials object; then you can pass that when you set up the Host-Instances (i.e. the user/pass for the service name that runs the Host-Instances.)

NOTE 1: I have not tested this in an environment where there are more than one BizTalk server in the group.  In that case, code changes would have to be made to add one Host-Instance per server.

NOTE 2: In BizTalk 2013, Powershell Extensions are included with the install, you don’t need the CodePlex extensions.  http://www.quicklearn.com/blog/2013/07/19/automating-and-managing-biztalk-server-2013-with-powershell/

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn
#get-PsSnapIn -registered   ### list registered Snap-In's

function AddHostAndHostInstance($HostName, $Is32BitOnly, $IsTrackingOn, $HostType)
{
 if ($HostType -eq "Isolated") 
   {
     $HostTypeCode = 2
   }
   else 
   {
     $HostTypeCode = 1 
   }
 #$HostType = 1 # 1 = InProcess assuming 2 = ISO 
 $myNTHostGroupName = "mscwvbizd01\BizTalk Application Users"
 $AuthTrusted = $false
 $serverName = "mscwvbizd01"
 #$HostItemType = 1 #NOTE: I got an error when I tried using the -ItemType on the New-Item of the Host-Instance 
 #(fortunately it is defaulting to In-Process) 

 cd "Biztalk:\Platform Settings\Hosts"
 Write-Host "Try to add New Host=$hostName"
 #New-Item $HostName -HostType:$HostType -NtGroupName:$NTGroupName -AuthTrusted:$AuthTrusted
 $temp = New-Item -path $hostName -HostType:$HostTypeCode -NtGroupName:$myNTHostGroupName -AuthTrusted:$AuthTrusted 
 Set-ItemProperty -Path $hostName -Name 'Is32BitOnly' -Value $Is32BitOnly
 Set-ItemProperty -Path $hostName -Name 'HostTracking' -Value $IsTrackingOn
 dir

 cd "BizTalk:\Platform Settings\Host Instances"
 Write-Host "Try to add New HostInstance=$hostName"
 dir
 New-Item $hostName -HostName $hostName -Credentials $hostCredentials -RunningServer $serverName
 #dir
}
### MAIN CODE HERE - Calls Function Above once per Host/Host-Instance ### 

$domainName = "biztalkdev"
$serviceUserid = "biztalkservice"

$hostCredentials = $Host.ui.PromptForCredential("Logon Credentials","This account must have SQL Server permissions.", $domainName + "\" + $serviceUserid, "");
#[String]$hostCredentialsPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($hostCredentials.Password));

# pass three parms: first host name, is32Bit, IsTracking 
AddHostAndHostInstance "TrackingHost"  "True" "True" "In-Process"
AddHostAndHostInstance "AS2Receive"    "True" "True" "Isolated"
AddHostAndHostInstance "TestNewHost32" "True" "False" "In-Process" 
AddHostAndHostInstance "TestNewHost64" "False" "False" "Isolated" 

Write-Host “Script Completed”

Here’s one more library of functions that a co-worker of mine used at a previous client. I didn’t learn about it until after I wrote the above:
https://github.com/lantrix/BTS/blob/master/build/2013/ConfigureBizTalkServer2013EnvHostAndHostInstances.ps1

Update: 05/30/2017 – above script was added to include the line to set the Is32BitFlag so you can make the Host 64-bit or 32-bit using the Powershell extensions.

Update: 05/31/2017 – added the ability to create Isolated hosts by passing a fourth parameter

Update 05/31/2017 – now I also have a script to Update Adapters with Host Names. This was modeled from code here.

First, I found data on Wikipedia. It was formatted into columns, but I had inutition that in the HTML behind the page, the data would not be in columns, that was just formatting, and I was right.

This is the page that I started with: http://en.wikipedia.org/wiki/List_of_cities_in_Texas.

I did right-click “View source” then copied the HTML surrounding the cities to NotePad++.

With a few minutes of editing, I was able to remove the lines of HTML that didn’t look like this:

<code>
<ul>
	<li><a title="Abbott, Texas" href="/wiki/Abbott,_Texas">Abbott</a></li>
</ul>
<ul>
	<li><a title="Abernathy, Texas" href="/wiki/Abernathy,_Texas">Abernathy</a></li>
</ul>
<ul>
	<li><a title="Abilene, Texas" href="/wiki/Abilene,_Texas">Abilene</a></li>
</ul>
<ul>
	<li><a title="Ackerly, Texas" href="/wiki/Ackerly,_Texas">Ackerly</a></li>
</ul>
</code>

Then I pasted this into Notepad++, and used the Find/Replace with the RegEx capture feature.

NotePad++_Find_Replace_Regex_Capture

NOTE: Be sure and check the “Search Mode” type to “Regular Expression” BEFORE cliking “Replace All”.
And of course, when it doesn’t work,  CNTL-Z to undo and try again.

Let me explain this find “RegEx”;

.* title="(.*)"&gt;.*
Then Replace \1 

Results:

Abbott, Texas
Abernathy, Texas
Abilene, Texas
Ackerly, Texas

The parentheses indicate the area to capture (I only need to capture one phrase).
If I wanted to capture the city as \1 and the state as \2 I could do something like this.

.* title="(.*), (.*)"&gt;.*

Then Replace \1 \2

NotePad++_Find_Replace_Regex_Capture2

There’s a nice RegEx Cheat Sheet here: http://www.cheatography.com/davechild/cheat-sheets/regular-expressions

But briefly, the . means any character, and the * means “0 or more”. Then I’m looking for the exact match of the phrase ‘title=”‘ (i.e. the word “title” followed by a double quote). I want to capture the city, which is everything up to the comma. (The theory will not work on city names that might perhaps have commas in them, I doubt there are any of them, but who knows…). Then I want to skip a space and capture the next set of characters up to the next double-quote followed by greater-than-sign than any string of characters.

Then in the “Replace with” textbox, I simply enter \1 (or \1 \2 if capturing city/state separately, then I could put \1/\2 or \1-\2 with my own desired separarator or no separator at all.

Results:

Abbott Texas
Abernathy Texas
Abilene Texas
Ackerly Texas

For whatever reason, I have escaped using any Oracle database in my 30+ year IT Consulting career, and now at my new client, I’m finally using it, at least to run simple queries. As a BizTalk developer, so much depends on the client. I have worked with clients that call MSSQL Stored Procs from BizTalk, and I have maintained those huge stored procs. In other clients that are more SOA based, BizTalk only calls web services, and the web services do all the communication with the SQL back end databases.

Today I was trying to find a “PARTS” table, which was like finding a needle in a haystack of about 1000 different tables. The client uses an ERP system called “IFS” and all I was told it was a special table created by the client outside of ERP system.

SELECT *   FROM dba_tables WHERE table_name LIKE '%PART%' and OWNER not like 'IFS%' and OWNER not like 'SYS%'
ORDER BY TABLE_NAME

I cannot include any client tables in this blog, but here’s an example of searching the Oracle SYS tables:

SELECT *   FROM dba_tables WHERE table_name LIKE '%COL%'  and OWNER not like 'IFS%'
ORDER BY TABLE_NAME

PLSQL_Example

You can take it one level deeper and join tables and columns, then search for either table name of column name:

select * from cols C
inner join dba_tables T on C.Table_Name = T.Table_Name 
where T.table_name like '%PART%'
order by T.Table_Name, C.Column_ID 

PLSQL_Example_Columns

 

When switching from Microsoft’s SQL Studio Manager to “PL/SQL Developer”, there are a few things to get used to.
In Microsoft, you use F5 to execute the SQL, but in PL/SQL you use F8.  If you hit “F5” like you maybe in the habit of doing, you will get the “Explain SQL” screen (which you can then close).

The other biggest difference, is that by default, “PL/SQL Developer” returns about 40 rows, which I believe is based on my screen size and resolution.

There are two icons that sort of look like double-arrows or the fast-forward button on a VCR/DVD/DVR player.
PLSQL_Toolbar1

Click the one on the left to get another 40 rows.  Click the one on the right to retrieve all the data.

There is not “top x” syntax in Oracle.  In Microsoft, you can say “Select Top 25 * from TableName”, but in PL/SQL you either use this feature built into the query tool, or you can manually enter “Select* from TableName where RowNum <= 25”.

 

PL/SQL can be licensed for about $216 per developer (2014 prices) and is sold by “AllAroundAutomations” http://www.allroundautomations.com/plsqldev.html (but I also found a download here – not sure if it just a trial or limited functionality: http://download.cnet.com/PL-SQL-Developer/3000-10254_4-10012692.html  A more expensive tools is “TOAD” (created by Quest and pricing available ($975 and up) from Dell here: https://shop.software.dell.com/682/purl-toad-for-oracle.

 

References:

1. http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle/205746#205746  (for how to enumerate or list Oracle table names)

2, http://www.w3schools.com/sql/sql_top.asp  (for Top 25 vs rownum <= 25)