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
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)