Cannot execute SQL query ("USE statement is not supported") on Azure SQL DB

I have really big issue which I am not able to solve and did not find proper solution on web. I need to execute INSERT statement from PowerShell script into SQL database.

On regular SQL Server Database this is executed just fine and the row is inserted properly. This is my code:
Install-Module -Name SqlServer -Force
push-location
Import-Module SqlServer
pop-location
Write-Host(“Adding source to database $AMB_DB_NAME”)
$query = @"
USE $AMB_DB_NAME
INSERT INTO system VALUES (’$item’, NULL, NULL);
GO
"@
Invoke-Sqlcmd -Query $query -ServerInstance $SQL_SERVER_NAME -Username $DB_ADMIN_USER -Password $DB_ADMIN_PASS

If I am targeting the database which is being installed on Azure then I am getting following ERROR:

“USE statement is not supported to switch between databases. Use a new connection to connect to a different database.”

I am not even switching between the databases in my code. All my code related to database is what I wrote above. For connection I just state SQL SERVER name, and credentials and that it is it. I do not need any connections strings or anything since PowerShell is smart enough to handle this.

I saw that this issue is troubling many, many people on the web… Many are facing the same issue… https://github.com/HeidiSQL/HeidiSQL/issues/188

but no one propose concrete solution how to execute this. Please I will really need if someone have time to write me the real solution since I could not find it at all really, from the PowerShell perspective of course.

Thank you in advance for concrete example how to execute the same INSERT command for Azure SQL since I really do not have any clue… :frowning:

 

If you are querying Azure SQL Database, connect to the database name specifically in your Invoke-SqlCmd call ( in general, this is the better way anyway)

Invoke-Sqlcmd -Query $query -ServerInstance $SQL_SERVER_NAME -Database §AMB_DB_NAME -Username $DB_ADMIN_USER -Password $DB_ADMIN_PASS

You will also need to remove the use statement from the query

 

Hi @Rob SQLDBAWithABeard Sewell

can you please tell me in that case how I should define QUERY (with GO statement at the end or without?)

$query = @”
INSERT INTO system VALUES (‘$item’, NULL, NULL);
GO
“@

Thank you!

USE and GO are both for SQL Management Studio or commands for readability, but should not\cannot be used in commands from outside SQL tools:

SQL Server provides commands that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

Thank you RobS :- ) You really saved me!