No assistance provided for one server

Copy the first query to SQL Assistant settings replacing the existing query for SQL Server (Objects). Hope this helps.

Tried that here: http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=21914#19260 No effect. If you have something with tracing or debug codes, I'd be happy to run it.

I forwarded this issue to the development team and got back 1 question and 1 suggestion

1. Why are you using "exec sp_executesql N'...',N'@b0 int',@b0=1"... instead of the default query that comes with SQL Assistant?

2. Try installing SQL Assistant into a separate folder and start it from there. This will ensure you are using default options. Check if you can reproduce the problem. Make sure to restart targets after you install it into a new folder and start it from there.

I'm not using executesql. I think they are using a parameterized command, which is converted to executesql by ADO/ADO.NET. The query I'm using is the default:

select
name, xtype, id
from
/*db.*/dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')

I will try the install in seperate directory, although I ensured when I uninstalled the last time that the directory was empty.

Did you get a chance to install it into a different directory? Did it resolve the issue?

Yes, I tried it. No resolution.

More information:

Select FROM sysdba.
closing the empty box then doing ctrl-space produces box with functions, databases, and schemas.

It appears that it is not selecting the proper schema. This query seems to give me the table list at the expense of the columns:

	Code:
	select name, xtype, id from /db./dbo.sysobjects where xtype in ('U','S','V','P','X','RF','FN','TF','IF') and uid = :SCHEMA_ID or uid=5 and not (name = 'dtproperties') and not (name like 'dt%' and xtype = 'P')

SQL Profiler is telling me the app is sending a SCHEMA_ID of 1 which corresponds to dbo even though a different schema is being chosen.

When attempting to get column names, the column query is not executing either. This query executes a few times:

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

Schema Id comes from the "Schemas (MSSQL)" query.

Are you sure yo are running SQL 2005 and not a preview edition of 2008? What do you get when you execute SELECT @@version ?

	SysOp wrote:
	Schema Id comes from the "Schemas (MSSQL)" query.

Yes, and the query seems to return valid data, but it seems to be picking the schemaId for the user rather than the typed one. It always picks dbo, which is correct for me as a user, but typing "From sysdba." should pick that schema id which is 5.

	SysOp wrote:
	Are you sure yo are running SQL 2005 and not a preview edition of 2008? What do you get when you execute SELECT @@version ?

Quite sure:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks for the info. I am sending it to the development team.

This appears to be a bug that requires reworking some part of the internal code. A fix for for this should be available in release 3.0. I hope we can deliver first beta by the end of this month, but cannot promise any specific date.

Last edited by SysOp on Fri Oct 05, 2007 3:40 pm; edited 1 time in total

Are there any duplicate entities (for 'sysdba'?) in the result set of the "Schemas (MSSQL)" query?

or try to replace the "Schemas (MSSQL)" query with the following

Code:

if @@version like 'Microsoft SQL Server 2005%'
select
name collate Latin1_General_CI_AS, 'SC', schema_id
from
/*db.*/sys.schemas
where
schema_id < 16384

union all
select
name collate Latin1_General_CI_AS, 'SR', principal_id
from
/*db.*/sys.database_principals dp
where
type in ('A','R')
and not exists (
select null from /*db.*/sys.schemas where schema_id < 16384 and name = dp.name
)

union all
select
name collate Latin1_General_CI_AS,
case when type = 'R' then 'SR' else 'SL' end,
principal_id
from
/*db.*/sys.server_principals sp
where
type in ('S','U','G','R')
and not exists (
select null from /*db.*/sys.schemas where schema_id < 16384 and name = sp.name
)
else
select
name collate Latin1_General_CI_AS,
case when issqlrole + isapprole > 0 then 'SR' else 'SC' end,
cast(uid as int)
from
/*db.*/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 sl
where not exists (
select null from /*db.*/dbo.sysusers where (issqluser = 1 or isntname = 1) and name = sl.name
)

Last edited by SysTema on Mon Oct 08, 2007 11:38 pm; edited 1 time in total

Can you correct this section of the query:

Code:

union all
select
name collate Latin1_General_CI_AS,
case when type = 'R' then 'SR' else 'SL' end,
principal_id
from
/*db.*/sys.server_principals sp
where
type in ('S','U','G','R')
and not exists (
select null from /*db.*/sys.schemas where schema_id <16384> 0 then 'SR' else 'SC' end,
cast(uid as int)
from
/*db.*/dbo.sysusers
where
issqluser = 1 or isntname = 1
or issqlrole = 1 or isapprole = 1

union all

	SysTema wrote:
	Are there any duplicate entities (for 'sysdba'?) in the result set of the "Schemas (MSSQL)" query?

Nothing truly duplicate. The two sysdba rows are:

sysdba SC 5
sysdba SL 266