SQL and Powershell

PowerShell Help
1

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… :slight_smile:

2

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

3

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
4

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

5

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. :slight_smile:

6

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

Happy coding,
Daniel

7

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

8

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()