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…