Inconsistent SQL Error

Question

Hi, 

We have a released product running on SQL 2008/2005.  On all of our internal servers, plus other customer servers, we can execute the following SQL with no issues:

SELECT order.name FROM [OtherDB].[dbo].order

"OtherDB" simply refers to a DB on the same server.

However on one customer server, also SQL 2008, the EXACT same SQL yields the error "The multi part identifier order.name could not be bound."

Clearly I can, and will in the future, fix this with an table alias, however this is part of a large system and changing this particular code will cost us weeks of testing.  I feel like there must be a reason why it works differently on this one server, perhaps some "SET" option?

Thanks,

Jeff

Answer 1

There could be a few problems:

1. Case sensitive installation of SQL Server - in this case all table names and column names must match exactly the case in the DDL of the table

2. Permission issues - no access to the table

I bet on the first problem.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Make a mental note of the exact time it occurred, and go on a deep dive into the SQL log files as well as event viewer, assuming you have permissions to get to one or the other or both..  It's highly likely that somewhere buried in there will be a more detailed explanation.

Answer 3

So you get this error when run the query from your product or through SSMS ?

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 4

Thanks for all the replies, no progress for me.

The server and database are set to SQL_Latin1_General_CP1_CI_AS from server options.

We are doing this in SSMS on a db owner account.  The query above is a sample that recreates the issue, the query in the app is much more complex and involves linked servers.  I didn't want to confuse the issue so I reduced it to the smallest statement I could.

I did look in the server logs but there is nothing that corresponds to the time we run the sample in SSMS.

Since last night we have been able to recreate this problem on our own 2005 and 2008 servers.  We can litterally run that query on some databases and not others under the sa account.  We are starting to think it may have to do with where and what version of SQL the databases were created.  This is a version 7 application that is over 10 years old so we have databases that were created on SQL 2000, 2005 and 2008.

Thanks,

Jeff

Answer 5

Making progress.  If we create a new database in SSMS on 2005 with SQL 2000 compatibility turned on the query fails.  Since it is likely many of our databases are originally SQL 2000 the question is how to we move them off this setting?

Found how to turn it off, this is now a management decision, thanks all.

Answer 6

Please check the below blog post for the required query.

http://blogs.msdn.com/b/ai/archive/2012/06/12/sql-server-set-comparability-level-for-all-server-databases.aspx

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.