SQL Assisntant Auto Complete and MS SQL Server

Hello.
I have SQL Assistant 6.3.171 and MS SQL Server 2008R2.
I set "With schema name" for "Always Fully Qualify Object Names" option.
For auto complete SQL Assistant always use schema name which set as default schema for current user.(even db object doesn't exist in default user schema)
Why SQL Assistant doesn't get schema name of current db object?
How can I change it?

I assume you are using SQL Assistant with SQL Server Management Studio 2008 (SSMS here and later)

Let's check that you have changed "qualify" settings in the correct place.
1. In SSMS, right-click in the editor and choose SQL Assistant -> Options menu
2. Click "DB Options" tab, then select "SQL Server" assistance type on the left (this is the second item)
3. On the right, expand the "Auto Complete" group of options
4. Set "Always Fully Qualify Object Names" option as required and then click Ok.

Try entering some query and check if that works.

Assuming it does work, restart SSMS, repeat steps 1 to 3. Verify the value for the option is still the same. If not exist SSMS, and start it in Run As Administrator mode. Repeat steps 1 to 4 again.

Hello.
This is my settings

For example:
I have user test.

This is my test database

I type "select * from" and call SQL Assistance context list

Why Sql Assistance doesn't show all table from all schemes?
After that I select ErrorLog table and press enter and I get

Table test.errorlog doen't exists in test db.
How can I change it?

Content of SQL Assistant popups and dialogs is filled from DB Queries which you can see on DB Options tab. What these queries return depends on your connection context. Basically, if you connect as user test, you will see only the things that the database server makes visible to user test. Additionally. If the user doesn't have permissions to query all system catalog tables and use schema_name() function name, then by default SQL Assistant will user's user's name as schema name instead of nulls.

you can easily test the above. Connect to your server as user test.
Copy "Context (MSSQL)" query text to SSMS and execute it. check the results and see if they are correct. If not, fix user permissions.
Next, do the same for "Schemas (MSSQL)" query, replacing in the query text $DB_NAME$ with AdventureWorks
Lastly, try the same for "Objects (MSSQL) + Typed Synonyms" replaceing $DB_NAME, and also replacing :SCHEMA_ID with the number returned from the "Schemas (MSSQL)" query.

The results should match content of SQL Assistant's popups and behavior.

As You can see(screenshot 2 from my previous post ) user test is db_owner and he has all permission for all objects in all schemes in this database.
As I see in SQL Profiler, when I call context menu with objects list, SQL Assistant execute once “Context” query(return default user scheme - in this case test).After that Assistant execute “Objects” query twice for dbo and test schemes. Why SQL Assistant did execute only for 2 schemes? “Schemas ” query retunes all schemes in database.
I don’t understand why SQL Assistant does use default users schema for auto complete, why he doesn’t get scheme of selected object?

Please execute the queries I asked to execute in the previous post. they will provide answers to your questions and help us with troubleshooting the issue.

Hello.
This is "Context" with result

Code:

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
EXEC ('select
SERVERPROPERTY(''ServerName''),
SYSTEM_USER,
DB_NAME(),
ISNULL(SCHEMA_NAME(), ''dbo'')')
else
EXEC ('select
SERVERPROPERTY(''ServerName''),
SYSTEM_USER,
DB_NAME(),
ISNULL(USER_NAME(), ''dbo'')')

****\SQL2008R2 test AdventureWorks test

This is "Schemas" query with result

Code:

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

This is "Object" query with result for several schemas

Code:

declare @SCHEMA_ID int ;

set @SCHEMA_ID = schema_id ( 'dbo' );

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
select name
,type
,object_id
,create_date
,modify_date
from AdventureWorks.sys.all_objects
where type in ('U'
,'S'
,'V'
,'P'
,'X'
,'RF'
,'FN'
,'TF'
,'IF'
,'SN'
,'PC'
,'FT'
,'FS'
,'TR')
and (
schema_id = @SCHEMA_ID
or (
1 = @SCHEMA_ID
and user_name() = 'dbo'
and schema_id = 4
and name in ('sysobjects'
,'sysindexes'
,'syscolumns'
,'systypes'
,'syscomments'
,'sysfiles1'
,'syspermissions'
,'sysusers'
,'sysproperties'
,'sysdepends'
,'sysreferences'
,'sysfulltextcatalogs'
,'sysindexkeys'
,'sysforeignkeys'
,'sysmembers'
,'sysprotects'
,'sysfulltextnotify'
,'sysfiles'
,'sysfilegroups')
)
)
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and type = 'P' )
else
select name
,xtype
,id
,crdate
,crdate
from AdventureWorks.dbo.sysobjects
where xtype in ( 'U' ,'S' ,'V' ,'P' ,'X' ,'RF' ,'FN' ,'TF' ,'IF' ,'TR' )
and uid = @SCHEMA_ID
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and xtype = 'P' )

/*
*
*/

set @SCHEMA_ID = schema_id ( 'test' );

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
select name
,type
,object_id
,create_date
,modify_date
from AdventureWorks.sys.all_objects
where type in ('U'
,'S'
,'V'
,'P'
,'X'
,'RF'
,'FN'
,'TF'
,'IF'
,'SN'
,'PC'
,'FT'
,'FS'
,'TR')
and (
schema_id = @SCHEMA_ID
or (
1 = @SCHEMA_ID
and user_name() = 'dbo'
and schema_id = 4
and name in ('sysobjects'
,'sysindexes'
,'syscolumns'
,'systypes'
,'syscomments'
,'sysfiles1'
,'syspermissions'
,'sysusers'
,'sysproperties'
,'sysdepends'
,'sysreferences'
,'sysfulltextcatalogs'
,'sysindexkeys'
,'sysforeignkeys'
,'sysmembers'
,'sysprotects'
,'sysfulltextnotify'
,'sysfiles'
,'sysfilegroups')
)
)
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and type = 'P' )
else
select name
,xtype
,id
,crdate
,crdate
from AdventureWorks.dbo.sysobjects
where xtype in ( 'U' ,'S' ,'V' ,'P' ,'X' ,'RF' ,'FN' ,'TF' ,'IF' ,'TR' )
and uid = @SCHEMA_ID
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and xtype = 'P' )

/*
*
*/

set @SCHEMA_ID = schema_id ( 'Production' );

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
select name
,type
,object_id
,create_date
,modify_date
from AdventureWorks.sys.all_objects
where type in ('U'
,'S'
,'V'
,'P'
,'X'
,'RF'
,'FN'
,'TF'
,'IF'
,'SN'
,'PC'
,'FT'
,'FS'
,'TR')
and (
schema_id = @SCHEMA_ID
or (
1 = @SCHEMA_ID
and user_name() = 'dbo'
and schema_id = 4
and name in ('sysobjects'
,'sysindexes'
,'syscolumns'
,'systypes'
,'syscomments'
,'sysfiles1'
,'syspermissions'
,'sysusers'
,'sysproperties'
,'sysdepends'
,'sysreferences'
,'sysfulltextcatalogs'
,'sysindexkeys'
,'sysforeignkeys'
,'sysmembers'
,'sysprotects'
,'sysfulltextnotify'
,'sysfiles'
,'sysfilegroups')
)
)
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and type = 'P' )
else
select name
,xtype
,id
,crdate
,crdate
from AdventureWorks.dbo.sysobjects
where xtype in ( 'U' ,'S' ,'V' ,'P' ,'X' ,'RF' ,'FN' ,'TF' ,'IF' ,'TR' )
and uid = @SCHEMA_ID
and not ( name = 'dtproperties' )
and not ( name like 'dt%' and xtype = 'P' )

/*and so on*/

The "Context" query returns
-----------------------------------
current user = test
current user's schema = test
------------------------------------
this seems to be correct

The "Schemas" query also returns "test" schema, which is good too.

The "Objects" query for the test schema returns just one table UserTestTable, which seems to be correct too. And it correctly returns ErrorLog table for the "dbo" schema.

So all queries seem to return the expected results. However, they don't match the content of the popup on your screenshot. In the popup list for objects, only objects in the users's current schema appear in the list at the top and without schema name. Objects in other schemas appear folded in the schema level (see the attached screenshot).

In the current execution context from SQL Assistant's point of view, your current schema is dbo, not test as returned by the Context query and expected by SQL Assistant. and that's why when you select ErrorLog it is inserted as test.ErrorLog, not as dbo.ErrorLog.

Please check your SSMS settings or queries you are executing. somehow your current schema changes to dbo behind the scenes and SQL Assistant is not aware of that change. If thinks you are still in the test schema.

I also noticed that you are using old catalog queries from version 6.2 or older. I strongly recommend that you reload your SQL Assistant settings using Import/Export in the Options dialog and then picking the default settings.

My SQL Assistant version is 6.3.171.I haven’t changed anything in db scripts.
I reloaded my settings using Import/Export to default.Unfortunately, I still get this problem. As I wrote SQL Assistant get object from default user schema(test) and dbo, and think these objects from test schema.

How I can see there is no solution for my problem...

I suggest to test the same type of login with a different editor. For example, with Notepad, enabling Notepad as a target in SQL Assistant settings. This is to exclude SQL Server Management Studio as a factor and ensure it is not the target environment that is interfering somehow. Also, with Notepad, you can try a different connection type, for example ODBC, instead of ADO. This way all other external factors can be excluded 1 by 1 until we figure out what's causing the issue.