When scheduled BAT-files cascades

Server was acting slowly. What have happened? When I opened Task Manager. There were thousands of cmd-programs open. I noticed there were some scheduled BAT files without exit row to finish them. 

It was too slow to kill them one by one. However, there is a command to kill them all with one command. Firstly, you must open cmd with admin privileges! Then write:

taskkill /F /IM cmd.exe

This command will kill your current command prompt too.

Merging XML files togheter with XSL using Saxon-B

Sometimes source system produces thousands of source files. If you open and process each of them individually, it takes plenty of time. Sometimes it is must faster to merge files together and process larger file.

 Here are example. Source system produces tons of XML-files about comics. Single XML file looks like this:

<?xml version="1.0" encoding="UTF-8"?>

<comics>
    <comic>
        <name>Moomin</name>
        <authors>
           <author>Tove Jansson</author>
           <author>Lars Jansson</author>
        </authors>
        <started>19470101</started>
        <ended>19750101</ended>
        <publisher>Associated Newspapers</publisher>
    </comic>
</comics>

File merging XSL looks like this.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output name="XML-format" method="xml" indent="yes" encoding="utf-8"/>

    <!-- Patch size. We don't want put all files togheter. We want to have chunks of controllable size. In this example size 5 is okay. --><xsl:param name="patchSize" select="5"/>

    <!-- Source path variable with filemask. -->
    <xsl:variable name="sourceFiles" select="collection('file:///C:/comic/input/?select=comic*.xml')"/>

    <xsl:template match="/">

        <!-- group-by with patch size -->
        <xsl:for-each-group select="$sourceFiles/comics" group-by="(position() - 1) idiv $patchSize">
           <xsl:variable name="patchID" select="position()"/
              <xsl:result-document format="XML-format" href="Comics_{$patchID}.xml"> <!-- Output file name-->
                <comics>
                   <xsl:for-each select="current-group()">                                <xsl:sequence select="*"/>
                   </xsl:for-each>
                </comics>
              </xsl:result-document>
        </xsl:for-each-group>

    </xsl:template>
</xsl:stylesheet>

We will save xsl file as: C:/comic/merge_comic_files.xsl 

Input folder will be: C:/comic/input

Output folder will be: c:/comic/output/

Let's open command prompt. Give this kind of command:

> java -jar "c:/saxonb9-1-0-8j/saxon9.jar" -s:"c:/comic/input/" -o:"c:/comic/output/" -xsl:"c:/comic/merge_comic_files.xsl"

Command will use Java JRE with -jar parameter. Second parameter is location of Saxon-B processor.
-s parameter is source, -o is parameter for output and -xsl is path of xsl file.

You can download example files here.

Saxon-B Free XSLT and XQuery Processor

 I got assignment to modify XML files to CSV and HTML. With freeware of cource. Actually I was told to make processing with Saxon-B XSLT and XQuery Processor.

You can load Saxon from here.

Download file:

saxonb9-1-0-8j.zip

You will need Java runtime environment(JRE) or -SDK to Run Saxon-B. Runs with pretty old Java but does not have problems with later ones either.

You can download Java JRE from here.

I will make some examples about use of Saxon-B parser with future posts.

Testing connection string with Powershell

Some systems wants connection string from you. Whent you give connection string to them they just tell you "ERROR!" or "No connection!". It is not helpful. What went wrong?

Sometimes I test connection strings with powershell. Write this kind of script with notepad or similar editor:

$connectionString = 'Data Source=123.123.123.123,1234;database=NameOfYourDatabase;User ID=migtyuser;Password=secret123!' -f $ServerName,$DatabaseName,$userName,$password

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString

$sqlConnection.Open()

$sqlConnection.Close()

Save it as connectiontest.ps1.

123.123.123.123 is IP number of your server and 1234 is the port number (not mandatory). Save your file as Powershell file. 

Then start Windows powershell as admin user and give next kind of commands:

>set-executionpolicy remotesigned

 - Answer Y-when system asks permission. 

Okay. Now we can use connection tester. Give powershell command:

> ./connectiontest.ps1 

If everything is just ok. Nothing happens. Hurray! But if there is something wrong with your connection string Powershell will give nicely detailed error message. It is much better error message than with most other systems.

SAS to SQL. How to do nway with SQL

Recently I have been translating SAS-code to SQL. Old SAS was pretty new for me. Some things was pretty easy to understand. Some things about SAS code are somewhat cryptic. For example SAS will make worktable "prices_per_productype" with this command:

proc summary nway missing data = invoice;
class id_corporation id_productgroup ;
var price tax;
output out=prices_per_productype (drop=_type_ _freq_) sum= ;
run;

When I translate it to SQL it looks like this:

SELECT
id_corporation, 
id_productgroup,
sum(price) as price, 
sum(tax) as tax
FROM invoice
GROUP BY id_corporation, id_productgroup 

If data looks like this before processing:

id_corporation	id_ productgroup	price	tax
12	6	10	10
12	34	350	35
12	34	400	40
12	34	10	1
14	17	900	90
14	48	50	5

 

 Data will look like this after processing (in both cases):

id_corporation	id_ productgroup	price	tax
12	6	10	10
12	34	760	76
14	17	900	90
14	48	50	5

                            

How to get person's birthdate from Finnish social security number with SQL-query

 Finnish social security number is datemask ddmmYYxAAbc

• dd = date with two numbers.
• mm = month with two numbers.
• YY = year with two numbers.
• x = "delimeter" character. It can be '+','-' or 'A'. It presents century.

• '+' = 1800
• '-' = 1900
• 'A' = 2000
• AAbc = varying numbers and caharacters for id purposes including cheksum. We are not interested in about it this time.

SQL-query:

DECLARE @sSecurityId char(11);

SET @sSecurityId = N'221177-123Z';  

select CONVERT(date, CASE SUBSTRING(@sSecurityId, 7, 1)
    WHEN '-' THEN '19'
    WHEN 'A' THEN '20'
    WHEN '+' THEN '18' 
END + SUBSTRING(@sSecurityId, 5, 2) + SUBSTRING(@sSecurityId, 3, 2) + SUBSTRING(@sSecurityId, 1, 2)) as birthdate

Result: 1977-22-11

Keywords for Finnish developers:

Kuinka muuttaa suomalainen sosiaaliturvatunnus, eli sotu, päivämääräksi tai päiväksi SQL-haun avulla.

ODBC connection error

This hint is maybe useful to you if your ODBC connection is configured with server name and not with IP number.

One day there was an error message in ETL log.

Connection failed:

SQLState: ’01000’
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOPen (Connect())
Connection failed:
SQLState: ’08001’
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Specifed SQL server not found.

 ETL was using ODBC drivers. Connections were established with ODBC Data Source Administrator.

When I tried configurations of faulty connections with ODBC Data Source Administrator I got same errors as above again.

 First I tried hints from Bobcares site with no luck.

Then I used command line and PING-commad (PING [server_name]). Ping went through just fine.

I tried to reconfigure connection with ODBC Data Source Administrator again. I made new connection actually. I copied settings from faulty ODBC setting but replaced server name with IP-number, which I got response from PING command. This time ODBC Data Source Administrator give SUCCESS from connection test! I renamed faulty connection with “_old” ending and renamed new connection with previous name.

ETL-process went through with any errors.

What was wrong? I don’t know. There was other ODBC connections to different databases with the same server and they were working just fine with server name.

But hey! Connection works!