SQL Joins are not working in Powershell Scripts. Throwing Error-CodePudding

I wrote a SQL script that provides result from the same table through the ID. I need to use this script in PowerShell to extract a result for a purpose.

It works in SQL, but it doesn't works in Powershell. Tried using with or without Join functionality. Got the same error.

Found that, AS is not recognized inside the Powershell command & though the Second Table is not getting through the Resultant.

Below is the code,

$QueryUpdate = "SELECT [Table_1].[SystemName] AS [VName]   
FROM [Table] LEFT OUTER JOIN    
[Table] AS [Table_1] ON [Table].[ParentMachineId] = [Table_1].[id]   
WHERE [Table].[Active] = 1 AND [Table_1].[SystemName] IS NOT NULL
AND [Table].[SystemType] NOT IN(4)"

$cmdUpdate=New-Object system.Data.SqlClient.SqlCommand($QueryUpdate,$conn)
$SystemSelect = $cmdUpdate.ExecuteNonQuery()

Tried removing the [ ] from the query in Powershell, Got errors as (.) Invalid Statement or Missing (.)

After executing the script, I got the Error as Ambiguous column name

Exception calling "ExecuteReader" with "0" argument(s): "Ambiguous column name 'SystemName'."
At line:12 char:1
  $VMSelect = $cmdUpdate.ExecuteReader()
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      FullyQualifiedErrorId : SqlException

Could someone provide the solution to override this issue for getting the expected result

CodePudding user response：

The error is from SQL, so it looks like the powershell connection to database works and the command tries to execute the query.

Maybe the problem is that you do not set an alias for the first [Table].

try this:

SELECT 
B.[SystemName] AS [VName]   
FROM [Table]  AS A 
LEFT OUTER JOIN    
[Table] AS B 
ON  A.[ParentMachineId] = B.[id]   
WHERE 
A.[Active] = 1 AND 
B.[SystemName] IS NOT NULL AND 
A.[SystemType] NOT IN(4)

CodePudding user response：

The code below provides me with the exact result I'm looking for. I've modified the code a little bit & It works

$QueryUpdate = "SELECT [Table_1].[SystemName] AS [VName]   
FROM [Table] LEFT OUTER JOIN    
[Table] AS [Table_1] ON [Table].[ParentMachineId] = [Table_1].[id]   
WHERE [Table].[Active] = 1 AND [Table_1].[SystemName] IS NOT NULL
AND [Table].[SystemType] NOT IN(4)"

$cmdUpdate=New-Object system.Data.SqlClient.SqlCommand($QueryUpdate,$conn)
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmdUpdate)
$da.fill($ds)
$conn.close()

$ds.Tables

Thanks, everyone. Suggestions Welcome, If the code needs to be upgraded