Follow @bgelfius

I needed a script that would recursively traverse a directory structure looking for a manifest file. The manifest file contained filenames of SQL code that needed to be executed. The manifest file containted the filenames in the order that they needed to process.

param([string]$rootdir, [string]$server, [string]$dbname)
cd \$rootdir
# get only directories
$dirs= Get-ChildItem -R | where {$_.PsIsContainer -eq $true}
$currdir = pwd
Foreach ($dir in $dirs) {
$cp = (Convert-Path $dir.PSPath)
"Processing directory [$cp]"
cd $cp
if (Test-Path .\manifest.txt) {
Select-String -Pattern "^load[1|2]" -Path .\manifest.txt | Foreach-Object {
$f = $_.Line;
"$f"
}
}
}


Follow @bgelfius

During a data migration a had the problem of an INSERT failing because of a duplication in concatenated key.   I found one solution to my problem using kind of a “look ahead/behind” feature in MS SQL.  Using  the CTE “Common Table Expressions” feature, I was able to build an in memory version of my data that I could use to check the previous record.

The first step is to create the CTE function.


;WITH DemoCTE
AS (
SELECT ROW_NUMBER() OVER (ORDER BY Key, SequenceNbr) as rownum,
Key,
SequenceNbr,
Datavalue
FROM [DB].dbo.Demodata
)


The code defines a function called DemoCTE that can be used in a FROM clause later.  The ROW_NUMBER OVER bit generates a sequence number for every record retrieved.  The ; before the WITH is required if the CTE  is not the first thing in the script.

The next piece is to create a query that will use the CTE function. In my problem the key is actually the key value + the sequence number. The sequence number was the part of the key that was duplicated in the table.


SELECT cRow.key,
cRow.SequenceNbr,
cRow.Datavalue
FROM DemoCTE cRow
INNER JOIN DemoCTE pRow
on cRow.rownum = prow.rownum - 1
and cRow.key = prow.key
and crow.SequenceNbr <> prow.SequenceNbr
ORDER By key, SequenceNbr


The code joins two in memory tables and returns only those rows where the sequence number of the current row doesn’t match the sequence number of the previous row.

My data had several million records and this code ran in a matter of seconds. It was much faster than creating a CURSOR and sweeping through two copies of the same table.

Follow @bgelfius

I was working on a problem where I needed to be able to determine whether or not a string had any non printable characters embedded in it.  The route to the solution I chose was to create a regular expression that would look for all the characters and digits.  The regular expression I came up with looked something like this:


string pattern = @"[a-zA-Z0-9]";


This worked reasonable well until I determined the data might have carriage returns and line feeds. I also determined that I also wanted to allow all the characters accessible as shifted characters. I modified it to allow those.


string pattern = @"[\x20-\x7E|\A|\D]";


This works well until I found some foreign language characters sometimes appeared in the data. I found information about some extended codes that are supported by C# that allow checking of UNICODE values. I modified the code to look like this:


string pattern = "[\p{L}\p{M}\p{N}\p{Z}\p{P}\r\n]";


This pattern seems to be working and allowing printable UNICODE characters in our data to properly evaluated.

Follow @bgelfius

I needed a tool to verify a date function I had created in MSSQL.  The dates were all in a YYYYDDD format.  The Windows program that I wrote allowed me to select a calendar date and it generates the Julian format or I can enter a Julian date and get the calendar date.  I also added the ability to get a “days between” value.  Select the link on this page to install the program.  DateFunctions