When using SSMS, can you specify the database within a SQL Script?

Question

I work on multiple Azure databases, using AzureAD - Universal with MFA in SSMS.

I usually have a number of different scripts on the go, comparing results between the different databases, then an hr or so later, the connection is terminated by the server and if I try to re-run it, it tells me doesn't recognise the table, because it no longer knows which database to connect to.

Is there a way to specify the database within SSMS, if I try USE dbDocument before the script, it tells me Msg 40508, Level 16, State 1, Line 1 USE statement is not supported to switch between databases. Use a new connection to connect to a different database..

Is there any way around this, without having to right-click the db in question and hitting NEW QUERY?

Thanks, it was unlikely, just wondered if someone had a better way. Because of having to use AzureAD Universal with MFA authentication I'm limited to SSMS, unless you know any good, free clients. I was using Heidi, but that doesn't support it. — aSystemOverload
– aSystemOverload, Commented Jul 24, 2020 at 9:20

Alberto Morillo · Accepted Answer · 2020-07-25 04:08:04Z

3

Answer recommended by Microsoft Azure Collective

The USE statement is not supported on Azure SQL Database. One solution is to split your work to several different steps according to the database that you want to use, and connect to the right database for each step.

If you want to execute the same query on multiple Azure Databases then you can use one of these options:

Use horizontal partitioned databases based on shard map using the elastic database. Here you can find how to query a remote database.
You can also use elastic queries.
Or you can create your solution based on Azure Data Lake.

answered Jul 25, 2020 at 4:08

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dima · Accepted Answer · 2020-07-24 09:20:00Z

2

Looking at this MS doc, I doubt this is possible at all...

If a database other than the current database is provided, the USE statement does not switch between databases, and error code 40508 is returned. To change databases, you must directly connect to the database. The USE statement is marked as not applicable to SQL Database at the top of this page, because even though you can have the USE statement in a batch, it doesn't do anything.

answered Jul 24, 2020 at 9:20

Dima

551 silver badge8 bronze badges

3 Comments

aSystemOverload Over a year ago

Thinking about it, that is complete rubbish. You don't have to connect again, you just have to right-click the database and hit NEW QUERY. Note that I am talking about a new DB within the same Azure Server. Either way, it's plain stupid that you can't specify the database before your query.

allmhuran Over a year ago

@aSystemOverload When you right click the database and hit "new query", it opens a new connection. That's why you get a new session_id.

Dima Over a year ago

@aSystemOverload, yep, the USE implementation is rubbish. But I agree with allmhuran -- clicking "new query" does issue a new connection to DB. What you were thinking, probably, was that your connection to SQL Server stays intact, which is true !

Collectives™ on Stack Overflow

When using SSMS, can you specify the database within a SQL Script?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related