powershell conecting to a microsoft Windows Internal Database

Question

_{Saturday, July 21, 2018 6:17 AM}

how do i connect to a WID using powershell

this is what i tried

[string] $Server= "\.\pipe\MICROSOFT##WID\tsql\query"
[string] $Database = "racctdb"
[string] $UserSqlQuery= $("SELECT * FROM sessiontable")

$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery $UserId $Pass

# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
    $Datatable = New-Object System.Data.DataTable

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable.Load($Reader)
    $Connection.Close()

    return $Datatable
}

#validate we got data
Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")

All replies (5)

_{Saturday, July 21, 2018 6:40 AM ✅Answered}

Don't over complicate the code.

$sqlConn = 'server=\\.\pipe\MICROSOFT##WID\tsql\query;database=racctdb;trusted_connection=true;'
$conn = New-Object System.Data.SQLClient.SQLConnection($sqlConn)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = 'SELECT * FROM sessiontable'
$rdr = $cmd.ExecuteReader()
$dt = New-Object System.Data.DataTable
$dt.Load($rdr)
$conn.Close()
$dt

"Pipes" must be configured for the server and the client.

\(ツ)_/

_{Monday, July 23, 2018 3:18 AM}

works perfectly thanks jrv!

_{Tuesday, April 9, 2019 5:26 PM}

Hello,

a little error at line:

$rdr = $Command.ExecuteReader()

should be replace by

$rdr = $Cmd.ExecuteReader()

Thanks.

Thierry DEMAN-BARCELO. Offce Apps&Services MVP. MCSE:Messaging 2016,MCSE:Server Infrastructure 2012(85 MCPs). MCSA Office 365 http://base.faqexchange.info

_{Tuesday, April 9, 2019 5:28 PM}

Hello,

a little error at line:

$rdr = $Command.ExecuteReader()

should be replace by

$rdr = $Cmd.ExecuteReader()

Thanks.

Thierry DEMAN-BARCELO. Offce Apps&Services MVP. MCSE:Messaging 2016,MCSE:Server Infrastructure 2012(85 MCPs). MCSA Office 365 http://base.faqexchange.info

Thanks - fixed it.

\(ツ)_/

_{Tuesday, April 9, 2019 7:51 PM}

I use invoke-sqlcmd from sql management studio.