Error while updating external MS_SQL DB

rbross

Hello,

Trying to do a simple update of two fields in a order lines table of a MS-SQL DB.
Update Completed='Y' and date_last_modified=CURRENT_TIMESTAMP
Updating the order header records works fine.

I get the following error.

It is finding the order line record fine. the completed field is a text field and the date field is a Timestamp.

Any idea's????

Thank you!

tford

I could isolate which attribute it causing the issue. Also look in the logs for clues.

rbross

I looked at the log while the process ran and also the log of the "View" button for AwareIM server.
I will look at the log.txt file and see if I can find a reference to the attribute. thx

"I could isolate which attribute it causing the issue. " Is there a typo in there?
Are you saying you can isolate the attribute?

tford

oops -- "I would isolate ..."

meaning narrow it down to ONE attribute first to be 100% sure which one is the issue.

rbross

I looked at the test.log and AwareIM is updating the attributes. What I Found when I look at the AwareIM server Output view is this:
Exception com.bas.basserver.persistence.PersistenceException trying to commit transaction Error persisting business object oe_line2 Error converting data type nvarchar to decimal.
com.bas.basserver.bsmanager.U: Error persisting business object oe_line2 Error converting data type nvarchar to decimal.

Looks like it is failing when it is committing the transaction, not sure where to go from here?

tford

Is the rule on a BO or in a process?

Please post the rule in it's entirely when asking for help with an issue like this (instead of just an excerpt from the rule).

rbross

The rule is on a process.
Parameter passed is oe_hdr

FIND oe_line WHERE oe_line.order_no=oe_hdr.order_no
oe_line.complete='Y'
oe_line.date_last_modified=CURRENT_TIMESTAMP

tford

What happened when you eliminated one of these rules at a time?

oe_line.complete='Y'
oe_line.date_last_modified=CURRENT_TIMESTAMP

rbross

I did try that, but no matter which update I left in I still got the error.
FIND oe_line WHERE oe_line.order_no=oe_hdr.order_no
oe_line.complete='Y'

FIND oe_line WHERE oe_line.order_no=oe_hdr.order_no
oe_line.date_last_modified=CURRENT_TIMESTAMP

Still gave the error.
If I do just the FIND it works fine.

RocketRod

Are you sure your completed attribute on the line is defined as Plain Text and not Number?

Cheers Rod

rbross

This is how AwareIM Discovered the Attribute for "Complete"
I do not have access to the MS-SQL DB to look at it in SQL Manager.
When adding the BO

When viewing the BO Attributes

If I change the Type to a number will it affect the physical SQL table?
I think it will wont it?

RocketRod

Then as Tom suggested check all your rules on both the line item and header tables as the header will be most likely updated as well. Also check initial attribute values in case one of them is incorrect. Somewhere you must be trying to change a text value into a number attribute.

Also if you turn on logging and log to viewer you should see the offending rule.

Cheers Rod

rbross

order header updates fine.
I did turn on logging and I see the update to each of the attributes and no errors show.
In one of my previous posts I showed how it looks like there is an issue when it goes to commit the transaction.

RocketRod

Are you trying to put a null then into one of your number attributes instead of zero?

Cheers Rod

rbross

No, I am just trying to update two fields.
Complete with 'Y' and date_last_modified with CURRENT_TIMESTAMP
If I try to just update the complete field I still get the error.

This is very weird?

RocketRod

No no. Do not just look at these two fields. The full record is being committed so you must check all fields. Rules you have in BO may not be correct. Go through the fields one by one and make sure you know what its value is.

Cheers Rod

rbross

OK, I will look at that.
Since it is a BO that points to an external table I do not have any Rules on the BO.

RocketRod

Oh. If you have changed an attribute on the external table and not refreshed it within AwareIM then they may be your cause. Changes in external tables need to be refreshed. Check that what it shows in AwareIM actually matches your table definition!

Cheers Rod

rbross

I have not changed anything on the external table and nothing on the external table has been changed.
But if something was changed, how do you refresh it, Click Discover Attributes under Persistence/Settings for the BO?

RocketRod

Yes.

Cheers Rod