![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
StepSON
Joined: 13 Feb 2007 Posts: 4 Country: Russia |
|
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?
|
|
Tue Feb 13, 2007 2:38 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
To be more specific please try running the following in SSMS or Query Analyzer
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
StepSON
Joined: 13 Feb 2007 Posts: 4 Country: Russia |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
StepSON
Joined: 13 Feb 2007 Posts: 4 Country: Russia |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
StepSON
Joined: 13 Feb 2007 Posts: 4 Country: Russia |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
Thu Feb 15, 2007 9:41 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
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
|
|
|