SQL Server Performance Forum – Threads Archive

T-SQL in SQL 2005 requires full table names, why?

Hi all, Just at the very beginning of looking at SQL 2005 here. I’ve copied our ERP database (minus most of the actual data) to our 2005 test server. The application fails on startup claiming it can’t find a certain table. I’ve verified the table exists and is accessible to the user the app is connecting as (at least, from what I can make out, the new schemas thing is a bit confusing). When I use the old Query Analyser from SQL 2000 I connect as the same user the ERP app is using and run the query the app failed on. Right enough, it claims it cannot find the table specified in the query. However, when I change the FROM clause so it goes <dbname>.<owner/schema>.<tablename> it works. Is this something I can switch on and off or are we stuck with 2000 until (and indeed if) the app vendor decides to rewrite all the queries? TIA
Niall

If this is a 3rd party app, I would not blindly install the database on SQL 2005. There could be references to 2000-specific system objects that simply do not exist in 2005.

Hi,
it might be the problem of schema / object owner if the object owner is other than dbo you need to specify it , does it runs from QA without specifying dbname !? Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

All the tables in the DB belong to the schema "bmssa". The application is attempting to connect as the user "bmssa" and I’m logging in through Query Analyser as the same user. The user appears to have full permissions for that table and still I get "Invalid Object Name" from both the app and QA. I have checked and the user and schema "bmssa" both have select permissions for the table in question. So far I’m not liking this new schemas stuff, it just seems to add complexity to me.

You say you installed a database that was running under SQL 2000 previously. Is the user bmssa a SQL login? If it is, then does the SQL 2005 instance also have this login? In SQL 2000 you can use the sp_change_users_login sproc to match SQL logins between different server instances – there must be something similar in SQL 2005.

Before digging too deep into it, I would contact your vendor and ask if their ERP application is supported on SQL Server 2005. If it isn’t (yet), all of your further testing doesn’t make sense and you could spend your time better elsewhere. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Frank, The app is not supported on SQL 2005 but then again the app is about to go end of life in another month or two anyway and we’re not in a position to upgrade yet. The new version requires 2005 as do some other things we want to upgrade, hence the need to prepare to migrate to 2005. Adriaan, The bmssa login was present in the backup file I made on the 2000 server when I restored it onto the 2005 machine. This didn’t create it in the server logins on SQL 2005 tho, I had to do this myself.

You still have to match the login in the restored database with the one you have created on the new instance of SQL Server. The logins do match by name between what the database says and what master db says, but they do not match by the unique smallint UID column of sysusers (at least as it was in SQL 2000).

In SQL 2005 you can set the default schema, and it defaults to dbo if it isn’t set alter user bmssa with default_schema bmssa Cheers
Twan

]]>