PD 16.1 reverse engineers SQL Server datetime2 incorrectly

[Former Member]

I am reverse engineering SQL Server 2008 R2 tables that contain columns defined as DateTime2(3).  PowerDesigner creates these columns with a data type DateTime23.  Is there a fix for this?  I can manually correct the columns.

[arnaud_laurent · Accepted solution]

It works fine on my machine using a .sql file in PD 16.1 EBF17. I currently have no connection to that database so I have not tested using SQL Native ODBC.

There must be mappings in your DBMS. Go to Database > Edit Current DBMS... and select  ::Script\DataType\PhysDataType

datetime2 = DT

datetime2(%n) = DT.

If you are using ODBC, the value is retrieved from the 'precision' column of the 'sys.column' table. Check the actual value for the datetime2 column in SQL server, I mean how datetime2(3) is stored for its length. What you could try as a workaround is to add the ODBC Datatype mapping by modifying the xdb file. Edit your DBMS, select ::Script\DataType\OdbcPhysDataType
and add the mapping to translate them.
For info, there is an open bug: CR# 674142.

Hope this could help.

[Former Member · Accepted solution]

I'm using an ODBC connection that uses SQL Server Native Client 10.0. 

I see the mappings in the PhysDataType file that you mentioned:

datetime2 = DT

datetime2(%n) = DT

In sys.columns, the precision for the column is 23 and the scale is 3. 

Is the fact that that both datetime and datetime2 map to DT causing a problem? 

I created a test table:

create table TestDateTime

(OldDateTime        datetime

,DateTime2_normal   datetime2

,DateTime2_1        datetime2(1)

,DateTime2_2        datetime2(2)

,DateTime2_3        datetime2(3)

,DateTime2_4        datetime2(4)

,DateTime2_5        datetime2(5)

,DateTime2_6        datetime2(6)

,DateTime2_7        datetime2(7)

)

When I pull it into PD, I get the bad data types. 

Even the DateTime2_normal column is incorrect. 

Here's the generated DDL from PD,

create table dbo.TestDateTime (

   OldDateTime          datetime             null,

   DateTime2_normal     datetime27           null,

   DateTime2_1          datetime21           null,

   DateTime2_2          datetime22           null,

   DateTime2_3          datetime23           null,

   DateTime2_4          datetime24           null,

   DateTime2_5          datetime25           null,

   DateTime2_6          datetime26           null,

   DateTime2_7          datetime27           null

)

In sys.columns

column_name         system_   type_id precision scale

--------------------         -------------- ---------   -----

OldDateTime          61             23        3

DateTime2_normal  42             27        7

DateTime2_1          42             21        1

DateTime2_2          42             22        2

DateTime2_3          42             23        3

DateTime2_4          42             24        4

DateTime2_5          42             25        5

DateTime2_6          42             26        6

DateTime2_7          42             27        7

Any other suggestions?

Thanks for your help!

[Former Member · Accepted solution]

I also updated to EBF17 and still have the same error.

Where can I find the open bug CR# 674142?

[Former Member · Accepted solution]

I found the CR on the support website.  Is there any way I can add comments to it or indicate that I am experiencing the same issue? 

[arnaud_laurent · Accepted solution]

Sorry, these Change Requests cannot be edited by users.

When an incident case is open with Technical support, engineers investigate the problem and if they conclude it is the same bug, your case will be associated to the CR.

[arnaud_laurent · Accepted solution]

For your information, CR# 674142 will be solved in next PD 16.5.2 PL3.

The solution is to edit your MSS2008 resource file, select MSSQLSRV2008::Script\Objects\Column\SqlListQuery
and use the below value.

HTH.

{OWNER,
TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL,
IDENTITY, DOMAIN, DEFAULT, ExtIdentitySeedInc, COMMENT, ExtCollation,
ExtIdtNotForReplication, ExtDeftConstName, Sparse, FileStream,
ExtRowGuidCol}

select
u.name,
o.name,
c.column_id,
c.name,
case when c.system_type_id in (165, 167, 231) and c.max_length = -1 then t.name + '(Max)' else t.name end,
case when c.system_type_id in (42) then c.scale else c.precision end,
case (c.max_length) when -1 then 0 else case (c.system_type_id)
when 240 then 0
when 99 then (c.max_length/2)
when 231 then (c.max_length/2)
when 239 then (c.max_length/2)
when 42 then 0
else (c.max_length) end end as colnA,
case when c.system_type_id in (42) then 0 else c.scale end,

case(c.is_computed) when 1 then convert(varchar(8000), (select
z.definition from [%CATALOG%.]sys.computed_columns z where z.object_id =
c.object_id and z.column_id = c.column_id)) else '' end as colnB,
case(c.is_nullable) when 1 then 'NULL' else 'NOTNULL' end,
case(c.is_identity) when 1 then 'identity' else '' end,

case when(c.user_type_id <> c.system_type_id) then (select d.name
from [%CATALOG%.]sys.types d where d.user_type_id = c.user_type_id)
else '' end as colnC,
case when (d.definition = '('''')') then '~''''~' else convert(varchar(8000), d.definition) end,

case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' +
convert(varchar, i.increment_value) else '' end as colnD,
convert(varchar(8000), p.value),
c.collation_name,
case (i.is_not_for_replication) when 1 then 'true' else 'false' end,
d.name,
case(c.is_sparse) when 1 then 'true' else 'false' end,
case(c.is_filestream) when 1 then 'true' else 'false' end,
case(c.is_rowguidcol) when 1 then 'true' else 'false' end
from
[%CATALOG%.]sys.columns c
join [%CATALOG%.]sys.objects o on (o.object_id = c.object_id)
join [%CATALOG%.]sys.schemas u on (u.schema_id = o.schema_id)
left outer join [%CATALOG%.]sys.types t on (t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id)
left outer join [%CATALOG%.]sys.identity_columns i on (i.object_id = c.object_id and i.column_id = c.column_id)
left outer join [%CATALOG%.]sys.default_constraints d on (d.object_id = c.default_object_id)

left outer join [%CATALOG%.]sys.extended_properties p on (p.major_id =
o.object_id and p.minor_id = c.column_id and p.class = 1 and p.name =
'MS_Description')
where
o.type in ('U', 'S', 'V')
[ and u.name = %.q:OWNER%]
[ and o.name=%.q:TABLE%]
order by 1, 2, 3

[Former Member · Accepted solution]

Thank you, Arnaud! I modified the file with this sql and now PD will reverse engineer datetime2 correctly.