Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hollandstephen
Starting Member
10 Posts |
 Posted - 2007-05-17 : 12:39:13
|
| Thanks for the help. I truly appreciate it.I have a couple of more questions.After executing an ODBC trace, I found something interesting...This app uses SQL Server Authentication. Let's say the SQL Login is "USER1" and USER1 only belongs to PUBLIC.All of the tables in the database are owned by USER1.While logged into the database as USER1:The user attempts an INSERT into TABLE_A without fully qualifying the object name. The INSERT fails due to the fact that it would violate a unique index on TABLE_A.SQL SERVER 2000 - Not fully qualifiedThe error that is returned in SQL Server 2000 is:Server: Msg 2601, Level 14, State 3, Line 1Cannot insert duplicate key row in object 'TABLE_A' with unique index 'TABLE_A_U1'.The statement has been terminated.The connection used for the INSERT statement is then dropped and a subsequent UPDATE statement is allowed to execute.SQL SERVER 2005 - Not fully qualifiedWhen the same insert, using the same credentials is attempted in SQL 2005, the error returned is:Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object 'dbo.table_a' with unique index 'TABLE_A_U1'.The statement has been terminated.The connection used for the INSERT statement is NOT dropped and a subsequent UPDATE statement is NOT allowed to execute because the previous connection does not release the lock.Note that the returned error shows the object qualified with DBO as the owner of the object instead of USER1.SQLSERVER 2005 - Fully QualifiedIf I run the same insert statement with the object qualified as USER1.TABLE_A the error returned is:Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object 'user1.table_a' with unique index 'TABLE_A_U1'.The statement has been terminated.Note that this time, the error message correctly qualified the name with USER1 as the table owner.QUESTIONSIs this not a bug in SQL 2005? Is it possible that the ODBC driver fails to drop the connection it was using for the INSERT, because it is trying to drop a connection to DBO.TABLE_A while the actual connection is to USER1.TABLE_A? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
More regarding the deadlock issue...