Author Posts

August 4, 2016 at 9:13 am

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... 🙂

August 4, 2016 at 9:38 am

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

August 4, 2016 at 10:12 am

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

August 4, 2016 at 10:21 am

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

August 4, 2016 at 10:34 am

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. 🙂

August 4, 2016 at 10:38 am

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

Happy coding,
Daniel

March 21, 2018 at 1:34 pm

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

March 21, 2018 at 2:42 pm

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