PowerShell and SQL server

Question

Currently I'm working on a PowerShell script which is extracting info about OS, hotfix and installed software.

Well, everything is working well if I want to export to txt file - everything.

But my new task is to upload this information to sql server.

So I'm creating a foreach loop to print installed software and put everything in server. PowerShell isn't showing any errors but I can't get this data into sql server.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=myserver; Initial Catalog=table; Integrated Security=SSPI;"
$conn.Open()
$soft = Get-WmiObject Win32_Product
$cmd = New-Object System.Data.SqlClient.SqlCommand 
$cmd.Connection = $conn
$cmd = $conn.CreateCommand()
foreach ($Software in $soft){

$query = "INSERT INTO dbo.mytable (SoftName, SoftVersion) VALUES ('$($Software.Name)', $($Software.Version))" }

$cmd.CommandText = $query
$result = $cmd.ExecuteNonQuery
$conn.close()

So the idea is that when I run this script, I get all software installed on pc listed in sql server.

SoftName: SoftVersion:

Office 14.202

Sql 15

Thanks!

score 2 · Accepted Answer · 2014-03-03 11:04:57Z

2

What do you get in $result variable when you run your script? I believe, that you get signatures of overloaded methods with the name ExecuteNonQuery. The proper method call looks like this:

$result = $cmd.ExecuteNonQuery()

If you see errors I would recommend you to use code below to get full information about them:

$ErrorActionPreference = "Stop"
$Error.Clear()
try {
    # Your code
}
catch {
    $Error | Format-List * -Force
}

edited Mar 3, 2014 at 11:04

answered Mar 3, 2014 at 10:37

user2060111

Sign up to request clarification or add additional context in comments.

11 Comments

user22401 Over a year ago

Now Im getting error in powershell - Incorrect syntax near '.2100' and under that overloaddefinitions

Joey Over a year ago

That's not a PowerShell error, but an SQL error, I guess.

user2060111 Over a year ago

I've updated the post with ways how to get more information about errors. Can you post the results of running updated code?

user2060111 Over a year ago

Also, please post the value of the $query variable

user22401 Over a year ago

Error code is too big, i copied it into txt file, here is a link - efshare.com/?s=WCELTH . About $query , Im getting exactly the same error.

|

vonPryz · Accepted Answer · 2014-03-03 10:39:12Z

0

You are overwriting the insert command instead of appending into it. In addition, as levgen points out, you are missing method call syntax:

foreach ($Software in $soft){
    $query = "INSERT INTO dbo.mytable (SoftName, SoftVersion) VALUES ('$($Software.Name)', $($Software.Version))" 
} # Oops!
# Now $query contains only the last insert statement.
$cmd.CommandText = $query
$result = $cmd.ExecuteNonQuery # Should be .ExecuteNonQuery()
$conn.close()

Try something like so,

foreach ($Software in $soft){
    $query += "INSERT INTO dbo.mytable (SoftName, SoftVersion) VALUES ('$($Software.Name)', $($Software.Version));" 
} # Appending insert staements
# Now $query should contain lots of insert staements
$cmd.CommandText = $query
$result = $cmd.ExecuteNonQuery()
$conn.close()

answered Mar 3, 2014 at 10:39

vonPryz

24.3k10 gold badges57 silver badges69 bronze badges

4 Comments

user22401 Over a year ago

Thanks! Something new appeared. But now Im getting bunch of errors about incorrect syntax near and some numbers

vonPryz Over a year ago

@user22401 Add the errors into the post. Without specific error messages not much can be done.

user22401 Over a year ago

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '.6012'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'. Incorrect syntax near '.4763'.

user22401 Over a year ago

There is much more of them but many of error codes are the same.

Collectives™ on Stack Overflow

PowerShell and SQL server

2 Answers 2

11 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Related