SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
No assistance provided for one server
Goto page Previous  1, 2, 3  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
No assistance provided for one server
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Copy the first query to SQL Assistant settings replacing the existing query for SQL Server (Objects). Hope this helps.
Tue Oct 02, 2007 12:04 pm View user's profile Send private message
judahr



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

Post Reply with quote
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.
Tue Oct 02, 2007 12:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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 View user's profile Send private message
judahr



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

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Did you get a chance to install it into a different directory? Did it resolve the issue?
Wed Oct 03, 2007 8:12 am View user's profile Send private message
judahr



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

Post Reply with quote
Yes, I tried it. No resolution.
Wed Oct 03, 2007 8:20 am View user's profile Send private message
judahr



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

Post Reply with quote
More information:

Select FROM sysdba.
closing the empty box then doing ctrl-space produces box with functions, databases, and schemas.
Thu Oct 04, 2007 1:48 pm View user's profile Send private message
judahr



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

Post Reply with quote
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()

Thu Oct 04, 2007 2:18 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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 ?
Thu Oct 04, 2007 3:31 pm View user's profile Send private message
judahr



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

Post Reply with quote
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)
Thu Oct 04, 2007 4:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Thanks for the info. I am sending it to the development team.
Thu Oct 04, 2007 5:40 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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 View user's profile Send private message
SysTema



Joined: 01 Dec 2006
Posts: 3

Post Reply with quote
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
Fri Oct 05, 2007 3:27 pm View user's profile Send private message
judahr



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

Post Reply with quote
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

Mon Oct 08, 2007 8:10 am View user's profile Send private message
judahr



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

Post Reply with quote
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
Mon Oct 08, 2007 8:12 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
Goto page Previous  1, 2, 3  Next
Page 2 of 3

 
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.