1

I've been asked to make some changes in an old asp-classic application. The production server and the server they use for testing and dev or over 20 routers away and the lag time between my typing and then seeing the type is horrible. So I set up a server here, just like theirs (MS Server 2008) installed MS SQL Server 2008 R2. I thought I configured my database just like theirs. They have 100s of thousands of lines of old vbscript.

I backed up the test database on the remote side, copied it over to my server and restored it there. I copied the body of script they have over. Site is up and running. But.... Anything that requires a query to the database creates the error "Invalid object name 'atable'. Looking at their code and there isn't a fully qualified name anywhere. I log on to the remote server start up Management Studio and sure enough you dont need fully qualified names to make any queries. I asked them if I could change all the code to use fully qualified names and the answer was no. I asked how they had configured the database, and the answer was a former contractor set it up that way. (and they like it)

So my questions; is there a configuration option in MS SQL Server 2008 to default to not using fully qualified names? How could they get around not using database, owner, schema in their code? I login as the application user and that doesnt seem to help as well.

Please, I know we should use fully qualified names as best practices. I've read the comments concerning performance gains/losses. Their database instance uses a schema name when you automatically script out a create database script. If I use that schema name to preface table names all is good. They just dont want me doing that in any script I edit or create for them.

Madcowtricks
  • 11
  • 2
  • What happens when you change the users to all have the same default schema (dbo, I'm presuming, is where all these objects live)? It's still going to completely suck for the reasons you pointed out, but at least nobody will have to correct their code. – Aaron Bertrand Mar 30 '15 at 20:56
  • There's only one user. Their application pages log on with that userid. – Madcowtricks Mar 30 '15 at 21:02
  • Oops, hit "add comment" too fast. The tables are owned by that user and the schema name matches that of the user. – Madcowtricks Mar 30 '15 at 21:03
  • 2
    You didn't say anything about this user's default schema. – Aaron Bertrand Mar 31 '15 at 01:16
  • And the user's default schema is all the same. To illustrate, I have a database named DBtest. The database is owned by DBtest. There is a system login named DBtest. And a user for the database named DBtest whose default schema is also DBtest. – Madcowtricks Mar 31 '15 at 18:55
  • Then there's something else going on in your system or application that I can't possibly grasp, sorry. Maybe it's time to start thinking about fixing the application to do things the right way? – Aaron Bertrand Mar 31 '15 at 18:57
  • i agree, if it was mine i would. – Madcowtricks Apr 01 '15 at 03:48

1 Answers1

0

The order of resolution of an object that is not specified (select * from table1) is sys then the user's default schema then finally dbo. So SQL Server (given the database context) will look for table1 in the sys schema, then the assigned schema (which may or may not be dbo) and finally dbo. As long as everything is in dbo (which is very bad practice but you are stuck with that) everything will be found. Its like using one room to store everything.

I have worked for a very astute manager, who supports many databases, who makes it a practice to identify all his objects as three part names -databasename.schema.object but I understand that Microsoft intends to stop supporting this type of name resolution in the future. But at the very least its extremely common to see schema_name.object_name for all objects in scripts.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22