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

Question

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

Surendra Adhikari 211

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.

LiHong-MSFT 10,056 Reputation points

2022-04-11T03:41:21.413+00:00

Hi @Surendra Adhikari

does not complete execution in one database

Is there an error message?
Also please share us your SQL Sever version.

Best regards,
LiHong

2 answers

Your answer

LiHong-MSFT 10,056 Reputation points

2022-04-11T03:41:21.413+00:00

Hi @Surendra Adhikari

does not complete execution in one database

Is there an error message?
Also please share us your SQL Sever version.

Best regards,
LiHong

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

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?

Surendra Adhikari 211 Reputation points

2022-05-08T11:53:48.473+00:00
Hi Erland,
the scenario is as following:

let the database having the problem is dbone and another database is dbtwo.

by using dbone the query keeps running without result and never completes.

the queries like following do not execute:
use dbone; select * from tableone; select * from dbtwo.dbo.tabletwo; SELECT t.* FROM sys.dm_tran_locks t;

but the queries which do not reference any database execute like following: use dbone; select @@version;

the queries referencing dbone execute successfully by using dbtwo as following:
use dbtwo; select * from dbone.dbo.tableone; SELECT t.* FROM dbone.sys.dm_tran_locks t;

the result of @@version is:
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-05-08T12:03:08.677+00:00

You have Service Pack1 of SQL 2016. which is a very old build. You should download and install Service Pack 3, which is the last service pack for SQL Server 2016. This may or may not resolve the issue, but there is no reason that you should run with an outdated build.

I should have been clear about the query for sys.dm_tran_locks. You should run this query from a separate query window and make sure that you are in tempdb when you run this query.

I should also clarify that the reason I asked for this is that your description made think that something could be wacko on your server, so I want to to know if we could use this DMV at all. It has been a while since you posted your original question, but it seems that the issue persists one month later. So maybe then your server is OK overall (since you would have restarted it).

Anyway, apply SP3 first and we can talk later.

Answer 2

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

Share via

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

2 answers

Your answer