SqlDbx Forum

giuliohome

Member

Showing Schema for MS Sql Express

First of all, thank you for this cool app!
I'm sorry to disturb you with this question, but I'd appreciate your help very much.
I've used sqldbx with (Sybase and) MS Sql without any issues.
Now I'd love to access a local MS Sql Express DB, which indeed I can do, in fact I see the succesful app log

"SQLDriverConnect: DRIVER={SQL Server Native Client 11.0};SERVER=(localdb)\MSSQLLocalDB;DATABASE=ComplianceDB;APP=SqlDbx Personal;AttachDbFilename=C:\compliance\localDB\ComplianceDB.mdf;
Connected To: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    Oct 28 2016 18:17:30
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows 7 Enterprise 6.1 <X64> (Build 7601: Service Pack 1)"
and if I run a query, for example,

SELECT TOP 90 * FROM [OilPhysical].[TradeValid]
        WHERE
        CONVERT(varchar(15), DealNumber) LIKE '%' + '802' + '%'
        ORDER BY TradeDate DESC

I get the expected results! Great!
So far so good.
Now on the left of the screen, in the Tables panel, the tables are shown withoout any schema, and if I select a table, right click and click on "SELECT * ", the error is

Invalid object name 'ComplianceDB.TradeValid'.. Error 208. SQLSTATE 42S02. Severity 16. MsgState 1. Line 1.

So it is looking for ComplianceDB.TradeValid instead of ComplianceDB.OilPhysical.TradeValid

I've also noticed that - when I go the the menu Admin>Tables - the owner is NULL, e.g.

Table Name    Owner    Rows    File Group    Created
TradeValid        9819    PRIMARY    15/02/2019 12:28:47

When I run some checks like

SELECT schema_name, schema_owner
FROM information_schema.schemata

I find that all is fine and the schema has a owner

schema_name    schema_owner
OilPhysical    dbo

and the table cols belong to the schema

select  c.name as colomn_name , t.name as table_name , s.name as schema_name
from sys.columns c
   inner join  sys.tables t     on c.object_id=t.object_id
   INNER JOIN sys.schemas AS s  ON t.[schema_id] = s.[schema_id]

colomn_name    table_name    schema_name
Incoterms    TradeValid    OilPhysical
InstrumentType    TradeValid    OilPhysical
LastUpdate    TradeValid    OilPhysical

So, what am I missing and what am I supposed to do to fix the above error ?

giuliohome

Member

Re: Showing Schema for MS Sql Express

I could make it work by using an ODBC connection instead of a MS SQL! But it is annoying that I have to use the temporary pipe connection in the config

Driver ODBC di Microsoft SQL Server versione 06.01.7601

Nome origine dati: LocalExpress
Descrizione origine dati:
Server: (local)\MSSQLLocalDB
Database: ComplianceDB

What could be the underlying issue? What is the issue in the standard approach of my first post above? Maybe the driver is wrong {SQL Server Native Client 11.0} and it shouldn't be the native 11?

giuliohome

Member

Re: Showing Schema for MS Sql Express

No that driver is OK and at the moment my final solution is to use just that in the ODBC! It seems it can automatically detect the correct pipe conn, if I'm not wrong

Microsoft SQL Server Native Client Version 11.00.6518

Data Source Name: Local2Test
Data Source Description:
Server: (local)\MSSQLLocalDB
Use Integrated Security: Yes
Database: ComplianceDB

So, it looks like I've sorted it out by myself... at least I have a valid detour/workaround