osql in powershell 1.0?

Question

I have an ODBC connection set up on my Windows 2008 Server, and I'm trying to replace some .BAT files that do some processing with Powershell files.

Is there a way to do the same thing as this in PowerShell?

CALL osql /instanceName /Uuser /Ppassword /Q"EXECUTE storedProcName @Parm1= %ePROFILE%, @param2 = N'%eValList%'

Answer 1

SQL Server 2008 provides an osql Powershell cmdlet called invoke-sqlcmd that does that same type of thing as osql from Powershell. That said if you want to continue to use osql you should be able to do something like this and continue to use your Windows users varaialbes:

osql /instanceName /Uuser /Ppassword /Q"EXECUTE storedProcName @Parm1= $env:ePROFILE, @param2 = N'$env:eValList'

Answer 2

If you want an actual Powershell Object to work with after you query a database you can use a function like this that I recently wrote:

function Query-DatabaseTable ( [string] $server , [string] $dbs, [string] $sql )
{
  $Columns = @()
  $con = "server=$server;Integrated Security=true;Initial Catalog=$dbs"

  $ds = new-object "System.Data.DataSet" "DataSet"
  $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($con)

  $da.SelectCommand.CommandText = $sql 
  $da.SelectCommand.Connection = $con

  $da.Fill($ds) | out-null
  $ds.Tables[0].Columns | Select ColumnName | % { $Columns += $_.ColumnName }
  $res = $ds.Tables[0].Rows  | Select $Columns

  $da.Dispose()
  $ds.Dispose()
  return $res
}