Unable to update SQL Table

PowerShell Help
1

Hi Don,

I am going through your CBT nuggets for PowerShell tool making and I am stuck at updating the SQL Table. The function is able to get the computer names fine but just doesn’t update the table.

Keep getting the below error:
Exception calling “ExecuteNonQuery” with “0” argument(s): “Incorrect syntax near the keyword ‘WHERE’.”
At C:\Users\mohammed\Documents\WindowsPowerShell\Modules\MOLDatabase\MOLDatabase.psm1:55 char:9

  •     $command.ExecuteNonQuery() | Out-Null

  •     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:slight_smile: , MethodInvocationException
    • FullyQualifiedErrorId : SqlException

I have not made any changes to your MOLDatabase.psm1.

I have attached my “MSTools.psm1” script for your reference.

2

We don’t support .PSM1 attachments; you need to change it to a TXT. You can also just paste your SQL query in your message, if you like. You’ve got an error in the WHERE portion of it.

When I’m building a SQL statement with variables:

$sql = "UPDATE x SET y = $y, z = $z WHERE a = '$a'"

I’ll usually output that string just so I can see the final query with the variables expanded:

Write-Verbose $sql

I find that often makes the error a lot more obvious. If you can do something similar, I can probably help you pin down the SQL error. You might need to do this in the MOLDatabase module, since it’s building the query based on the data you’re sending it.

3

Hi Don,

Thanks for your reply.

I have attached the txt file for your reference.

Regards,
Mohammed

The SQL Query:

function Save-MSSystemInfo {
param (
[Parameter(Mandatory=$True,ValueFromPipeline=$True)]
[Object]$InputObject
)
PROCESS {
$query = @"
UPDATE MSSystemInfo SET
Manufacturer = ‘$($InputObject.Manufacturer.Replace("’“,”‘’“))',
RAM = $($InputObject.RAM),
OSVersion = '$($InputObject.OSVersion.Replace(”‘“,”’‘"))’,
SPVersion = $($InputObject.SPVersion),
Model = ‘$($InputObject.Model.Replace("’“,”‘’"))',
OSBuild = $($InputObject.OSBuild),
Sockets = $($InputObject.Sockets),
WHERE ComputerName = ‘$($InputObject.ComputerName)’
"@
Write-Debug $query
Invoke-MOLDatabaseQuery -isSQLServer -connectionString $MSConnectionString -query $query

}

}

4

You have a trailing comma in your SET clause, right before WHERE (after Sockets = $($InputObject.Sockets) ).

5

Also, you should add [CmdletBinding()] before your Param() block. Would make Write-Debug a lot more useful.

6

Champion Guys…this is excellent stuff…makes a wonderful tool.

I am currently working on building a tool with combination of PowerShell + PowerCLI + SQL and a web dashboard(Wavemaker and Tomcat)

I will surely use this forum for all my queries…you guys are awesome :slight_smile: