Hi,

I'm writing a script that will grab info from a database, then use it to ping servers, then write some results back to the same database.  Unfortunately it is falling over when writing back to the database.

Here it is.

Invoke-Sqlcmd -ServerInstance DPROVDB -Query "select org_id
from organisation_tbl
where opt_id = 3
and org_categories like 'S%'
and org_categories not like '%A%'
and org_categories not like '%M%'
order by org_id desc" | ForEach-Object {

   $DENI = $_.org_id

   gwmi Win32_PingStatus -Filter "Address='$DENI-am01'" | select IPV4Address | where { $_.IPV4Address -notlike '10.*' } | ForEach-Object {

   Invoke-Sqlcmd -ServerInstance DPROVDB -Query "INSERT INTO HoldingDB.dbo.OFFLINESIMS_TMP VALUES ('$DENI')"

   }
}
---------------------------------------
I can write-host the results no worries, but when I attempt to run the SQL Insert I recieve this error :

The WriteObject and WriteError methods cannot be called from outside the overrides of the BeginProcessing, ProcessRecord, and EndProcessing methods, and only from that same thread. Validate that the cmdlet makes these calls correctly, or please contact Microsoft Support Services.

At :line:1 char:13

+ Invoke-Sqlcmd <<<< -ServerInstance DPROVDB -Query "
----------------------------------------

I've googled the error and I couldn't find anything that related to what I am trying to do.  So wondered if anyone could help in any way.

Thanks in advance

Robbie.

Hmmm, that could be a bug y'know. You may want to post that over at Microsoft Connect. The error message is a pretty clear indication that the cmdlet is not implemented properly.

Damn! Thanks for the help - I read a MS post saying a similar thing.

Such an annoyance!

Thanks again,

Robbie.

I wrote a work around for it. Basically rather than writing to the DB directly, I just dump the records into a temp file then use it to write to the DB.

Not pretty, but it works!

function WritetoDB {
$Orgs = Get-Content $tmp_file
Invoke-Sqlcmd -ServerInstance DPROVDB -Query "DELETE FROM HoldingDB.dbo.OFFLINESIMS_TMP"
ForEach ($Org in $Orgs ) {Invoke-Sqlcmd -ServerInstance DPROVDB -Query "INSERT INTO HoldingDB.dbo.OFFLINESIMS_TMP VALUES ('$Org')"}
}