Migrating an application with a SQL 2000 back end

Question

Migrating an application with a SQL 2000 back end

Ian H

SSC Enthusiast

Points: 196
More actions
October 10, 2007 at 3:25 pm

#120792

Hi all,
I've been giving google a good workout on this matter, with modest success, and so am hoping that you'll have a few insights to share on how best to proceed -- I'm guessing that the answer is quite simple, and that I simply haven't stumbled across it yet.
I'm tinkering with an application which runs quite happily with SQL 2000 as its back end. Testing it out on 2005, I've encountered a few bumps in the road.
Take a db where half of the tables are owned by dbo, and the rest by a different owner. In 2000, I can happily SELECT * from [tableName] regardless of the owner. In 2005, how can I do a similar select without having to include the prefix? Is there a way to have the db search for an appropriate table within the db when a query is thrown at it, so long as there aren't any naming conflicts with the table names themselves resulting in ambiguity?
I've encountered SYNONYM, and testing reveals that the use of synonyms works around the issue to a degree, although it seems like a bit of a hack that's missed the mark. Also, if I'm running a batch which alters multiple tables and falls to using a synonym, it will fail due to the limitations of synonyms, of course.
Ideally, the end result would allow use of the tables through either of [owner].[tableName] or just [tableName].
I look forward to hearing from you,
- Ian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Ian,

the best solution would be to always use the two-part name like dbo.tablename. Not only is it a best practice but there's also a (very) small performance gain by doing so.

But if that's not an option than you need to make sure that your users have the correct default schema. If you address an object without the schema name SQL Server will first check the sys schema if such an object exists, when the users default schema and if it still hasn't found the object it will try the dbo schema.

You can read more about schemas in BOL or on this site.

Markus

[font="Verdana"]Markus Bohse[/font]

Ian H SSC Enthusiast Points: 196 More actions · Answer 2

Thank you very much Markus 🙂

I'm using synonyms in testing at the moment to buy me a couple of days -- ideally enough time to learn how to implement a more appropriate solution with schemas through BOL, etc.

bnordberg SSCertifiable Points: 5480 More actions · Answer 3

I would wonder why objects are owned by different users. My practice (and I was told it was best practice??) was that dbo should own all objects in production. Because issues like this, restores ... can all get hairy when objects are owned by different schemas. If there is not a good reason for different owners, I would use sp_changeobjectowner (for 2000 - run before migration http://msdn2.microsoft.com/en-us/library/ms177519.aspx ) or ALTER AUTHORIZATION (for 2005 if you do it post migration).

Good luck