-1

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

Mohamed Sheik M
  • 112
  • 10
  • 1
    If you added the minimum amount of SQL to the question to create and populate the tables with some sample data it would be much easier to help you. – Andrew Morton May 19 '22 at 17:36
  • 1
    Are you really sure this posted query raises that error? Maybe you adjusted SQL while debugging? Try re-running *exactly* what you posted here. – Parfait May 19 '22 at 17:43
  • @AndrewMorton Yeah Brother. I could understand. I’ve created & executed this script in SQL. No such error. When I try to import or execute this in Powershell script, It throws me this Ambiguous column Error. Only in Powershell – Mohamed Sheik M May 20 '22 at 18:09
  • @Parfait I didn’t get this error in SQL. It executes & provides expected result in SSMS. But when I try to execute this in Powershell script, I got this error. Feels Like **AS** statement is not recognised in PS1 – Mohamed Sheik M May 20 '22 at 18:13
  • 1
    Did you try re-running what you *exactly* posted here? Please don't go off memory. Carefully check between actual code that produced this error and what you posted here. You appear to be redacting with generic *table* name. Maybe also with *SystemName*? Please post actual SQL (table/column names should not be sensitive information). – Parfait May 20 '22 at 18:46
  • 1
    @MohamedSheikM Please see [How to create a Minimal, Complete, and Verifiable Example for database-related questions](https://dba.stackexchange.com/help/minimal-reproducible-example). – Andrew Morton May 20 '22 at 19:14
  • @MohamedSheikM Maybe it needs a `$conn.Open()` to open the connection before executing the query. – Andrew Morton May 20 '22 at 19:17
  • Thanks, Everyone. I've solved the issue with a different technique – Mohamed Sheik M May 23 '22 at 16:38

2 Answers2

1

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)
stackozaurus
  • 126
  • 3
  • Thanks, Mate. You provided me with a suggestion to set an alias for First Table. Sometimes overthinking leads to confusion with one we have in our mind – Mohamed Sheik M May 23 '22 at 16:47
0

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

Mohamed Sheik M
  • 112
  • 10