To do this, I'll use simple function in my script:

#######################
function Get-SQLData
{
    param(
    [string]$serverName,
    [string]$databaseName,
    [string]$query
    )

    Write-Verbose "Get-ISData serverName:$serverName databaseName:$databaseName query:$query"

    $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
    $dt = New-Object "System.Data.DataTable"
    [void]$da.fill($dt)
    $dt

} #Get-SQLData


Then I will call the function within the same script:

Get-SqlData "Z002\SQL2K8" "pubs" "select au_id from authors" | foreach {$_.au_id }

Hi, thanks for the script, but I have a problem for understand the third parameter $query.
what is au_id and authors?
if you can explain for me, I will be very hapy.

Thanks.

Posted By ggmt89 on 18 Sep 2009 02:00 PM
$SQLSERVER=read-host "Enter SQL Server Name:"
$Database=read-host "Enter Database Name:"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysobjects where type='u'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$query="select usuarios from usuarios" | foreach {$_.usuarios}
Write-Verbose "Get-ISData serverName:$serverName databaseName:$databaseName query:$query"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$SqlConnection)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt
$SqlConnection.Close()


tables name is: usuarios
columns name is: usuarios
but return this error:
Exception callin "fill" with "1" argument(s): "ExecuteReader: CommandText property has not been At C:\sql.ps1:16 char:16
+          [void]$da.fill( (((( $dt)

help me correct this error!