SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
No assistance provided for one server
Goto page 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
judahr



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

Post No assistance provided for one server Reply with quote
I connect to multiple sql servers. However, one server and database just isn't providing the popup assistance the others are. I've run SQL Profiler and I don't even see the same queries being issued to the database. The objects query is not but another one asking about the servername is. Where can I look? I did try to create an explicit connection, but that didn't seem to do anything different.

Select

produces a list of databases, schemas, and functions.

Select * from Database.

produces a list of schemas

Select * from Database.dbo.

produces an empty box.
Fri Sep 28, 2007 12:46 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I am sorry, I don't exactly understand the problem. Does it work for other databases on the same server? Do you get any error messages? What about the collation settings of that database?
Fri Sep 28, 2007 12:55 pm View user's profile Send private message
judahr



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

Post Reply with quote
It works fine for other databases on the same server. No error messages popup. Collation settings are the same for all servers and databases. Certain schemas do work fine in that database. Permissions are the same (group I am in is sysadmn). Schema is setup the same on both servers.

Behavior is the same in Query Analyzer and SSMS.
Fri Sep 28, 2007 2:18 pm View user's profile Send private message
judahr



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

Post Reply with quote
Also navigating through using the window starting from Select * From allows me to see all tables within a schema as well as columns within tables.
Fri Sep 28, 2007 2:26 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
What is exactly the schema name?
Fri Sep 28, 2007 3:09 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Two more questions. Does it work in places other than FROM or SELECT parts, for example, for EXEC?

Does it work if you specify full object name including all 3 parts, like Database.dbo.SomeTable. ?
Fri Sep 28, 2007 3:56 pm View user's profile Send private message
judahr



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

Post Reply with quote
schema name is sysdba

Select
produces box with functions, databases, schemas.

Select sysdba.
produces just an empty box.

Select FROM
produces box with functions, databases, schemas.

Select FROM sysdba.
produces empty box.

Select From Database.sysdba.
produces empty box.


This is the query I see in profiler:
select serverproperty(''ServerName''), system_user, db_name(), user_name()


When using the server that works, I see this query (it never shows up on the server that isn't working):
select
name, xtype, id
from
Database.dbo.sysobjects
where
xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'')
and (uid = @P1 )
and not (name = ''dtproperties'')
and not (name like ''dt%'' and xtype = ''P'')
Fri Sep 28, 2007 4:10 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Please try comparing results of SELECT * FROM Database.dbo.sysusers WHERE name = 'sysdba' query executed on the server where you experience the anomaly with results of the same query executed on the other server(s) where it works for you. Check if on both servers it is an existing user/schema mapped to an existing logon.

In case if this is a SQL Server 2005 database, try changing the "Objects" query in SQL Assistant options to the following

Code:
select
   name, type, object_id
from
   /*db.*/sys.all_objects
where
   type in ('U','S','V','P','X','RF','FN','TF','IF')
and schema_id = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')

Mon Oct 01, 2007 3:50 pm View user's profile Send private message
judahr



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

Post Reply with quote
SELECT * FROM Database.dbo.sysusers WHERE name = 'sysdba'

Both servers have the same data, except different sid.

Changed query, saw same behavior as before.
Tue Oct 02, 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
I also tried uninstall/reinstall. No effect. Why would it not attempt to execute the Objects query?
Tue Oct 02, 2007 8:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I am pretty sure it makes an attempt to execute that query first time. Please note that it uses internal caching to improve the performance. Queries are not run before each popup, they are run only if the data in the cache is old or not available. That possible explains why you don't see the query after the initial connection.

Is that a SQL 2000 or 2005?
Tue Oct 02, 2007 9:09 am View user's profile Send private message
judahr



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

Post Reply with quote
2005. I closed all query windows and closed sql assist. I opened a trace, then opened SQL Assist, then opened a query window. Then typed:
select * from sysdba.

Here are the queries (some may be from SQL Assist, some from Management Studio):

Code:
SELECT SYSTEM_USER

SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON

select @@spid select SERVERPROPERTY('ProductLevel')

SET NOEXEC, PARSEONLY, FMTONLY OFF

SET SHOWPLAN_TEXT OFF

SET SHOWPLAN_ALL OFF

SET SHOWPLAN_XML OFF

select serverproperty('ServerName'), system_user, db_name(), user_name()

select name, 'DB'
from master.dbo.sysdatabases

if @@version like 'Microsoft SQL Server 2005%'
   select
      name collate Latin1_General_CI_AS, 'SC', schema_id
   from
      [Database].sys.schemas
   where
      schema_id <16384 then 'SR' else 'SC' end,
      cast(uid as int)
   from
      [Database].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

exec sp_executesql N'select
   name, xtype, id
from
   [Database].dbo.sysobjects
where
   xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'')
and uid = @b0       
and not (name = ''dtproperties'')
and not (name like ''dt%'' and xtype = ''P'')
',N'@b0 int',@b0=1

select serverproperty('ServerName'), system_user, db_name(), user_name()


select serverproperty('ServerName'), system_user, db_name(), user_name()

Tue Oct 02, 2007 9:56 am View user's profile Send private message
judahr



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

Post Reply with quote
All the queries return what appears to be proper data, except for the sp_executesql. It is using the id for dbo, however I'm using a different schema. Changing the query resulting in this:

Code:
exec sp_executesql N'select
   name, xtype, id
from
   [SalesLogix].dbo.sysobjects
where
   xtype in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'')
and (uid = @b0        or uid = 5)
and not (name = ''dtproperties'')
and not (name like ''dt%'' and xtype = ''P'')
',N'@b0 int',@b0=1


Did not populate the empty popup window.
Tue Oct 02, 2007 10:05 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Have you tried changing the query to the following?

Code:
select
   name, type, object_id
from
   /*db.*/sys.all_objects
where
   type in ('U','S','V','P','X','RF','FN','TF','IF')
and schema_id = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')


Here you can go by schema id or by principal id

Code:
select
   name, type, object_id
from
   /*db.*/sys.all_objects
where
   type in ('U','S','V','P','X','RF','FN','TF','IF')
and principal_id = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')



One of them should return the expected results and populate the object list properly
Tue Oct 02, 2007 10:41 am View user's profile Send private message
judahr



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

Post Reply with quote
First query runs fine on both servers. The second one does not produce any data on either server because principal_id is null.
Tue Oct 02, 2007 11:47 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 1, 2, 3  Next
Page 1 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.