SQL2005 Database Schema without Assocated Owner?

I was quite happy to see that schema names and database users had finally been separated in SQL 2005 and used it to finally clean up a mish-mash database that hosts a number of tiny unrelated applications.

I created a new schema for each little section, and the schemas are all owned by dbo. There is no associated user with the schema. Really it all works great...

...except...

from an ADP project in Access! If an object is in the dbo schema, or in a schema with a matching user, then everything works fine. All of the objects that are in schemas without a matching user, have the name of some random user in the database list

For example, the table NEWS.Articles is listed in access as "News (TAT$)". TAT$ is a user in the database, but is in no way related to the NEWS schema, nor the Articles table!! When you try to open the table, you get an ADO error that it does not exist! This is basically the same behavior you see if you try to use the old enterprise manager or other SQL 2000 tools.

Everything works fine if you create a linked table through ODBC.

Is there any workaround for this behavior (besides having dummy users for the schema...)

Joel

Answer this question

SQL2005 Database Schema without Assocated Owner?

Eamon_OTuathail
I guess ODBC is using the new native client while you ADO conenctionstring does not. Try using the explicit connectionstring for SQL Server 2005, rather than the old one. I don’t have much experience about these differences, buit it sounds reasonable that oDBC using Native client while ADO does not.

HTH, jens Suessmeyer.
4i
Yeah, that definitely seems to be the issue. I can choose the client when making an ODBC connection. When making an ADP project, though, I can't find any way to change the connection properties, other than the host name, etc. It doesn't really give you an option to choose different data sources, since it is geared exclusively to SQL server.
SQL2005 Database Schema without Assocated Owner?

Answer this question