Re: Error Adding Main Invoice

Josh Hillman ( (no email) )
Tue, 29 Apr 1997 12:08:37 -0400

> When ever I go to create credit card invoices or renewal (I haven't tried
> Invoice)
> I get about half way through the section then it gives me an error
> "SQL Error Adding Main Invoice"
> then
> "Rolling Back Last Invoice"

I had this problem a few times too...

Run the batch process one more time and note how many invoices it went
through before displaying the error.

It turned out that the problem was caused by some character that was in a
database field that Emerald uses while processing invoices. I'm not sure
exactly where it was located, but check for odd-ball characters (such as
those above the numbers on the keyboard) in the following areas:

Emerald Admin / config database:
Service Types (dashes are ok)

Also take a look in the MBRs and Services that are being invoiced and look
for the same types of characters. It's easiest to run a script to look at
the info, such as:

SELECT * FROM MasterAccounts
WHERE CreateDate > "Mar 15 1997" (replace the date with the
approx date of the last
successful invoice run)
SELECT * FROM SubAccounts
WHERE SignDate > "Mar 15 1997" (same as above)

The reason I specified a date above, is because in my case, the problem
popped up after I created a new user with an accounttype that no one else
used, therefor I was able to narrow it down a bit. You don't need to
specify a date, but if you haven't changed any existing accountypes since
the last invoice run, then chances are the problem has something to do with
a user or service added since that last run.

After changing the names or descriptions for whatever was creating the
problem on my end, I re-batched the invoices. Look at the first x invoices
that were created, and VOID any that have partial or no data in them -- x
represents the number of invoices that were starting to be processed before
the original error showed up. After VOIDing these, rerun the batch and it
should only create invoices for those that were just voided. Go through
them and look for any screwy invoices again. If you don't find any,
everything should be alright. If you still find a couple bad invoices,
VOID them again and repeat the process until all new invoices are ok.

Hope it helps!

Josh Hillman