Heidisql: MSSQL azure DB: SQL Error (40508): USE statement is not supported to switch between databases.

[okke-formsma]

Steps to reproduce this issue

1. Connect to an azure database database using 'master'
2. Try to access another database on the server (e.g. "other_database")

3. Error message:

   USE "other_database";
   /* SQL Error (40508): USE statement is not supported to switch between databases. Use a new connection to connect to a different database. */

Current behavior

Exception 40508 is thrown

Expected behavior

To access the 'other_database'

Possible solution

Don't use 'USE' statements when connecting to azure mssql databases.

Environment

Azure hosted mssql database

• HeidiSQL version:
  9.5.0.5261

• Database system and version:
  SQL Server 12.0.2000.8 (hosted by Azure)

[ansgarbecker]

For servers not supporting that USE statement, you just need to specify the database before connecting, in the session settings. Set "Database(s):" to the one db you want to connect to and retry.

Related commits:

• 50f66662ab169de9ac44c9c066f8ce2b0b1e3edd
• 34f70eaf08c2e04a1f551464c3984e644d4f4f34

[okke-formsma]

Thanks for the quick reply Ansgar.

The workaround functions when using a single database. I'm often using multiple databases on a single server and it's nice to be able to quickly switch between them. Specifying more than one will break with another error. I could provide the specific error tomorrow if you'd like.

[Norm-RR]

This is still a bug.

I'm evaluating HeidiSQL as admin tool to manage our Azure SQL Managed Instance, instead of MS Visual Studio, but this bug is a show stopper.

The workaround is not practical, I connect to multiple servers housing multiple databases for different projects. One server has 15 databases, opening 15 sessions is just ridiculous.

Are you going to reopen this bug and fix it? Azure databases is not going away .

[ansgarbecker]

I am willing to solve this, if someone just posts some practical solution. If there is no support for USE, how should the client tell the server in which database its queries should be fired?

[Norm-RR]

I'll do some research and see if I can come with a long term solution for you to implement. Thanks for responding!

[lukinhaspm]

I'm a HUGE Heidi fan for years, but somethings is better when we use the official tools, unfortunately, SQL Azure is different than SQL Server.
When we use Management Studio, we needs to click in database and ask for new query window, internaly he changes the database, but I don't know how, its very anoing. Probably internaly, Management Studio open one new connection to handle this.

[ansgarbecker]

HeidiSQL uses the MS OLE DB driver to connect to SQL Server. So, I was just searching for a way to change the current database within an app, found a related forum thread and posted that question there.