Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Location
    Plzeň, Czech Republic
    Posts
    10

    Lightbulb Unanswered: PowerDesigner problem (resolved)

    Hello,

    I am trying to modify my MS SQL 2005 database by PowerDesigner model using PowerDesigner built-in Modify feature.

    PD however seems to have problems reverse engineering the database.

    I get tons of errors like this:
    Code:
    Reverse engineering tables...
       Table _MyTable
    Unable to list the columns.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
    SQLSTATE = 37000
    It also seems that it generates incompatible SQL code when modifying the database. E.g. (Parentheses after nonclustered violates syntax)...

    Code:
    create table _H_IpNetworkToVlanNetwork (
       ipNetAddress         bigint               null,
       vpnNetId             int                  null default 0,
       vlanId               int                  null,
       _ChangeDate          datetime             null,
       _ChangeType          int                  null,
       constraint PK__H_IPNETWORKTOVLANNETWORK primary key nonclustered ()
    )
    go
    What may be wrong? Do I have badly configured PowerBuilder? Or maybe badly configured ODBC data source?

    Thanks for any help.

    I have PowerBuilder 12.1 and MS SQL 2005
    Last edited by jsiii; 03-06-07 at 04:34. Reason: thread resolved

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    You are most likely using an incompatible definition file. The MS SQL 2005 definition file is sqlsv2k5.xdb located under the Resource Files/DBMS

  3. #3
    Join Date
    Mar 2007
    Location
    Plzeň, Czech Republic
    Posts
    10
    Hi Willy,

    Thanks for the reply. I have MS SQL 2000 definition set. My database however says it is running in MS SQL 2000 compatibility mode. When I switch to 2005 the situation gets even worse and not even tables can be listed. I will try a different DBMS and let you know about results.

    Thanks anyway, looks like this is the problem.

    UPDATE - So the correct database was MS SQL 7x for some reason. Many thanks again Willy :-)

    JS
    Last edited by jsiii; 03-06-07 at 04:02.

  4. #4
    Join Date
    May 2007
    Posts
    1
    To recover from this bug, you have to change DBMS configuration. To change your DBMS config follow below steps;
    1) Goto "tools/resources/DBMS"
    2) select Microsoft SQL Server 2005 (or any derivative from it)
    3) Click properties
    4) Go to "Script/objects/column"
    5) Click on SqlListQuery
    7) Backup current SQL script in the value window.
    6) change the SQL script in the value window with the following;

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

    select
    u.name,
    o.name,
    c.column_id,
    c.name,
    t.name,
    c.precision,
    case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else c.max_length end,
    c.scale,
    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,
    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,
    convert(varchar(8000), d.definition),
    case (c.is_identity) when 1 then convert(varchar, i.seed_value) + ', ' + convert(varchar, i.increment_value) else '' end,
    c.collation_name,
    case (i.is_not_for_replication) when 1 then 'true' else 'false' end,
    d.name,
    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)
    join [%CATALOG%.]sys.types t on (t.user_type_id = c.system_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)
    where
    o.type in ('U', 'S', 'V')
    [ and u.name = %.q:OWNER%]
    [ and o.name=%.q:TABLE%]
    order by 1, 2, 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •