Link to home
Start Free TrialLog in
Avatar of Danny Kon
Danny KonFlag for Netherlands

asked on

SQL join in powershell

I want to make a SQL join in a powershell something like this

$dataSource = "XXX\SQLEXPRESS"
$user = "XXX"
$pwd = "XXX"
$database = "Test BV"
 
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
 
$query = select mwOrders.fldOrderID, dbo.tblRelatie.fldNaam, dbo.tblRelatie.fldAdres
from .dbo.mwOrders
inner join dbo.tblRelatie
on dbo.tblRelatie.fldRelatieID = mwOrders.fldRelatieID

Thx Danny
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Hi,

this should work:
$dataSource = "XXX\SQLEXPRESS"
$user = "XXX"
$pwd = "XXX"
$database = "Test BV"
 
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$query = "select mwo.fldOrderID, trel.fldNaam, trel.fldAdres from dbo.mwOrders mwo inner join dbo.tblRelatie trel on trel.fldRelatieID = mwo.fldRelatieID"

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

Open in new window


(adapted from http://irisclasson.com/2013/10/16/how-do-i-query-a-sql-server-db-using-powershell-and-how-do-i-filter-format-and-output-to-a-file-stupid-question-251-255/)

HTH
Rainer
Avatar of Danny Kon

ASKER

Rainer,

I am not good at this, i get the following error:
+ ... ecurity=False;"$connection = New-Object System.Data.SqlClient.SqlConnection
+                    ~~~~~~~~~~~
Unexpected token '$connection' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken
ASKER CERTIFIED SOLUTION
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rainer,

Perfect exactly what i needed the rest of the script is also working

Danny