John Croson's Blog Home

Image via CrunchBase Reporting in OpenAir has presented our company with some challenges. As such, I was asked to take a look at how we might be able to create an ETL of this data , create our reports and automatically distribute them to our project managers. This is how I did it. First we needed to purchase OpenAir's Automated Backup service. This service will export data in text format or MySQL dumps of any or all tables in their database, zip them up and deposit them anywhere via FTP, FTPS , or SFTP . We set up an FTP server for our repository in the DMZ. Once this bit was done, I needed an installation of MySQL Server and a method to programmatically pull the data from the DMZ, deposit it on a Windows share, unzip and insert the data. Below is a batch file used for this purpose. @ECHO On SETLOCAL ENABLEDELAYEDEXPANSION @SET _LOGFILE="C:\Export\LOGS\OpenAirMysqlImports.txt" @SET _MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe...

Image via Wikipedia Some time ago, I installed the SUA package with the thought I might use it for scripting and other tasks that I find easier to accomplish using GNU -like tools rather than Windows . Recently I needed to run a batch script that programatically checks for the evidence of some data files and injects them into MS Sql via DTS . This script worked fine on another server for years with a few tweaks now and then, and suddenly during my tests on this machine, it was failing. What I found was that my script was using the Windows FIND command, which is also installed by SUA. When I used FIND in my script, it was using the SUA flavor, and not the Windows version. I checked my PATH environment and saw that SUA's path was listed first, then %SystemRoot% , and %SystemRoot%\System32 where Windows FIND is installed. If I were a betting man, I'd have to say that since the SUA tools are listed before Windows, they get processed first. I've not tested this, but really...

Some time ago, I wrote an article about using Cognos Impromptu to export data to CSV from a then Misys, now AllScripts Tiger data source, and use DTS in MS Sql Enterprise Manager to import it for displaying data in ASP pages. I started using this method to export other types of data, in an attempt to create a dashboard for our clients. I quickly found that Impromptu is not well suited or reliable enough for scheduling multiple jobs. The requirements and caveats for using Impromptu in this manner are: Each report must be scheduled so as to not be running when the next is scheduled. Impromptu will not allow the next scheduled report to wait too long, and this may cause the "chain" of reports to fail. The workstation running the reports must have a user logged in with the Cognos Scheduler application running in order to fire the reports off. It cannot run via the Windows Scheduled Tasks (AT for us old-timers). If you run your reports against multiple companies, as we do...

Image via Wikipedia I recently had the need to provide printer admin access on an ERP server to ordinary users. Nothing makes me shudder more than elevating a users access on a machine that holds your entire business operation on it's drives... I was able to come up with a solution using CPAU.exe which I've written about here . The scenario: An active label printer and a backup. When one goes down, the other needs to be put into service, but it needs to have the same name as the active one did. The users then also need to be able to "flip a switch" to put the active one back into service. First, I needed the guts of the switching script, which makes use of Microsoft 's prncnfg.vbs script, stored on all Windows boxes in the %windir%\ system32 directory (Called ChangePrinter.cmd) : @ECHO OFF @SET _ONE=ACTIVEPROD @SET _ONE_DOWN=ACTIVEPROD_146 @SET _TWO=BACKUPPROD @SET _HOST=BAPFSVR03 :: PRINTERS RECEIVE ADDRESSES VIA DHCP :: WHEN NO ERROR CONDITIONS E...

Recently I wrote a post about my search for a TIFF iFilter that would enable me to use VBScript to query a Windows Indexing Services server for file management. I found that since OCR is never always 100% accurate, neither were my attempts at sorting all the inbound EMR faxes we get each day. I did however, find Tesseract , a great product that was originally developed by HP and proprietary, and is now developed by Google and licensed under the Apache License v2, open source . It is one of the most accurate open source OCR engines available. It is quite basic, and in the version you obtain from the project page , it only operates from the command line, and without the libtiff library, will only do it's work on un-compressed TIFFs. More information can be found on the project pages , and Wikipedia . Doing some scouring, I aso found a front-end , and ArchivistaBox, a complete document management system . Image via Wikipedia I'm using it in Windows, so I needed to do one of...