AllScripts Tiger to MS SQL to GoDaddy Hosting

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:

1. 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.
2. 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).
3. If you run your reports against multiple companies, as we do, a macro will be required to be run to switch from company to company between reports.

Since I need high reliability and speed, I decided to investigate the option of using the installed Transoft ODBC drivers and Visual Basic ADO to retrieve data. The Transoft documentation was a bit sparse on this method, and eludes to a sample application bundled with the drivers, but sadly AllScripts saw fit to strip these out of their AllScripts Query installer package.
What I discovered was that it was quite simple to connect to the Micro Focus Cobol Server Express data files. A DSN connection string is created for each company we connect to for AllScripts Query, so the following is a sample of how to retrieve all Insurance Plans for a given company:

Dim Conn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim oFile As Scripting.FileSystemObject
    
    Dim strSql As String
    Dim strConn As String
    
    strSql = "SELECT T1.PLAN_INS_NUM c1 , T1.PLAN_NUMBER c2 , T1.PLAN_NAME c3 , T1.PLAN_ADDRESS1 c4 , T1.PLAN_ADDRESS2 c5 , T1.PLAN_CITY c6 , T1.PLAN_STATE c7 , T1.PLAN_ZIP c8 , T1.PLAN_PAT_TYPE c9 , T1.PLAN_NOTES c10 , T1.PLAN_PHONE1 c11 , T2.P_NAME c12 from P01 T2, INS_PLANS T1 Where T2.P_NUMBER <= T1.PLAN_NUM_KEY order by 1 asc , 2 asc"
                
    strConn = "DSN=Company_" & strCompany & ";uid=MyUid;pwd=MyPass"
    
    Set Conn = New ADODB.Connection
    Conn.Open strConn
    
    Set Rs = New ADODB.Recordset
    Rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer
    Rs.Open strSql, Conn, adOpenForwardOnly, adLockReadOnly
    
    Set oFile = New Scripting.FileSystemObject
    Dim ouFile
    Set ouFile = oFile.OpenTextFile("\\MyServerPath\" & strName & "InsurancePlan.csv", ForWriting, True)
    
    ' Write the header
    ouFile.Write Chr(34) & "insurance_number" & Chr(34) & "," & _
                    Chr(34) & "plan_number" & Chr(34) & "," & _
                    Chr(34) & "name" & Chr(34) & "," & _
                    Chr(34) & "address1" & Chr(34) & "," & _
                    Chr(34) & "address2" & Chr(34) & "," & _
                    Chr(34) & "city" & Chr(34) & "," & _
                    Chr(34) & "state" & Chr(34) & "," & _
                    Chr(34) & "zip" & Chr(34) & "," & _
                    Chr(34) & "default_pt_type" & Chr(34) & "," & _
                    Chr(34) & "notes" & Chr(34) & "," & _
                    Chr(34) & "phone" & Chr(34) & vbCrLf
                    
    Do While Not Rs.EOF
            ouFile.Write Chr(34) & Trim(Rs.Fields.Item(0)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(1)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(2)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(3)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(4)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(5)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(6)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(7)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(8)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(9)) & Chr(34) & "," & _
                            Chr(34) & Trim(Rs.Fields.Item(10)) & Chr(34) & vbCrLf
    Rs.MoveNext
    Loop

Rs.Close
Conn.Close

The company number is set via a required argument to the program I've written, as well as the report type, as I have several sub-routines that determine the type of data to dump.
You can refer to the article mentioned above to review how to import via DTS (Data Transformation Services).
My data is sent to GoDaddy (ya, ya, ya...some day we'll get a real hosting company) via the Microsoft SQL Server Database Publishing Wizard. It is script-able, and this is the way I use it today. You can get it directly from CodePlex, or review GoDaddy's knowledgebase about it.
Running sqlpubwiz.exe help publish will give you a list of command line options. You may use these to form your connection manually, or you can take the lazy route like I did, and run the GUI. This will allow you to create your connections in a permanent manner, easily referenced by aliases in your script. You'll want to remember to include the path to the program in your PATH environment, or adjust your scripts accordingly.
There is a nice article at GoDaddy for the steps required to publish your database, accompanied by nice screenshots. You'll want to review this article to determine what can and can't be uploaded, and database space limitations. One of the things you won't find, is information regarding what I found; even though the data I attempted to send was described as being 156mb in size by running EXEC sp_spaceused on the table I intended to upload, GoDaddy will not allow it to be uploaded, because combined, the two exceed the 200mb limit, and the destination isn't dropped before the source gets uploaded. This required me to make some modifications to my data size, and what I needed to do before I uploaded it.
The biggest hurdle for me was to determine how to form my queries, as I had no intimate knowledge of the database schema. This was quickly remedied by Impromptu. After running a report, go to the Report pull-down menu item, and select Query.

In this window, select the Profile tab, and the SQL option. This will reveal the SQL query used to retrieve your data. You'll notice that if you've got some sophisticated grouping, sorting or calculations happening in your report that they aren't necessarily represented here. That's because there is a limited number of built-in functions in legacy C-ISAM "databases", and Improptu handles much of that, sometimes at a high processing cost to your workstation.

Your best approach is to strip all your grouping, sorting and calculations out of your report, then copy and paste this SQL into Transoft's Win U/SQLi32 to verify you are capturing the data you want. Then you can massage your data a bit in VB, during the DTS import process, or ultimately when you regurgitate your data for other uses, because you will find that there are only a few SQL functions available through Micro Focus Cobol Server Express.

Image via CrunchBase

In my case, that would be through ColdFusion and the excellent amCharts flash charting product. These charts can take much of your dry practice management data and turn it into a more vibrant form of information.

I'm using this in conjunction with the Google Maps API to help our practice managers and physicians better understand where their visitors are coming from, and other demographic information.