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

    Lightbulb Unanswered: PowerDesigner problem (resolved)


    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:
    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)...

    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 ()
    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 03:34. Reason: thread resolved

  2. #2
    Join Date
    Feb 2002
    Willy is on vacation
    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
    Plzeň, Czech Republic
    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 :-)

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

  4. #4
    Join Date
    May 2007
    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}

    case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else c.max_length 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,
    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 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,
    case (i.is_not_for_replication) when 1 then 'true' else 'false' end,,
    case(c.is_rowguidcol) when 1 then 'true' else 'false' end
    [%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)
    o.type in ('U', 'S', 'V')
    [ and = %.q:OWNER%]
    [ and]
    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