distal-attribute
distal-attribute
distal-attribute
distal-attribute

HeidiSQL bug with regard to MS SQL Server?

panofish posted 2 years ago in HeidiSQL portable

I am getting an error in HeidiSQL. I am using the latest version of HeidiSQL and this error has existed for over a year now. I am able to successfully connect to a mssql server.

However, selecting a table and the "data" tab, I get the error:

  • SQL Error (208): Invalid object name 'ARK..VehicleAssets'.

I can get a query to work if I fully qualify like this:

  • select * from Ark.Pipeline.ApplicationLinks;

In this image you can see what the schema names look like in Microsoft SQL Server Management Studio vs HeidiSQL.

Thanks for the great software Ansgar!

panofish posted 2 years ago

bump :)

panofish posted 2 years ago

bump ? :)

ansgar posted 2 years ago

I already tested that here and I could not reproduce that. Probably I need to create a new schema first?

On which MSSQL version are you?

panofish posted 2 years ago

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (X64) Jul 9 2014 15:59:57 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

panofish posted 2 years ago

I'm not an SQL Server expert, but it seems in my example above.. the database called Ark uses several custom schemas like "Pipeline", "VehicleAsset"... etc. Apparently, dbo is the default schema in SQL Server, but users can create their own schemas to allow them to better manage the object namespace.

Perhaps HeidiSQL is only referencing the default dbo schema and is not interrogating all database schemas?

Thanks for your time Ansgar, but I really would like to use HeidiSQL with SQL Server database. I do not like Microsoft's tool "SQL Server Management Studio".

cibiz posted 2 years ago

Hi @ansgar,

I also facing this kind-of-same problem. Really hope that you can help fix this.

I think the problem came from the OWNERS of table in Database. In my case, please refer attachment below.

You can see that the full schema is:

IJPNCMN.IJPN.staff_session

In this IJPNCMN database, I'm using IJPN as owner of table staff_session. The "Grid editing error" shows that MISSING owners of IJPN in this case. Which is i assume that it looking for .dbo as default owner of the table.

IJPNCMN.??.staff_session

I'm using MSSQL Server 2008 R2. Hope this helps. Great software Ansgr. Love it! :)

2 attachment(s):
  • heidisql1
  • heidisql2
matsg posted 1 year ago

Same here, it's especially troubling since we're migrating towards grouping all of our tables in schemas (domains). The "dbo" schema is always visible, but any other schema name seems to disappear; so I always have to type in a query instead.

And then I can't edit the query either, because HeidiSQL tries to look up the table by just the table name, and it's always missing the schema... (once again, "dbo" always works for some reason)

alanlilly posted 1 year ago

I am also seeing this problem. The schema is not included to fully qualify the table.

It should show my table as dbname.schemaname.tablename, but I am only seeing dbname.tablename If I do the following query, I get the correct results.

select * from dbname.schemaname.tablename;

Tables that use the default schema of 'dbo' appear to work correctly.

I hope this can be fixed soon. Thanks.

yan posted 1 year ago

Same here. I had experienced the issue in earlier versions but had turned to other data browsers when that happened. I do confirm that the issue only manifests when table_schema is not dbo:

  • if it is dbo, HS uses a proper select * from table_catalog.dbo.table_name
  • if schema is not dbo, HS issues a select * from table_schema..table_name, which errors out.

May be unrelated but I have noted in the query logs that HS issues different queries on the information_schema:

  • first case, SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='xxx';
  • second case, SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='yyyy' AND TABLE_NAME='xxx';

Both could return the column details and I don't see the rationale for a different criteria, but regardless the query is incorrect as the records returned could not be unique: first case, if 2 tables with same name but in different catalog, query would return the columns of both columns combined. Same in the other scenario, if 2 tables with same name exist in different schema.

@Ansgr - you could easily reproduce this if you create a schema that is different from 'dbo'.

Note I use SQL 2016 (13.0.1711.0) but the exist in all MSSQL versions.

yan posted 1 year ago

in my comment pls read "...the columns of both tables combined."

Also, I am not using Portable version so the issue exists with core HS.

panofish posted 1 year ago

This issue still exists under portable and core HeidiSQL. I hope Ansgar can find the time to work on this issue. Resolving this will enable our small company to use HeidiSQL with Microsoft SQL and it would be greatly appreciated. I certainly would do my best to get my company to make a donation :)

panofish posted 1 year ago

Sorry Ansgar, just tested this again.. and it appears to work properly now. Not sure if this was fixed by Ansgar OR if something was recently reconfigured on our server so that it works now. ???

panofish posted 6 days ago

I finally found the work around and a stronger hint concerning this bug in HeidiSQL.
When I connect to the SQL Server by TCP/IP windows authentication without specifying a "Database", then I see the above problem. However, if I specify a specific "Database" during connection, the problem mentioned above does not occur. Not sure why that changes the logic, but that clearly indicates a bug in the connection logic for Microsoft SQL Server (TCP/IP). This bug still exists as of HeidiSQL v9.4.0.5186.

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