SQL and Powershell

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 4 months ago.

  • Author
    Posts
  • #49234
    Profile photo of Søren Møller
    Søren Møller
    Participant

    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
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    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
    Profile photo of Søren Møller
    Søren Møller
    Participant

    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
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    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
    Profile photo of Søren Møller
    Søren Møller
    Participant

    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
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

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

    Happy coding,
    Daniel

You must be logged in to reply to this topic.