Datetime2(7) issue in SQl server 2008

[somu_june]

Hi All,

I'm trying to insert data in to SQL server 2008 table. The data type is Datetime2(7) and I tried all the combinations like using functions like below in transformer


StringToTimestamp(TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss"):'.000000',"%yyyy-%mm-%dd %hh:%nn:%ss.6")

In DRS stage I made SQL type as Timestamp and extended as "microseconds" and in length I mentioned as 26 and scale as 6

I'm getting warning's as

Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ.
Metadata mismatch for column 'CRETD_TP': PRECISION Expected = 20, Actual = 27
Metadata mismatch for column 'CRETD_TP': SCALE Expected = 3, Actual = 0


I also tried

StringToTimestamp(TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss"):'.000',"%yyyy-%mm-%dd %hh:%nn:%ss.3")

In DRS stage I made SQL type as Timestamp and extended as "microseconds" and in length I mentioned as 23 and scale as 3

I'm getting warning's as

Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ.
Metadata mismatch for column 'CRETD_TP': PRECISION Expected = 20, Actual = 27
Metadata mismatch for column 'CRETD_TP': SCALE Expected = 3, Actual = 0

And finally changed DRS stage I made SQL type as Varchar(27)

TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss") and it is working fine but I'm still getting a single warning

Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ

Is there any way I can supress the above warning with out using message handler or any work around to solve the aboe issue



Thanks,
Raju

[ray.wurlod]

Could this data type require FOUR fractional second digits (making a total of 27 characters in the timestamp)? You'd need "%ss.4" in the format string as well as only four zeroes in the constructed data.

[SURA]

Datetime2(7) is also timestamp field. In SQL server datetime wont store the data if the data (year) is prior to 1753. On that case need to use datetime2.

You can choose the timestamp as the datatype in DS and load the data. It will work.

You are trying to insert the data to datetime2. But how the source data looks like?

If you want to load the current time , then you can choose the datatype as timestamp for the column in the TFM without any conversion and pass it.

DS User

[cppwiz]

We had to apply the DataDirect 6.0 upgrade to get DataStage 8.1 on AIX to work with the SQL Server 2008 datetime2 data type. I think the DataDirect 6.1 drivers were just released. What version are you using?

[madhusds]

I am also facing same problem could any one through some light on it plaese.

[somu_june]

Hi All,

Thanks everyone for the help. Timestamp datatype is working fine if I use ODBC stage,

For DRS stage I need to use NVARCHAR(27) datatype and I need to use the TimestampToString function like below


TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss")

So when we are using a DRS stage to load data in to Sql server we need to us NVARCHAR datatype for Date, Datetime2(7) datatypes in SQL server 2008

Thanks,
Raju

[madhusds]

I used NVARCHAR with 27 length in DRS stage.. It worked!