We’ve recently migrated from DI 11.5 to DS 4.2 SP1.
Server: Windows Server 2008 R2 Standard
Data Services 4.2 SP1 (14.2.1.224)
Target: MS SQL Server 2008 SP1
MS SQL Server 2012 Native Client installed on the server.
Not in all but in many jobs which has a datetime column, we’re getting the following error:
ODBC data source <SERVER_NAME> error message for operation :
<[Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.>.
I’ve found two possible resolutions so far:
Replace sysdate() with concat_date_time(sysdate(), systime()); or
datetime → char → datetime
These aren’t very feasible solutions as we’ve more than 300 jobs.
I think something changed in SQL Server 12 Native Client for DATETIME2 datatype or on the server itself, try inserting the same value from Management Studio
let me check if something is fixed in Patch 3 or going to be fixed in Patch 4 or SP2
DBA didn’t allow us to install\use Query Analyzer. In any case, SAP folks asked us to NOT to use SQL Server Native Client but instead only install SQL Server Client. This indeed resolved the datetime issue! However, doing so lead to something else – any job which has a template table as its target throws the following error, “ODBC data source error message for operation : <[Microsoft][ODBC SQL Server Driver]Optional feature not implemented>.”
We had lots of problems with DATETIME when we upgraded our Database servers from SQL Server 2005 to 2008. Possibly related?
If we migrate a dataflow from one system to another via the export/import functionality and that dataflow also has a table defined in it with a DATETIME column then the migrated dataflow fails with an INT overflow. It is resolved by re-importing the table definition through the data source. I assume the issue is related to DS wanting to use DATETIME2 and our tables still using DATETIME.
Thanks so much DanDensley. Even SAP folks weren’t able to provide a working solution. Reimporting all the tables in DS seems to be working. Will update once testing of all the jobs is complete.
Glad to have helped. Just remember you have to do it each time you import the dataflow definitions from another system/ATL file. Annoying but it works.
the reimport work around was needed in earlier version of 12.2 and this was subsequently fixed in 12.2 itself, since you are on DS4.2 SP1 there is no need for you to do a reimport
since you have upgraded your 2005 to 2008, did you create a new DataStore configuration with Database version set to 2008 ? also, you will need 2008 Client on the Job Server and Designer machine
We created new datastore configuration for all MS SQL datastores and set it to 2008. Also, we have SQL Server 2008 Client which automatically installs Native Client on the server.
I’m indeed using DS 4.2 SP1 but only re-importing all the table definitions worked. We’ve tested all the jobs in the repository and we’re not facing any connectivity issue now.
(BOB member since 2012-03-20)
(BOB member since 2009-05-12)