![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
No assistance provided for one server |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
What is exactly the schema name?
|
|
Fri Sep 28, 2007 3:09 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
I also tried uninstall/reinstall. No effect. Why would it not attempt to execute the Objects query?
|
|
Tue Oct 02, 2007 8:28 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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):
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Have you tried changing the query to the following?
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
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 |
|
![](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
|
|
|