question

SurendraAdhikari-2570 avatar image
0 Votes"
SurendraAdhikari-2570 asked ErlandSommarskog commented

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

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SurendraAdhikari-2570

does not complete execution in one database

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

Best regards,
LiHong

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Erland,
the scenario is as following:
1. let the database having the problem is dbone and another database is dbtwo.
2. by using dbone the query keeps running without result and never completes.
3. 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;

  1. but the queries which do not reference any database execute like following:

     use dbone;
    
        select @@version;
    

  2. 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)


0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog SurendraAdhikari-2570 ·

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.

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.