DATE / DATETIME-Issue after migrating to SQL-Server 2012

dataservices job design
1

Hi,
we got a rather strange issue after migrating to SQL-Server-2012.
When loading date-columns, we are given the following error: “error message for operation : <[Microsoft][ODBC SQL Server Driver]Optional feature not implemented>”

This happens, when we try to

  • load from Flatfile [FFA] (reads column als DATE, YYYYMMDD)
  • via BODS-Query (handles column as DATETIME)
  • to target-table [TTA] (DATE-Column, non-bulk-load)

When we swith to bulk-load, everything seems to work well and the data is loaded quite fine.

If we change the above scenario to the following:

  • load from Flatfile [FFA] (reads column als DATE, YYYYMMDD)
  • via BODS-Query (handles column as DATE)
  • to target-table [TTA] (DATE-Column, non-bulk-load)

We get another error: “Datetime field overflow”
Regardless, whether we bulk-load or do a normal load.

While we only load DATES with no time-portion, I tried
TO_DATE( TO_CHAR( … )) as well as
SUBSTR( … )
but to no avail.
I also tried to alter the target-columns from DATE to DATETIME and to DATETIME2 but nothing seems to work.

We are working with:
BO EDGE 4.0 (14.2.5.000),
SQL-Server 2012 (11.0.2100.60 (X64))
ODBC Driver 13 for SQL Server

Can anybody advise me on this problem?
Any help is greatly appreciated!

Scattered (BOB member since 2011-01-07)

2

:smiley: :smiley: :smiley:
I got it!
Responsible for this mess are language-settings.

The datstore-language-settings in BODS are NOT the settings of the SQL-Server-DB but rather the language-settings of the SQL-Server-LOGIN, which is used with this datastore.

Because we migrated from an old server (ENG) to a new server (GER), the LOGINS created on the new server inherrited the language-setting GER.
But BODS did not care at all.
Only after I altered the SQL-SERVER-LOGIN to default ENG, BODS was willing to load the data without complaining.
This may be due to the settings of other installed components, such as the MS-SQL-Server-Client-Driver, but it works.

Many thanks to the man who knows SQL-Server and its behaviour.

Scattered (BOB member since 2011-01-07)