Problems with Schema modeler Delphi - Unable to obtain Target Table metadata from connection

delphi

(Mies) #1

I have an active connection but no tables showing. If I refresh I see a triangle before Tables very short.
Screenshot

Also the autoSQL function for tables does not work.
Unable to obtain Target Table metadata from connection MSQLADOConnection. I’ve read other topics about “Unable to obtain Target Table metadata” but they don’t provide a solution…


(antonk) #2

Hello

This error means that the DB driver you use cannot access the database structure. This can happen for several reasons:

  1. DB driver bug
  2. Database-level issue
  3. User ID used to connect to the database cannot access its structure

Please check that your DB user has sufficient rights to access its structure.

Regards


(Mies) #3

Thanks for your quick response.

At design time I connect with sa which has sysadmin rights.

We use ADO with SQLOLEDB.1. (why can’t it access DB structure??)

If I use the native driver SQLNCLI11.1, I can browse the database through the connection.
But…
The parameter schemas=1 is added. We do not want to specify a schema because this may differ per customer. When I specify schemas=0 then DB structure can’t be accessed anymore.

User sa has default schema dbo so I shouldn’t have to specify the schema.

Any suggestions?

Kind regards
Mies


(antonk) #4

Hello

This looks like a security issue or a DB misconfiguration (f.e. the DB has been moved from another server and something is wrong with login mapping)

Could you please connect to the SQL Server using Server Management Studio and execute this query on the database in question?

SELECT
	s.name,
	t.name 
FROM sys.tables t 
	LEFT JOIN sys.schemas s on (s.schema_id = t.schema_id) 
ORDER by 1,2

(please send its result to support@ to keep it private)

PS Does the SQLOLEDB.1 list db tables when the Schemas=1 parameter is added?


(Mies) #5

I’ve send the query results…

SQLOLEDB.1 does work with schemas=1 parameter.


(EvgenyK) #6

Hi,
can you execute the same query:

inside DASM when SQLOLEDB.1 driver is used?
will it produce the same result as Server Management Studio in both modes (schemas=0 and schemas=1) ?


(Mies) #7

Yes,

Done it. All tables give dbo as schema for each table.
With schemas=0 and schemas=1.

I’ve sent screenshots via e-mail.


(EvgenyK) #8

Hi,

What version of DAD and DASM you are using?
you can find it in DAVersion.inc and in the about box of DASM


(Mies) #9

DAD 9.4.109.1377
image


(EvgenyK) #10

Are you using usual MSSQL or Azure version?


try to use this workaround:

  • you can create schema with any DAD/MSSQL driver that works correctly on your side, like FireDac/MSSQL, SDAC, ADO/SQLNCLI11.1 etc
  • in run-time, you can set connection with SQLOLEDB.1 as default connection and DAD will use it by default for all MSSQL connections

(Mies) #11

We use MSSQL.

We all ready set the connection at runtime.
Problem is that we need to specify the db schema (dbo) now in design time and we don’t want the schema hardcoded in the DASchema in the dfm because it can be different for each customer.

Unfortunately every driver has the same problem only work with schemas=1


(EvgenyK) #12

try this workaround:

  • backup
    C:\Program Files (x86)\RemObjects Software\Data Abstract (Common)\Bin\DAADODrv.dad
  • put this one DAADODrv.zip (1.0 MB) into
    C:\Program Files (x86)\RemObjects Software\Data Abstract (Common)\Bin.
  • retest this issue

Note: it is compatible with 9.5 but can cause issues with 9.4.109.1377. in this case, you can revert original driver


Varchar(max) translates to string(65536) nvarchar(max) to WideMemo
(Mies) #13

Hi Evgeny,

Yes this fixed the problem. Driver reads db structure with schemas=0.

Thank you very much.

Can I uses this workaround in a production environment or should I wait for an official update?

Regards,
Mies


(EvgenyK) #14

.dad driver is used in DASM only so it should be safe for your environment.

this fix will be present in this week’s beta.