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.

Tue Oct 02, 2007 3:21 pm

judahr

Joined: 09 Mar 2007Posts: 314Country: United States

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.

Tue Oct 02, 2007 4:12 pm

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6608

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

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:

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

Thu Oct 04, 2007 3:31 pm

judahr

Joined: 09 Mar 2007Posts: 314Country: United States

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 ?

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

Fri Oct 05, 2007 10:54 am

SysTema

Joined: 01 Dec 2006Posts: 3

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

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
)