SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
MS SQL Server Management Studio 2005 problem

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
MS SQL Server Management Studio 2005 problem
Author Message
StepSON



Joined: 13 Feb 2007
Posts: 4
Country: Russia

Post MS SQL Server Management Studio 2005 problem Reply with quote
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?
Tue Feb 13, 2007 2:38 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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.
Tue Feb 13, 2007 5:18 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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
Tue Feb 13, 2007 11:31 pm View user's profile Send private message
StepSON



Joined: 13 Feb 2007
Posts: 4
Country: Russia

Post Reply with quote


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).
Wed Feb 14, 2007 3:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Sorry. I ran out of ideas. I am escalating this issue and sending it to development team for further investigation.
Wed Feb 14, 2007 9:25 am View user's profile Send private message
StepSON



Joined: 13 Feb 2007
Posts: 4
Country: Russia

Post Reply with quote
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
Wed Feb 14, 2007 10:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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.
Wed Feb 14, 2007 12:41 pm View user's profile Send private message
StepSON



Joined: 13 Feb 2007
Posts: 4
Country: Russia

Post Reply with quote
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
Thu Feb 15, 2007 4:08 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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.
Thu Feb 15, 2007 9:41 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.