SQL and Powershell

Welcome Forums General PowerShell Q&A SQL and Powershell

Viewing 7 reply threads
  • Author
    Posts
    • #49234
      Participant
      Topics: 1
      Replies: 2
      Points: 0
      Rank: Member

      I have made a script to insert data into sql tabel. ( Azure pack billing)

      #Connect to the SQL database
      $conn = New-Object System.Data.SqlClient.SqlConnection
      $conn.ConnectionString = "Server=SQLServer\SQLServer;Database=Microsoft.MgmtSvc.Usage;trusted_connection=true;"
      $Conn.Open()
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Conn

      $insert_stmt = "INSERT INTO [Microsoft.MgmtSvc.Usage].[dbo].[usage.ExtIp] (IP,OWNER) VALUES (1,2);"
      ## Create your command
      $cmd = $conn.CreateCommand()
      $cmd.CommandText = $insert_stmt

      ## Invoke the Insert statement
      $cmd.ExecuteNonQuery()
      $Conn.Close()

      It works

      If then i made a script there look like this it fails please help.

      $ip = "10.10.10.151"
      $Owner = "xxxx@xxxxx.com"

      #Connect to the SQL database
      $conn = New-Object System.Data.SqlClient.SqlConnection
      $conn.ConnectionString = "Server=SQLServer\SQLServer;Database=Microsoft.MgmtSvc.Usage;trusted_connection=true;"
      $Conn.Open()
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Conn

      $insert_stmt = "INSERT INTO [Microsoft.MgmtSvc.Usage].[dbo].[usage.ExtIp] (IP,OWNER) VALUES ($ip,$Owner);"
      ## Create your command
      $cmd = $conn.CreateCommand()
      $cmd.CommandText = $insert_stmt

      ## Invoke the Insert statement
      $cmd.ExecuteNonQuery()
      $Conn.Close()

      ERROR:

      Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '.4'."
      At line:19 char:1
      + $cmd.ExecuteNonQuery()
      + ~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException

      Please help... 🙂

    • #49242
      Moderator
      Topics: 2
      Replies: 525
      Points: 24
      Team Member
      Rank: Member

      Hi,

      Your INSERT statement is invalid because IP and owner are strings which need to be surrounded by single quotes.

      Try one of below statements instead.

      $insert_stmt = "INSERT INTO [Microsoft.MgmtSvc.Usage].[dbo].[usage.ExtIp] (IP,OWNER) VALUES ('$ip','$Owner');"
      
      $insert_stmt = "INSERT INTO [Microsoft.MgmtSvc.Usage].[dbo].[usage.ExtIp] (IP,OWNER) VALUES ('{0}','{1}');" -f $ip, $Owner
      

      I hope that helps. Please let us know if you have any further questions. We are happy to help.

      Cheers
      Daniel

    • #49257
      Participant
      Topics: 1
      Replies: 2
      Points: 0
      Rank: Member

      Thx Daniel.

      I am getting this error now..

      Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.
      The statement has been terminated."
      At line:14 char:1
      + $cmd.ExecuteNonQuery()
      + ~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException

    • #49259
      Moderator
      Topics: 2
      Replies: 525
      Points: 24
      Team Member
      Rank: Member

      Looks like one of the fields (IP and OWNER) is not long enough to accept the value provided in the INSERT statement. My guess is that the IP field only accepts an IP address as integer or without the dots.

      Please could you post the table schema here to verify. I don't have access to this kind of database and couldn't find the table schema online.

      Thanks
      Daniel

    • #49261
      Participant
      Topics: 1
      Replies: 2
      Points: 0
      Rank: Member

      Hi Daniel

      I have find out that the lengt was set to 10, now i have set it to 80 and it works.

      1000000 thanks for your help. 🙂

    • #49263
      Moderator
      Topics: 2
      Replies: 525
      Points: 24
      Team Member
      Rank: Member

      Thanks very much for the confirmation. I'm glad that it is working for you now.

      Happy coding,
      Daniel

    • #96624
      Participant
      Topics: 0
      Replies: 1
      Points: 0
      Rank: Member

      I also had issue in datatype this forum helped me, thanks

    • #96636
      Participant
      Topics: 4
      Replies: 95
      Points: 192
      Helping Hand
      Rank: Participant

      Hi,

      You got it resolved but i'll still throw my one cent.
      I haven't put data that much to tables but I've read some and learned that using @" "@ for the queries is my option number one.
      It is a lot easier to format the query in that way.

      $ip = "10.10.10.151"
      $Owner = "xxxx@xxxxx.com"
      
      $insert_stmt = @"
      INSERT INTO [Microsoft.MgmtSvc.Usage].[dbo].[usage.ExtIp] (IP,OWNER) VALUES ('$ip','$Owner');
      "@
      
      
      #Connect to the SQL database
      $conn = New-Object System.Data.SqlClient.SqlConnection
      $conn.ConnectionString = "Server=SQLServer\SQLServer;Database=Microsoft.MgmtSvc.Usage;trusted_connection=true;"
      $Conn.Open()
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Conn
      
      ## Create your command
      $cmd = $conn.CreateCommand()
      $cmd.CommandText = $insert_stmt
      
      ## Invoke the Insert statement
      $cmd.ExecuteNonQuery()
      $Conn.Close()
      
Viewing 7 reply threads
  • The topic ‘SQL and Powershell’ is closed to new replies.