Question

Allowed Data Types in External Table

Laserfiche Version 10

Updated December 3, 2017

asked on April 27, 2017

What are all of the allowed data types that can be in a SQL table that can be registered as an external table in Laserfiche? Customer is trying to register a table that has 2 columns where the date type is date. He gets the following error when he tries to register the table in the Admin Console:

“The SQL data type of a column in the external table is not supported. [9449]”

Error Code: 9449

Error Message: The SQL data type of a column in the external table is not supported. [9449]

------------ Technical Details ------------

LFSO:

Call Stack: (Exception)

LFSession::ProcessResponse

LFSession::SendRequest (POST /+LF/query/externaldb)

CLFQueryExternalDB::Create

Additional Details:

HRESULT: 0xc00424e9 (LFSession::ProcessResponse, LFSession.cpp:3894)

(LFSO/10.1.0.114)

LFAdmin.dll (10.1.0.151):

Call Stack: (Current)

CExternalTable1PropPage::OnApply

Call History:

CExternalTable1PropPage::OnInitDialog

CExternalTable1PropPage::OnApply

If he removes those two columns then he can successfully register it.

The Admin Guide says:

Note: Certain SQL and Oracle data types are not supported by external tables. The data types xml and sql_variant are not supported, and will be ignored by Laserfiche when the table is registered. Tables containing the data types "interval year to month," "interval day to second," "timestamp with time zone," and"timestamp with local time zone" cannot be registered with Laserfiche. Binary data will not be dropped, but will be displayed as [data] and cannot be assigned to Laserfiche dynamic fields.

However, the data types in these columns are simple date types (see attached). I can replicate this as well in my environment.

Thanks!

haines sql table.png (47.48 KB)

| Download

1 0

Replies

replied on April 27, 2017

Hi Molly

How do you populate your fields "hire_date" and "termination_date"?

0 0

replied on April 28, 2017

The customer says they are using an INSERT function from their financial software database to their local SQL database.

0 0

replied on April 28, 2017

Ok. So it looks like a simple copy/past from software database to SQL database, right?

We need to be sure that Software database / hire_date and termination_date are a date's type.

Another way (not a good one in my mind but it can help) change your type to nvarchar and see the result.

0 0

replied on May 10, 2017

Yes, the Software database hire_date and termination_date columns are also set as date types. He can change the data type to datetime and he can register the table successfully.
I do not see any indication from Laserfiche that it does not support the standard basic “date” type. Especially since the data being imported does not include time information, just the date.

Any information from Laserfiche would be appreciated.

0 0

replied on May 4, 2017

This is a good question Molly. I don't see anywhere on the Laserfiche guides that mentions what types are supported.

0 0

replied on May 10, 2017

Hi Molly,

Funny timing--I'm working on a Forms+Workflow process right now that requires referencing an external table, and I just ran into the same error: "The SQL data type of a column in the external table is not supported. [9449]".

After reading this thread, I tried dropping the table and recreating it with the former [date] type columns typed as [datetime]. This solved my problem!

So, if possible, try dropping/recreating your table with the current [date] columns typed as [datetime] instead.

I hope this works for you!

Rob

0 0

replied on December 3, 2017

Thanks for the post guys, I have run into the same issue in 10.2.1 with a lookup to SQL and had to use "datetime" to resolve it.

0 0

You are not allowed to follow up in this post.

Question

Question

Allowed Data Types in External Table

Replies

Sign in to reply to this post.