PowerShell Insert into SQL Server issue

Question

This is the code I am using:

$command2 = New-Object System.Data.SqlClient.SqlCommand
#$MySql2="Invoke-sql INSERT INTO acubdb.scans (SessionID, Started, Status, Description, Target, Systime, Mailed) VALUES ($SessionVal, $StartedVal, $StatusVal, $DescVal, $TargetVal, $SysDateVal, $MailVal); "

$command2.CommandText = "INSERT INTO acubdb.scans (SessionID, Started, Status, Description, Target, Systime, Mailed) VALUES ($SessionVal, $StartedVal, $StatusVal, $DescVal, $TargetVal, $SysDateVal, $MailVal); "
$command2.Connection = $connect2
$command2.Parameters.AddWithValue("@server", $_.server) | Out-Null
$command2.Parameters.AddWithValue("@instance", $_.instance) | Out-Null

$rowsUpdated = $command2.ExecuteNonQuery()

Write-Output "Updating SQL Database; Adding record for "$SessionVal
Write-Output " "
$connect2.Close()

When I run it, I get this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near the keyword 'is'."
At line:10 char:13
+ $rowsUpdated=$command2.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException

What am I missing this time?

Variables are defined and filled outside of this snippet.

Code

$command2.CommandText = INSERT INTO acubdb.scans (SessionID, Started, Status, Description, Target, Systime, Mailed) VALUES (this is a test, 2017-11-17T07:00:01.418178+00:00, processing, name of site, address of site, 20/11/2017 15:57, 0);

My guess is that you'll need to check the values of the PowerShell variables that you're expanding in the SQL statement. The error seems to be pointing at the SQL statement, and without those values, it's going to be difficult to troubleshoot. — Jeff Zeitlin
On top of what Jeff said. You are also adding sql parameters that I don't see being used in your insert statement. We can't guess this for you. What does $command2.CommandText look like printed to console? — Matt
Where are $SessionVal, $StartedVal, $StatusVal, $DescVal, $TargetVal, $SysDateVal, $MailVal defined ? — Ranadip Dutta

Kashif Qureshi Kashif Qureshi · Accepted Answer · 2017-11-21T14:53:58

Your insert statement should look like this. You are missing single quotes around text:

INSERT INTO acubdb.scans 
            (sessionid, 
             started, 
             status, 
             description, 
             target, 
             systime, 
             mailed) 
VALUES      ('this is a test', 
             '2017-11-17T07:00:01.418178+00:00', 
             'processing', 
             'name of site', 
             'address of site', 
             '20/11/2017 15:57', 
             0);

PowerShell Insert into SQL Server issue

1 Answers