MS SQL Server Management Studio 2005 problem

I try to use SQL Assistant (v.1.0.68) first time and can't resolve the following problem - Assistant works only if current database is one of the system databases (master, temp...). If I select from the 'Available databases' combo-box any of my databases, Assistant windows doesn't appeared anymore (neither automatically nor by Ctrl+Space). Is it a limitation of the free version or I do something wrong?

It is likely a permission issue. To verify that, copy and paste the SQL query for database objects from SQL Assistant Options dialog (see DB Options tab). Please let us know what you get when you run it.

To be more specific please try running the following in SSMS or Query Analyzer

	Code:
	use [your database name here] select name, xtype, id from dbo.sysobjects where xtype in ('U','S','V','P','X','RF','FN','TF','IF') and uid = user_id() and not (name = 'dtproperties') and not (name like 'dt%' and xtype = 'P')

Please let us know what you get

The both queries work fine (see picture), the first one returns correct list of my databases, the second query - list of properties. I don't think that it may be related with permissions - I connect to server as 'sa' (also I've tried to connect with Windows Authentication, my Windows Login has 'sysadmin' role - thу same result).

Sorry. I ran out of ideas. I am escalating this issue and sending it to development team for further investigation.

Probably the following information may be useful:

SQL Server 9.0.2047
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Hi,

Thanks for the info. We have pre-release build 1.0.69 copied to the web server. Please give it a try and let us know if that build is working for you.

http://www.softtreetech.com/download/sqlassist_setup.69.exe

Please note, this is a pre-release file and it can change at any time or be renamed or deleted, we are still running some QA tests.

Hi,

build 1.0.69 doesn’t work too - now SQL Assistant window appears on each databases, but doesn't show any content of user databases (see picture).

But now, I suppose, I've found the solution. The problem is my databases have collating order different from the server's collating order (e.g. Cyrillic_General_CS_AI for server and Cyrillic_General_CI_AS for databases). As a result your 'Schemas' query returns error ''Cannot resolve collation conflict for column 1 in SELECT statement." (I am not sure, why it exactly happened - all system views from this UNION located in the same system database and have the same collating order, but it’s a fact). I've re-write this query with using a temporary table (I hope you can suggest the better solution, it's just for test) - now all works fine!

declare @tmp as table (name nvarchar(128), type char(2), principal_id int)

if @@version like 'Microsoft SQL Server 2005%'

begin
insert into @tmp (name, type, principal_id)
select
name,
case when type in ('S','U','G') then 'SC' else 'SR' end,
principal_id
from
/*db.*/sys.database_principals
where
type in ('S','U','G','A','R')

insert into @tmp (name, type, principal_id)
select
name,
case when type = 'R' then 'SR' else 'SL' end,
principal_id
from
/*db.*/sys.server_principals
where
type in ('S','U','G','R')

end
else

insert into @tmp (name, type, principal_id)
select
name,
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

insert into @tmp (name, type, principal_id)
select name, 'SL', 0
from master.dbo.syslogins

select
name,
type,
principal_id
from
@tmp

Thanks for the new info. It makes sense now. The solution for this is described in http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=21495. Please see the last reply.