query does not execute in one database but executes with full name convention from another database

Surendra Adhikari 206 Reputation points
2022-04-10T05:18:55.2+00:00

I am having a problem in which the query does not complete execution in one database but the same query runs from another database by using full naming convention for the table. below is the explanation. 

use dbone
select top 1 *
from dbo.tableone

this query execution does not complete. 

use dbtwo
select top 1 *
from dbone.dbo.tableone

this query executes.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,703 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-04-10T09:47:23.377+00:00

    Interesting. It seems to be a blocking situation, but I cannot immediately say exactly what that would be. The simplest way to resolve the situation may be to restart SQL Server, but that may not be feasible.

    I think we need some more information in order to help you. First, can you share the output of "SELECT @@version"?

    Next, what permissions do you have on this instance. If you run SELECT * FROM sys.dm_tran_locks do you get a result set or an error message? (At this point I'm asking this question to see if we can use this DMV for troubleshooting at all.)

    Do you have access to the SQL Server errorlog? Does it include anything interesting like dumps or similar?

  2. Ronen Ariely 15,096 Reputation points
    2022-04-10T13:33:53.28+00:00

    Hi,

    this query execution does not complete.

    (1) Please elaborate what happens when "this query execution does not complete". DO you get an error? If so then we need to get the exact full error.

    (2) Please confirm that you execute the query while connected the right database. It might be related to the executing the query while connected to a different database which is why using three parts names works while two parts names do not find the object

    Execute the following query and confirm that you are in the dbone database: 

    select DB_NAME()

    If you do not get dbone then this is the issue

    0 comments