How to get this SQL query working in Powershell

The query is working in SQL but it doesnt give me the same output in Powershell

$dataSource = "\sqlexpress"
$user = "*****"
$pwd = "*****"
$database = "test bv"
 
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
 
$query = "SELECT De2EngelenDb.dbo.mwOrders.fldStatus, De2EngelenDb.dbo.mwOrders.fldDatum, De2EngelenDb.dbo.mwOrderRegels.fldArtikelID, tblArtikel.fldOmschrijving,
                      De2EngelenDb.dbo.mwOrders.fldFactNr, tblRelatie.fldNaam
FROM tblRelatie INNER JOIN
                      De2EngelenDb.dbo.mwOrders ON tblRelatie.fldRelatieID = De2EngelenDb.dbo.mwOrders.fldRelatieID INNER JOIN
                      De2EngelenDb.dbo.mwOrderRegels ON De2EngelenDb.dbo.mwOrders.fldOrderID = De2EngelenDb.dbo.mwOrderRegels.fldOrderID INNER JOIN
                      tblArtikel ON De2EngelenDb.dbo.mwOrderRegels.fldArtikelID = tblArtikel.fldArtikelID
WHERE     (De2EngelenDb.dbo.mwOrders.fldStatus < 50)
ORDER BY De2EngelenDb.dbo.mwOrders.fldDatum DESC"
 
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
#$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText  = $query
 
$result = $command.ExecuteReader()

Thx for your help

Danny
LVL 6
Danny KonAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
I think you need this:
$table = new-object “System.Data.DataTable”
$table.Load($result)
$format = @{Expression={$_.fldStatus};Label=”fldStatus”},@{Expression={$_.fldDatum};Label=”fldDatum”},@{Expression={$_.fldArtikelID};Label=”fldArtikelID”},@{Expression={$_.fldOmschrijving};Label=”fldOmschrijving”},@{Expression={$_.fldFactNr};Label=”fldFactNr”},@{Expression={$_.fldNaam};Label=”fldNaam”}
$table | format-table $format

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
The code itself seems right, but I have noticed some things that may be causing the issue.
It seems that you have not added the dot (.) at the beggining of the SQLEXPRESS datasource.
$dataSource = ".\SQLEXPRESS" 

Open in new window


Also, I would double check if the database name is  correct, because you have a space on the name. Maybe the space does not exist or is a underline.
$database = "test_bv" 

Open in new window

or
$database = "testbv" 

Open in new window

0
 
Deepak ChauhanSQL Server DBACommented:
Hi Danny, Can you elaborate more what is the difference in output. Is this giving error or only result difference.
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
Rainer JeschorCommented:
Hi Danny,
does it return anything in Powershell?
Because your statement "the same output" could indicate that not everything is returned.

Am I right that your query addresses multiple databases?

Thanks.
Rainer
0
 
Danny KonAuthor Commented:
Walter. Sorry i didnt explain, i changed the information in de database/datasource myself, that is not the problem
If i do the query in SQL i get the information i want if i do it in Powershell i see only:


         
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6
                                                                                                                                                                     6

Thanks for helping Danny
0
 
Walter RitzelSenior Software EngineerCommented:
No problem.
Please publish the code that prints the result in powershell
0
 
Danny KonAuthor Commented:
Rainer,

You are right the same question as you answered before but now is the query more complex.
The output i get is in my answer to Walter

Danny
0
 
Danny KonAuthor Commented:
Walter,

At this moment only
$result
0
 
Danny KonAuthor Commented:
Walter,

You think 100% correct

Exactly what the patient needed:)

Thanks Danny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.