PC SOFT

PROFESSIONAL NEWSGROUPS
WINDEVWEBDEV and WINDEV Mobile

Home → WINDEV 2024 → Accessing MS SQL table in different schema than dbo
Accessing MS SQL table in different schema than dbo
Started by Damon Hacker, Jan., 14 2008 12:41 AM - 4 replies
Posted on January, 14 2008 - 12:41 AM
After much troubleshooting and continued failure linking to a MS SQL 2000 table via OLEDB, I have uncovered the issue--I just don't know what to do about it.

The tables that I am interested in fall under a different schema than the default 'dbo' schema. This is preventing me from accessing the data. I won't be able to change the schema due to other business constraints.

Does WinDev provide a way via OLEDB to add a table in a database under a different schema?
Posted on January, 14 2008 - 11:51 AM
I don't know much about MS SQL, but using Oracle, I can access any schema the user I logged in with has access rights to. I have installed the Nativ access driver. Maybe that is what is needed for.

Leo.



>After much troubleshooting and continued failure linking to a MS SQL 2000 table via OLEDB, I have uncovered the issue--I just don't know what to do about it.

The tables that I am interested in fall under a different schema than the default 'dbo' schema. This is preventing me from accessing the data. I won't be able to change the schema due to other business constraints.


>Does WinDev provide a way via OLEDB to add a table in a database under a different schema?
Posted on January, 17 2008 - 5:05 PM
Hello,

I don't use it but as fer as I know 'dbo' is related to the owner of an objet under MS SQL.
You can use tables with other owners but you need to log in via that user or that group and point to that table using
the owner's name.
As an example with "olivier" as owner : SELECT NameField FROM olivier.MyTable WHERE ....

You might need to use [] also depending on certain connection properties.

dbo is the only owner which is possible to access tables without naming dbo in the query. Like : SELECT NameField FROM
MyTable WHERE ....

All that is not related to Windev but to SQL. You can find more infos in the SQL doc or on the MS website.

Regards,

Olivier
Posted on August, 09 2014 - 9:53 PM
For SQL Server you can create a SYNONYM for the table within the SQL Server and then
Windev will import the struct/data. for some reason when querying sql server it does not prefix it with the schema name, just throws table name to sql server.

Assume its MySchema.MyTable

You will need to run this at the SQL Server


CREATE SYNONYM MyTable
FOR MyDatabase.MySchema.MyTable;
GO
Posted on February, 28 2020 - 6:15 AM
I tried various connections (except for the PC Soft Native Access Sql Server) to try and import into my Analysis the tables from a Sql Server database, which existed in 3 different schemas. No connection worked - I could see the tables in the import dialogue but on the final screen of the import, it failed with an "Unable to open table. OLE DB Access Error. Error number 170129. Failure Opening file. System Error Details: Description Invalid Object Name".

I tried the Synonym option as suggested, which seemed like an excellent idea to overcome the problem. The table descriptions were imported, even though I received an error, but the relationships between the tables were lost, which seems to suggest I now need to add links to the tables.

Did you experience the same issue when you used synonyms? If so, how did you overcome them?

Thanks

Guy