How to mark large numbers of exported records as un-posted.

MonTel marks records exported as "posted". Posted records cannot be exported again by the MonTel Tools Export32.exe and Monex32.exe. This is a feature designed to protect against the same record being billed to a client multiple times. However, there may be situations where exported records have been lost, and a re-export of a large number is required. This scenario is likely to be one that only affects an installer or support agent, say, after dealing with an external (non-MonTel) system that has failed to import disbursement call records exported from MonTel, and where these export data files have been lost.

Here's how to un-post the records (on an SQL Server or MSDE), so that they can be re-exported.

1) Copy the osql.exe file from the 'C:\MSSQL7\Binn' folder (or the equivalent) on the MSDE or SQL Server to a location of the computer on which you want to run this script. I have used C:\PUBLIC. You may want to add this folder to the PATH environment variable on the computer, so you can run it without needing to specify the path.

2) Create a script (text file) called UNPOST.SQL and place it on a folder on the local computer. (e.g. C:\TEST\UNPOST.SQL). Use notepad, but what that it doesn't add a spurious ".txt" extension. Enter that date range in the 'YEAR-MO-DY' format as shown in the four places below. It is probably easiest to cut, paste and modify this example.

UPDATE CallRecord

SET Posted = 0

WHERE DateOfEntry >= '2002-11-01'

AND DateOfEntry <= '2002-11-06';

GO

UPDATE DisburseRecord

SET Posted = 0

WHERE DateOfEntry >= '2002-11-01'

AND DateOfEntry <= '2002-11-06';

GO

3) Note that if you want to use the Start time of a Phone call record modify the FIRST (ONLY) where statement above to have 'StartTime >=' and 'StartTime <=' rather than DateOfEntry.

4) Create a batch file with the values shown bellow in {} brackets changed to the appropriate values from the PABX.INI file (without the curly braces of course), and where {SAPassword} is the password to the 'sa' account. Or if only Windows authentication mode is being used, then use -U{Admin account} -P{System Admin account Password}.

C:\PUBLIC\osql.exe -S{DatabaseServerName} -Usa -P{SAPassword} -d{DatabaseName} -iC:\test\unpost.sql -r1 -m-1
pause

5) Run the batch file. You should see a result (depending on the set up) like:

C:\Work\Montel\BAT>C:\PUBLIC\osql.exe -SDM3 -Usa -PTep23qui -dMONTELDB -iC:\Work\
Montel\SRC\CLIENT\DB\unpost.sql -r1 -m-1
1> 2> 3> 4> 5> (3 rows affected)
1> 2>
C:\Work\Montel\BAT>pause
Press any key to continue . . .

Note: This activity should only be undertaken by support staff, or administrators who know what they are doing (and why they are doing it!).

Also, once records are marked as unposted they will be exported again by monexsv at the next scheduled opportunity. You can speed this up by un-pausing monexesv.