# SQL and Powershell

**URL:** <https://forums.powershell.org/t/sql-and-powershell/6929>\
**Category:** PowerShell Help\
**Created:** [August 4, 2016, 3:43am UTC](https://forums.powershell.org/t/sql-and-powershell/6929 "2016-08-04T03:43:07Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![soren-moller](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@soren-moller](https://forums.powershell.org/u/soren-moller)\
**Post date:** [August 4, 2016, 3:43am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/1 "2016-08-04T03:43:07Z")

</div>

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](mailto: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()
- 

```auto
  + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  + FullyQualifiedErrorId : SqlException

```

Please help… 🙂

---

<div class="post-metadata">

**Author:** ![daniel-krebs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/daniel-krebs/32/484_2.png) [@daniel-krebs](https://forums.powershell.org/u/daniel-krebs)\
**Post date:** [August 4, 2016, 4:08am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/2 "2016-08-04T04:08:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![soren-moller](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@soren-moller](https://forums.powershell.org/u/soren-moller)\
**Post date:** [August 4, 2016, 4:42am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/3 "2016-08-04T04:42:33Z")

</div>

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

```auto
  + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  + FullyQualifiedErrorId : SqlException
```

---

<div class="post-metadata">

**Author:** ![daniel-krebs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/daniel-krebs/32/484_2.png) [@daniel-krebs](https://forums.powershell.org/u/daniel-krebs)\
**Post date:** [August 4, 2016, 4:51am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/4 "2016-08-04T04:51:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![soren-moller](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@soren-moller](https://forums.powershell.org/u/soren-moller)\
**Post date:** [August 4, 2016, 5:04am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/5 "2016-08-04T05:04:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![daniel-krebs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/daniel-krebs/32/484_2.png) [@daniel-krebs](https://forums.powershell.org/u/daniel-krebs)\
**Post date:** [August 4, 2016, 5:08am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/6 "2016-08-04T05:08:42Z")

</div>

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

Happy coding,  
Daniel

---

<div class="post-metadata">

**Author:** ![ganapathy-manian](https://avatars.discourse-cdn.com/v4/letter/g/ea666f/32.png) [@ganapathy-manian](https://forums.powershell.org/u/ganapathy-manian)\
**Post date:** [March 21, 2018, 8:04am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/7 "2018-03-21T08:04:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aapeli-hietikko](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@aapeli-hietikko](https://forums.powershell.org/u/aapeli-hietikko)\
**Post date:** [March 21, 2018, 9:12am UTC](https://forums.powershell.org/t/sql-and-powershell/6929/8 "2018-03-21T09:12:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:36pm UTC](https://forums.powershell.org/t/sql-and-powershell/6929/9 "2024-05-16T20:36:08Z")

</div>