Topic: Connecting to SQL Server but can't see tables

Go back to forum.

Topic by flozo 2009-07-23 04:32	Connecting to SQL Server but can't see tables I am connecting to SQL Server 2008 Database using windows authentication and have tried the JTDS driver and also downloaded the latest MS JDBC driver but I have had issues seeing objects that are not in the dbo schema. I am currently connecting using the MS SQL Driver, in the schemas window I get a list of users and roles for the database I am connected to but not SQL Server 'Schemas' The only table I can see is one table that is part of the dbo schema. All the other tables in the database are part of other schemas none of which I can see. For example Schema 'ModelA' is owned by dbo, but 'ModelA' doesn't appear in the schema list and under 'dbo' in the schema list in Data Cleaner none of the table scontained within ModelA can be viewed. ModelA does not exist as a 'User' in Sql server. Schema 'ModelB' is owned by 'userA', however 'ModelB' doesn't appear in the Data Cleaner schema list, and none of the tables in that schema appear under 'userA' in Data Cleaner schema list. Again 'ModelB' doesn't exist as a 'User' in Sql server does any one have any thoughts on how to get round this issue?
Reply by flozo 2009-07-24 02:40	Right we have a way round this: Created a user called 'ModelB' made that the owner of the schema 'ModelB' (not sure if it has to be the owner or if the user just has to exist) I also had to ensure the username I was connecting with had SA rights otherwise I couldn't see all the users.. but that might have been something to do with the permissions on the DB? Is this a bug? when connecting to SQL server shouldn't it be showing SQL server 'schemas' rather than users and roles. After all it is the schema that owns the objects (in SQL server).
Reply by kasper 2009-07-25 09:57	Hmm I'm not quite sure how MS SQL works because I've never used it a lot myself, but I know this wiki page contains a few notes and tips for configuring it ... http://eobjects.org/trac/wiki/DataCleanerMicrosoftSql
Reply by kasper 2009-07-25 14:39	Oh and to answer your questions: Yes - the schema tree should contain schemas/databases (whatever they call a "collection of tables") so it seems there might be a database driver bug. Which driver are you using?
Reply by flozo 2009-07-27 01:28	HI kasper, thanks for your posts. I am using Microsoft SQL Server JDBC Driver version 2.0 which is the one for SQL Server 2008. I had managed to connect using windows authentication using a similar method to the post in the wiki, I just put the driver jar file and the associated dll in the same folder as the datacleaner.exe file and that worked fine. I had the same issue originally when connecting using the JTDS driver, the JTDS connection only shows 'users' in the schema tree, so now I have made a user with the same name as the schema I want to view (as per my 2nd post) I can see the tables associated with that schema via the JTDS schema too. I was having the same kind of problems in Talend's open profiler, using the JTDS driver only users are displayed, so the work around of creating a user with the same name as the schema was needed for open profiler. So maybe you are right in saying its a bug with the driver. I just wasn't sure how the software generates the list in the schema tree so thought I should ask. Thanks for the replies anyway. Cheers
Reply by kasper 2009-07-27 09:00	I agree this seems like a bug, but I'm quite certain it's a driver-bug. To populate the tree we use the drivers metadata API (or rather JDBC's metadata API) to retrieve the information. FYI these method calls are used using JDBC: Connection.getMetaData().getSchemas() Connection.getMetaData().getTables(...) Connection.getMetaData().getIndexInfo(...) Connection.getMetaData().getColumns(...) Connection.getMetaData().getImportedKeys(...) Where the two first method calls are probably the ones returning weird metadata. If anyone knows a workaround (for example a better driver or some proprietary API for MS SQL) then I would love to hear about it.
Reply by flozo 2009-07-28 04:01	so the method .getSchemas should be right. I am new to SQL server but have read up and they changed the way ojbects were stored in SQL server 2005. It used to be database.user.table now its database.schema.table so maybe the drivers weren't changed to accomodate that ? Anyway after a bit more reading...Microsoft SQL Server JDBC Driver version 2.0 actually comes with 2 jar files, sqljdbc.jar and sqljdbc4.jar, to be used depending on your preferred Java Runtime Environment (JRE) settings. Our desktops where I work have an old JRE so I am using sqljdbc.jar which supports JDBC 3.0. I am going to try and get the desktop team to upgrade my machine with the latest version of java so that instead I can use the driver that supports JDBC 4.0, maybe in that version the method call might return the correct metadata. Will let you know how I get on.
Reply by asbjorn 2009-08-19 10:33	Hi I think this is the same issue I ran into. I created a ticket http://eobjects.org/trac/ticket/329 and hopefully I will have time to solve in a near future
Reply by asbjorn 2009-08-19 16:23	This issue is now fixed
	You need to be logged in to participate In order to post your own comments on this topic, you need to be logged in. Username: your comments Log in by clicking the login link at the top of the screen

Go back to forum.