- This topic has 4 replies, 3 voices, and was last updated 2 months, 1 week ago by
July 18, 2020 at 3:33 pm #243383
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
Write-Host(“Adding source to database $AMB_DB_NAME”)
$query = @”
INSERT INTO system VALUES (‘$item’, NULL, NULL);
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…. 🙁
July 19, 2020 at 2:23 am #243464ParticipantTopics: 0Replies: 1Points: 27Rank: Member
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
- This reply was modified 2 months, 1 week ago by Rob SQLDBAWithABeard Sewell.
July 20, 2020 at 5:27 am #243632
July 20, 2020 at 11:19 am #243671ParticipantTopics: 15Replies: 1776Points: 3,218Rank: Community Hero
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.
- This reply was modified 2 months, 1 week ago by Rob Simmers.
July 20, 2020 at 2:42 pm #243707
Thank you RobS :- ) You really saved me!
- You must be logged in to reply to this topic.