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

Welcome Forums General PowerShell Q&A Cannot execute SQL query (“USE statement is not supported”) on Azure SQL DB

Viewing 4 reply threads
  • Author
    Posts
    • #243383
      Participant
      Topics: 10
      Replies: 9
      Points: 139
      Rank: Participant

      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…. 🙁

       

    • #243464
      Participant
      Topics: 0
      Replies: 1
      Points: 27
      Rank: 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

       

    • #243632
      Participant
      Topics: 10
      Replies: 9
      Points: 139
      Rank: Participant

      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!

    • #243671
      Participant
      Topics: 15
      Replies: 1776
      Points: 3,218
      Helping Hand
      Rank: 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.
    • #243707
      Participant
      Topics: 10
      Replies: 9
      Points: 139
      Rank: Participant

      Thank you RobS :- ) You really saved me!

Viewing 4 reply threads
  • You must be logged in to reply to this topic.