Catalog SQL Server reports wrong field length

Posts   
 
    
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 02-May-2019 11:54:42   

5.5 (5.5.1) RTM adapter Access and SQL Server

I have this problem for quite some time. I can work around it by editing manual, but it's a good time to ask why it appears.

I sync the relational model data from both SQL server and Access DB (db first), seems wrong, but this is just the way it is (I update both from my metadata versioning manager). And I do have to use it this way as to get all new fields/columns and get the right mappings in both Access and SQL server. It works for hundreds of tables and thousands of fields. No problem.

Let's leave that. But with one field 'Zoekcode' in table/entity 'Relatie' it reports Length 12 in the SQL catalog explorer while it is in fact 30 (in the entity model, in the real Access table, in the catalog explorer of MS Access schema, and in the real MSSQL table, but not in the catalog explorer of the SQLClient schema.

It's 30 everywhere in the llblgenproj file.

It's also 30 in the INFORMATION_SCHEMA.COLUMNS.

So my question is, where does this come from then?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 12:30:01   

You have 'Length precision scale follow dblength precision scale' set to true in the project settings -> Database first development ?

I can't reproduce it as with that setting on field lengths are synced with the target length.

I sync the relational model data from both SQL server and Access DB (db first), seems wrong, but this is just the way it is (I update both from my metadata versioning manager).

what exactly do you mean with this and the first paragraph? simple_smile If there's a problem we should look into it, however from this problem description I can't determine much wink

Frans Bouma | Lead developer LLBLGen Pro
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 02-May-2019 14:08:03   

No, 'Length precision scale follow dblength precision scale' is false.

I sync the relational model data from both SQL server and Access DB (db first), seems wrong, but this is just the way it is (I update both from my metadata versioning manager).

With this I just mean as a remark before the conversation should go to an undisired direction. I'm not using it the way it's 'supposed' or expected to. Ignore it as a question, it's just the way I work for years. it's no problem.

For my search: Where do the table and field properties from the SQLClient in the Catalog Explorer come from?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 15:19:05   

Puser wrote:

No, 'Length precision scale follow dblength precision scale' is false.

Please have this setting set to true so the model fields are synced with the target fields. Having this setting set to false means the model values aren't synced, which can be what you want in some cases hence the setting.

I sync the relational model data from both SQL server and Access DB (db first), seems wrong, but this is just the way it is (I update both from my metadata versioning manager).

With this I just mean as a remark before the conversation should go to an undisired direction. I'm not using it the way it's 'supposed' or expected to. Ignore it as a question, it's just the way I work for years. it's no problem.

Ok no problem simple_smile

For my search: Where do the table and field properties from the SQLClient in the Catalog Explorer come from?

what properties in particular are you referring to? Field length etc. ? Please see the SQLServer schema retriever class in the sqlserver driver sourcecode in the sourcecode archive.

sql query:


SELECT  INFORMATION_SCHEMA.COLUMNS.*,  
    (
        SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')
    ) AS IsComputed, 
    (
        SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')
    ) AS IsIdentity,
    (
        SELECT COLUMNPROPERTY(OBJECT_ID(@sFullTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')
    ) AS IsRowGuidColumn,
 (
        SELECT COLUMNPROPERTY(OBJECT_ID('@sFullTableName'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'GeneratedAlwaysType')
    ) AS GeneratedAlwaysType 
FROM        INFORMATION_SCHEMA.COLUMNS 
WHERE   INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=@sTableName 
            AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='<schema>'
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ASC

where fulltablename / tablename are parameters and <schema> is replaced by e.g. dbo.

Frans Bouma | Lead developer LLBLGen Pro