War stories

/*******************************************
* Unintended Consequences
********************************************/
So, the site (CTS) had too many subscriptions that were going to expire. For some reason, they had thousands of subscriptions that would expire at the end of a month, say Oct 31.  John tried to make the argument that they should just build them so that don't expire (DateTime.MaxValue), but the site felt this wouldn't satisfy their contract with the state.  In any case, we were tasked with extending the subscriptions that were going to expire.  So we isolated the ones we wanted update, and then extended their toDate.  One detail that slipped through the cracks is that subscription From and To Dates used  to be stored on the Booking.  Then, Toronto introduced the DateIntervals table, which allowed for much more sophisticated activation logic, as well as multiple suspensions.  Unfortunately, our interface still pulled its data from the Booking record, so when we extended the subscriptions, we only updated the Booking's associated DateIntervals record.  So the user had no way of seeing an accurate end date for the subscription because the DateIntervals ToDate was not synched with the Booking.ToDate.
***********************************************
Another one...We implemented a data exchange for CTS to electronically exchange information with providers.  Providers pull trips down from reports and import into there system.  Some of those trips are activated trips. After the providers perform the trips, they electronically submit a file back with completion data.  Well, by design, when editing a subscription in MT, we delete anything in the scheduling window, and regenerate with the new changes (Partially this is because, what if the user edits the days of the subscription- at the time we felt it easier to delete and regenerate).  Well, those booking ids that had been sent to providers, were no longer on the system, because they had been deleted because of edits to the subscription. We ended up adding in a property that allowed sites to configure whether they want the editing of subscription to delete or cancel modify (a new cancel status we invented).
********************************************************

Another CTS one...kept getting complaints of performance issues....I was there on site- whenever a batch was running- scheduling server calls would queue up- the batch call was blocking all other calls...so as soon as the batch finished, then you'd see a deluge of the queued calls trying execute. The Toronto guys swore that the batch was not a blocking operation, but I done saw it with my own eyes. So ended up adding in some logging that demonstrated that batching was blocking....upon more investigation...it was blocking because the calls were all coming from the same user (because MT used one account for all scheduling server calls)...yay

/*****************************************************
*  Making an ass out of u and me
*****************************************************/

IIS Log

To determine where your IIS log files are stored, please perform the following steps on your server:

1. Go to Start -> Control Panel -> Administrative Tools
2. Run Internet Information Services (IIS).
3. Find your Web site under the tree on the left.
4. Right-click on it and choose Properties.
5. On the Web site tab, you will see an option near the bottom that says "Active Log Format." Click on the Properties button.
6. At the bottom of the General Properties tab, you will see a box that contains the log file directory and the log file name. The full log path is comprised of the log file directory plus the first part of the log file name.

For example, if the dialog box displayed the following values:

• Log file directory: C:\Windows\System32\LogFiles
• Log file name: W3SVC1\exyymmdd.log

Then your full log path to put into SmarterStats would be:
C:\Windows\System32\LogFiles\W3SVC1

I downloaded an IIS log analyzer from here:

http://www.splunk.com

username: stefanTrapeze

password: Trap3z3iub

Troubleshooting report viewer launcher Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message

In this case, notice the error "

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

It took me a while to realize/remember that the MT application I was running locally uses the credentials in web.config for the report viewer:

    <add key="ReportServerAccessAccountUsername" value="ReportServiceAccount"/>

    <add key="ReportServerAccessAccountPassword" value="Tr@p3z3"/>

    <add key="ReportServerAccessAccountDomain" value="AZ-Dev5"/>

Creating a PDF from Trapeze PASS reports

Question: in Pass Reports, is there a way to export to PDF that you're aware of?

Cary McQuitty/Trapsoft Inc: I have always installed primo pdf then use it as the printer

Disabling Service Shell Security

SQL trick with JOIN vs APPLY (JOINING to get the top 1)

So, assume this:

select * from meddenials

DenialId DenialTypeId DenialCode DenialDescrip
1                       1 1420         Not Medically Necessary
2                       1 1500         Other transportation available
3                       1 1600         No Eligibility Coverage on date of service
4                       2 100                 Missing Signature
5                       2 200                 Incomplete Information
6                       2 300                 Mileage over estimate threshold

And


select * from meddenialtypes



DenialTypeId DenialTypeDescrip
1                 Trip Denial
2                 Claim Denial


And

select * from medreimbursementdenials where bookingid = 44479


BookingId DenialTypeId DenialId
44479                       1 1
44479                       2 5


So, for an Encounter file, we wanted to get the top denial reason, so I tried something like this:



SELECT DenialReason.*,md.*
FROM Booking b
LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid )
AS DenialReason
LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId
where b.bookingid = 44479

but that causes an error:

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'where'.

So I tried moving the where clause out of the "sub table":

SELECT DenialReason.*,md.* 

FROM Booking b

LEFT OUTER JOIN ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials  ) 

AS DenialReason on DenialReason.BookingId = b.BookingId

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

Which is ok syntactically, but doesn't return the results the way we want:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

NULL NULL NULL NULL NULL NULL

So, I googled around and found this page:

http://stackoverflow.com/questions/2033699/top-1-with-a-left-join

which introduced a key word I wasn't familiar with "OUTER APPLY"

SELECT DenialReason.*,md.* 

FROM Booking b

OUTER APPLY ( SELECT TOP 1 DenialId, BookingId FROM MedReimbursementDenials where bookingid = b.bookingid ) 

AS DenialReason 

LEFT OUTER JOIN MedDenials md on md.DenialId = DenialReason.DenialId 

where b.bookingid = 44479

does the trick:

DenialId BookingId DenialId DenialTypeId DenialCode DenialDescrip

1               44479 1                       1 1420 Not Medically Necessary

Troubleshooting MT Portal -> Sched Server communication

To Troubleshoot Portal -> Portal Web Service -> Service Shell -> Sched Server communication.

Start the service shell and browse to

http://localhost:<portNumber>/context

Examine these properties (there are example values provided):

PASS/PassInfoServer/SchedServ/Config/IP AZ-DEV4
PASS/PassInfoServer/SchedServ/Config/Port 11002


PASS/Trapeze4/Schedule Server/Host Address AZ-DEV4
PASS/Trapeze4/Schedule Server/Host Port 11002

NOTE: these are stored in the CorePropValues


Profile/Network/PrimaryServerHost localhost
Profile/Network/PrimaryServerPort (port of Service Shell) 8081

So, we had a working configuration here of one of our customer sites running locally.  I saved off the PropValues table of that working configuration in another DB- got a fresh copy of the data from the customer site and restored locally- and then brought back the original PropValues table thinking that I'd be able to turn it on and go.  I found that there must be some config values not stored in the PropValues table. Not sure where they are stored.  But prior to restoring take a look at these values!

//doesn't come in through PropValues

PASS/PassInfoServer/SchedServ/Config/IP
PASS/Trapeze4/Schedule Server/Host Address

PASS/Trapeze4/Schedule Server/Host Port


Also, check this as well:
<driveLetter>:\Data\tpnet.ini

ALSO: if the portal has an error message saying that it can't connect to the service shell, double check the trace switches on the shell, Core -> Security -> and verify that authentication is turned off.