Unable to show database objects error

CodantiMCodantiM Posts: 245
edited May 18, 2010 4:20AM in SQL Prompt Previous Versions
I am running SQL Prompt 4.0.4.14 and have found that in some cases SQL Prompt tries to access a database for no reason, and if you don't have access you get an error.

Say that the server you are working on has a DB named NPI, but you don't have access to it. When you paste this code in to SSMS you will get an error.
SELECT 
	'X' AS NPI
INTO #NPI

SELECT 
	*
FROM #NPI NPI
WHERE
	NPI.NPI IS NOT NULL

It even happens without a temp table:
SELECT 
	*
FROM msdb.dbo.backupfile NPI
WHERE
	NPI.backed_up_page_count IS NOT NULL;

I'm not sure why it thinks that the NPI. in the query refers to a DB instead of to the alias, but it is annoying.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Michael,

    I think we have noticed this already and it should be fixed in a future version. It doesn't apply strictly to databases, but could also happen if your alias name is the same as another table in the same database. The bug reference number is SP-2370.
Sign In or Register to comment.