No assistance provided for one server

I connect to multiple sql servers. However, one server and database just isn't providing the popup assistance the others are. I've run SQL Profiler and I don't even see the same queries being issued to the database. The objects query is not but another one asking about the servername is. Where can I look? I did try to create an explicit connection, but that didn't seem to do anything different.

Select

produces a list of databases, schemas, and functions.

Select * from Database.

produces a list of schemas

Select * from Database.dbo.

produces an empty box.

I am sorry, I don't exactly understand the problem. Does it work for other databases on the same server? Do you get any error messages? What about the collation settings of that database?

It works fine for other databases on the same server. No error messages popup. Collation settings are the same for all servers and databases. Certain schemas do work fine in that database. Permissions are the same (group I am in is sysadmn). Schema is setup the same on both servers.

Behavior is the same in Query Analyzer and SSMS.

Also navigating through using the window starting from Select * From allows me to see all tables within a schema as well as columns within tables.

What is exactly the schema name?

Two more questions. Does it work in places other than FROM or SELECT parts, for example, for EXEC?

Does it work if you specify full object name including all 3 parts, like Database.dbo.SomeTable. ?

schema name is sysdba

Select
produces box with functions, databases, schemas.

Select sysdba.
produces just an empty box.

Select FROM
produces box with functions, databases, schemas.

Select FROM sysdba.
produces empty box.

Select From Database.sysdba.
produces empty box.

This is the query I see in profiler:
select serverproperty(''ServerName''), system_user, db_name(), user_name()

When using the server that works, I see this query (it never shows up on the server that isn't working):
select
name, xtype, id
from
Database.dbo.sysobjects
where
xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'')
and (uid = @P1 )
and not (name = ''dtproperties'')
and not (name like ''dt%'' and xtype = ''P'')

Please try comparing results of SELECT * FROM Database.dbo.sysusers WHERE name = 'sysdba' query executed on the server where you experience the anomaly with results of the same query executed on the other server(s) where it works for you. Check if on both servers it is an existing user/schema mapped to an existing logon.

In case if this is a SQL Server 2005 database, try changing the "Objects" query in SQL Assistant options to the following

	Code:
	select name, type, object_id from /db./sys.all_objects where type in ('U','S','V','P','X','RF','FN','TF','IF') and schema_id = :SCHEMA_ID and not (name = 'dtproperties') and not (name like 'dt%' and type = 'P')

SELECT * FROM Database.dbo.sysusers WHERE name = 'sysdba'

Both servers have the same data, except different sid.

Changed query, saw same behavior as before.

I also tried uninstall/reinstall. No effect. Why would it not attempt to execute the Objects query?

I am pretty sure it makes an attempt to execute that query first time. Please note that it uses internal caching to improve the performance. Queries are not run before each popup, they are run only if the data in the cache is old or not available. That possible explains why you don't see the query after the initial connection.

Is that a SQL 2000 or 2005?

2005. I closed all query windows and closed sql assist. I opened a trace, then opened SQL Assist, then opened a query window. Then typed:
select * from sysdba.

Here are the queries (some may be from SQL Assist, some from Management Studio):

Code:

SELECT SYSTEM_USER

SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON

select @@spid select SERVERPROPERTY('ProductLevel')

SET NOEXEC, PARSEONLY, FMTONLY OFF

SET SHOWPLAN_TEXT OFF

SET SHOWPLAN_ALL OFF

SET SHOWPLAN_XML OFF

select serverproperty('ServerName'), system_user, db_name(), user_name()

select name, 'DB'
from master.dbo.sysdatabases

if @@version like 'Microsoft SQL Server 2005%'
select
   name collate Latin1_General_CI_AS, 'SC', schema_id
from
   [Database].sys.schemas
where
   schema_id <16384 then 'SR' else 'SC' end,
   cast(uid as int)
from
   [Database].dbo.sysusers
where
   issqluser = 1 or isntname = 1
or issqlrole = 1 or isapprole = 1

union all
select name collate Latin1_General_CI_AS, 'SL', 0
from master.dbo.syslogins

exec sp_executesql N'select
name, xtype, id
from
[Database].dbo.sysobjects
where
xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'')
and uid = @b0
and not (name = ''dtproperties'')
and not (name like ''dt%'' and xtype = ''P'')
',N'@b0 int',@b0=1

select serverproperty('ServerName'), system_user, db_name(), user_name()

select serverproperty('ServerName'), system_user, db_name(), user_name()

All the queries return what appears to be proper data, except for the sp_executesql. It is using the id for dbo, however I'm using a different schema. Changing the query resulting in this:

	Code:
	exec sp_executesql N'select name, xtype, id from [SalesLogix].dbo.sysobjects where xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'') and (uid = @b0 or uid = 5) and not (name = ''dtproperties'') and not (name like ''dt%'' and xtype = ''P'') ',N'@b0 int',@b0=1

Did not populate the empty popup window.

Have you tried changing the query to the following?

	Code:
	select name, type, object_id from /db./sys.all_objects where type in ('U','S','V','P','X','RF','FN','TF','IF') and schema_id = :SCHEMA_ID and not (name = 'dtproperties') and not (name like 'dt%' and type = 'P')

Here you can go by schema id or by principal id

	Code:
	select name, type, object_id from /db./sys.all_objects where type in ('U','S','V','P','X','RF','FN','TF','IF') and principal_id = :SCHEMA_ID and not (name = 'dtproperties') and not (name like 'dt%' and type = 'P')

One of them should return the expected results and populate the object list properly

First query runs fine on both servers. The second one does not produce any data on either server because principal_id is null.