Add Data into SQL Database by PowerShell Script – ExecuteNonQuery with 0 Arguments: Incorrect Syntax Near 'S'

A PowerShell script designed for PST scanning in the file system resulted in a PSObject. When attempting to insert results into a SQL database, an error occurred.

The Problem

The error message: ExecuteNonQuery with 0 arguments: Incorrect syntax near 'S'

The initial code used string concatenation for the SQL query:

foreach ($entry in $pstarray) {
    $cmd.commandtext = "INSERT INTO data (pstdirectory,pstname,pstsize,pstowner,lastwritetime,SSOID,timestamp,pstsize2) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f $entry.directory, $entry.name, $entry.length, $entry.owner, $entry.lastwritetime, $entry.SSOID, $stamp, $entry.Length2

    $cmd.executenonquery()
}

The issue occurred when directory paths contained special characters, such as: \\fileServerName\OwnerName\mike's folder

The apostrophe character broke the SQL query. Attempting to escape the character did not resolve the issue:

$directory2 = $entry.Directory -replace "'", "`'"

The Solution

The problem was resolved by implementing a parameterized query:

foreach ($entry in $pstarray) {
    $cmd.CommandText = "INSERT INTO data (pstdirectory,pstname,pstsize,pstowner,lastwritetime,SSOID,timestamp,pstsize2) VALUES (@pstdirectory,@pstname,@pstsize,@pstowner,@lastwritetime,@SSOID,@timestamp,@pstsize2)"
    $cmd.Parameters.Clear()
    $cmd.Parameters.Add("@pstdirectory", $entry.directory)
    $cmd.Parameters.Add("@pstname", $entry.name)
    $cmd.Parameters.Add("@pstsize", $entry.length)
    $cmd.Parameters.Add("@pstowner", $entry.owner)
    $cmd.Parameters.Add("@lastwritetime", $entry.lastwritetime)
    $cmd.Parameters.Add("@SSOID", $entry.SSOID)
    $cmd.Parameters.Add("@timestamp", $stamp)
    $cmd.Parameters.Add("@pstsize2", $entry.Length2)

    $cmd.executenonquery()
}

Critical Step: $cmd.Parameters.Clear() is essential. Without clearing parameters between iterations, you will receive the error: “The variable name ‘@pstdirectory’ has already been declared. Variable names must be unique within a query batch or stored procedure.”