MS SQL Default Schema Error

[expired user #9044]'s profile image [expired user #9044] posted 8 years ago in General Permalink
With MS SQL Server, instead of typing DB_NAME.SCHEMA_NAME.TABLE_NAME, you can type DB_NAME..TABLE_NAME (two dots), which will just use the default schema name. When typing a table name out like this, an error occurs upon typing the second dot.
[expired user #8144]'s profile image [expired user #8144] posted 8 years ago Permalink
Actually I get this error also just by trying to browse any table (Data tab) in one database, where HS generates the SQLs on its own. See the sequence of SQLs HS generates:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='TEST' AND TABLE_NAME='TABLE';
SELECT c.name AS "column", prop.value AS "comment" FROM sys.extended_properties AS prop INNER JOIN sys.all_objects o ON prop.major_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.columns AS c ON prop.major_id = c.object_id AND prop.minor_id = c.column_id WHERE   prop.name='MS_Description'  AND s.name=''  AND o.name='TABLE';
SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, K.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON   C.CONSTRAINT_NAME = K.CONSTRAINT_NAME   AND K.TABLE_NAME='TABLE'   AND K.TABLE_CATALOG='TEST' WHERE C.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') ORDER BY K.ORDINAL_POSITION;
SELECT TOP 1000 * FROM "TEST".."TABLE";
/* SQL Error (208): Invalid object name 'TEST..TABLE'. */


2 differences I notice from other MSSQLs I use HS with:
- the table tree (left) shows usually schema.table - on the failing one, only table name
- on the failing one, the schema name happens to be the same as the DB name

Please login to leave a reply, or register at first.