Powershell error on empty dataset with SMO ExecuteWithResults function

Question

I use the function database.ExecuteWithResults() from the SMO library in powershell.

It works very well if my SQL request return result with at least one line. But if the result don't have any line I get the following error:

"You cannot call a method on a null-valued expression."

Bellow my code:

function runDBQuery($SqlServer, $database, $query){
  $SqlConn = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServer
  $db = $SqlConn.Databases[$database]
  $result = new-object System.Data.DataSet
  $result = $db.ExecuteWithResults($query)
return $result
}

If someone know how to managethis issue.

regards

Answer 1

The problem is that a DataSet isn't an IEnumerable type and that's what return looks for. Try:

return ,$result