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.

oe_line_error.JPG (19.28 KiB) Viewed 10489 times

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'

or

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

DiscoveredAttributes.JPG (90.2 KiB) Viewed 10474 times

When viewing the BO Attributes

complete.JPG (42.1 KiB) Viewed 10474 times

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?