Server stops workflow when workflow gets a conversion error on input from Oracle database.

Conversion error happening on the Input tool. Example "1013-10-10 00:00:00" is not a valid DateTime.

The Oracle database has dates in the right format. The workflows run in Designer with just a conversion error.

Note: this is happening on the Input tool. Other records have valid dates.

Thanks for everyone's input. It turned out to be an issue with the database itself. Workflows are running fine today.

Input

Server

Accepted answers

drsgriderax

Note: this is happening on the Input tool. Workflow runs in Designer on desktop with just a warning.

Same workflow - same input table - stops running on the server

Thanks for your input. It turned out to be an issue with the database itself. Workflows are running fine today.

All comments

apathetichell

that is not a date. what is the value in Oracle which is triggering this --- is the field a date in oracle or a string which you are casting as a date in your query?

KGT

Adding to that, dates in Alteryx start at 1400-01-01 or very close to. So, even though that looks like a date format, and we can see it's not a valid date in terms of reference, it will not be read as a date.

apathetichell

If you are using a version of Oracle which supports that as a date --- you MUST cast convert it to a string in you query (see https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_CHAR-datetime.html) --- or provide a where clause to filter out that values before '1400-01-01'

drsgriderax

The workflow just gives a warning in Designer and we can filter out selected date ranges then. Is there a way to get the server to just give the warning so our workflow can work like it does on Designer?

Server stopping on input tool.

drsgriderax

Note: this is happening on the Input tool. Workflow runs in Designer on desktop with just a warning.

Same workflow - same input table - stops running on the server

Thanks for your input. It turned out to be an issue with the database itself. Workflows are running fine today.

apathetichell

The places I'd look:

1) driver version

2) localization differences

3) permission differences

4) error treatment differences

if this is a large data set ---- use in-db. If you want to fix this problem --- use the filter or convert to string. If your core question isn't about the data and is about why is the query behaving x way locally vs y way on server --- 1 post the query. 2--- look at the listing of the 3 features above. For your initial question ---- convert it to string or filter out those dates in your query. That will fix your error on Server.

I'd recommend seeing if that value is being pulled when you run on Designer. Do you get an error message? There are situations where errors are ignored when running locally but fatal when running on Server --- but there are questions like --- do you want to understand Alteryx internals or do you want to fix this issue? assuming it's the later --- follow my advice. if it's the former --- turn on logging at the odbc level (in odbc 64 --- not in alteryx) on your server and see what's being passed in. compare vs your desktop.

drsgriderax

Thanks for your input. It turned out to be an issue with the database itself. Workflows are running fine today.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703