PROFESSIONAL NEWSGROUPS
WINDEV
,
WEBDEV
and
WINDEV Mobile
Home
|
Recent messages
|
Connect
|
Sign out
|
English
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
Connect yourself…
Damon Hacker
#1
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?
Report
0
0
Leo Voet
#2
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?
Report
0
0
Olivier Priser
#3
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
Report
0
0
Syed
#4
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
Report
0
0
Guy Meredith
#5
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
Report
0
0
→ Go back to WINDEV 2024
WINDEV 2024
WEBDEV 2024
WINDEV Mobile 2024
WINDEV (earlier versions)
Français
English
Español
Portuguesa
Close this window
Search type
Only topics
All the messages
Search period
Any time
Past hour
Past 24 hours
Past week
Past month
Past year
Cancel
Preview of your message
Adding an image
Import an image from a URL
Send an image from a file of your disk
Drop a file or click "Browse..."
or
Cancel
0%
WLanguage
SQL
XML, HTML
JAVA, Javascript
Text