We recently upgraded our enviornment to SQL Server2008 and noticed dataflows that have Data Transfer (DT) with date fields crashing with below error.
ODBC data source error message for operation : <[Microsoft][SQL Server Native Client 10.0]Numeric value out of range>.
This is causing by the a new data type introduced in SQL server for date field called Datetime2. BODI is creating the DT with this new data type and our target tables are defined with datetime. The new data type is longer and hence we are getting Numeric value out of Range error.
Even if you force the data transform (DT) to use datetime datatype by adding Generated SQL it is not using the datetime data type. I tried changing the target tables datatype to datetime2, but it is not working.
are you using imported table from Datastore as Transfer table or DI is creating the table at run time ?
a bug was fixed in later fix pack related to Template table, where Datetime Datatype was not handled properly. In your case if the table is created at run time, may be its the same issue
try using a Imported Table from the Datastore as Transfer Table or
you can try the latest 12.2 (SP/FP) and see if the problem is fixed or
I can verify this, can you give the steps to reproduce the issue ? like Source table structure with datatype, target table structure with datatype, and how the DF looks like ?
source ->DTT->Target
are you getting the same error message ? what is the Datastore database type and version ? is it Microsoft SQL Server and Version is set to 2008 ? or you are using ODBC datastore ?
FYI - These datatype-related SQL 2008 problems seem to still be happening under the latest DS (12.2.2.1).
This has caused us a lot of pain in our SQL 2008 upgrade process.
Moreover, even leaving the database in SQL 2005 compatibility mode (and leaving the datastore set to SQL 2005, vs. SQL 2008) still causes issues for us. It’s like DS is interrogating the server to get its version (rather than the database-level setting) and deciding to act in a certain way regardless of what the datastore says.
yes, for SQL Server 2008
DS gets the version from the Server and uses that
I don’t think you should be changing the tables datatype, SQL Server 2008 is supported from 12.2 and if there is a bug it should be fixed, you should try these kind of extreme workarounds only in situation if there is no way to get the issue fixed, if you have filed a case, please let me know the incident number, attach the ATL to the case, I can take a look if its still a bug, a bug is currently being addresed in 12.2.2.2 related to SQL Server 2008 DATE Datatype
I would argue that this is undesirable behavior. The whole point of the “Database compatibility mode” in SQL Server is that you can override what the server says the version is, and force your database to operate as an older version. So you can keep a SQL 2000 database on a SQL 2008 server machine. DS should be checking the database-level version (not the server version) – or just obeying what the Designer specified in the datastore – rather than trying to be smart about it.
I can file a bug/enhancement for evaluation, to check the SQL Server engine’s compatible mode along with the server version and process accordingly
does running SQL Server 2008 in 2005 mode disables the new features that are availabe in 2008 ? will it still allow you to create the column with new datatypes introduced in 2008 ?
Hmmm. This is very surprising. Even with the compatibility mode set to 2005 on a database, these commands do still work.
create table xtest
( id int primary key,
mydate datetime2)
go
SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS
'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS
'datetimeoffset';
go
I would have expected both of them to error out. So maybe checking the compat mode isn’t enough – maybe DI should just instead look at the datastore configuration to rely on the db type.
The SAP message # is 567842/2010. Please note that this ticket was not originally opened with the DATETIME issue however the technician recommend the switch to DATETIME2 would fix the problem.
I have opened a new case specifically for this issue 0000575427 2010)
I don’t think its failing with this error for all the rows, do you know the value for which its failing ?
or you can set the overflow file for the Target table and write the bad rows to it, from that you can get the rows that are failing, post the datetime value that is failing
usign the ATL that is attached to the case I am getting a different error
error message for operation : <[Microsoft][SQL Server Native Client 10.0] Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding
looks like something is broken while binding DATETIME column, I am debugging this
if you are looking for a workaround then try this instead of changing the Datatype of column in the table
the problem is with the scale value that is stored in AL_COLUMN table when the table is imported, for the columns with Datatype DATETIME, the value is 9, it should be 3, the workaround in your case would be to reimport the table and see if the value gets update to 3
I don’t think changing the version to 2005 in Datastore will work since the server version is queried from the server after connecting to the database and native driver for sql server 2008 is loaded
select a.DATASTORE,a.TABLE_NAME,a.COLUMN_NAME
from ALVW_COLUMNINFO a
where COLUMN_DATATYPE like 'date%'
and COLUMN_SCALE=9
order by 1,2,3
Sure enough, there are some columns set up as you suggested. And reimporting them does seem to reset the column_scale to a proper value.
However, the vast majority of the 350 items in my results, are columns in Data Transfer tables, or Template tables – both of which are generally not re-importable; they have schemas defined by DS…
yes, template table will be an issue, that’s a bug, let me work on that too
checked the template table for SQL Server 2008, if you are using drop and create then there shouldn’t be an issue, for DATETIME datatype DI will create column in the database as DATETIME2
are you running into issues with template tables using column datatype as DATETIME ?
(BOB member since 2004-01-30)
(BOB member since 2004-01-30)