Welcome to WuJiGu Developer Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
408 views
in Technique[技术] by (71.8m points)

powershell - sql distinct statement not working in powershel script

I am trying to run the following sql command on a powershell script: I set the following as a variable:

$DBSUM =     select x.[example 1], y.example2, z.example3
from (select count (distinct valueA) AS "example 1"
      from dbo.table1
     ) as x,
     (select count (distinct ValueB) AS "example2"
      from dbo.table2
     ) as y,
     (select count (distinct ValueC) AS "example3"
      from dbo.table3
     ) as z

after some other commands, i have the following:

    $SqlConnectionSUM = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnectionSUM.ConnectionString = "Server = $SQLServer; Database = 
    $SQLDB; Integrated Security = True"

    $SqlCmdSUM = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmdSUM.CommandText = $DBSUM
    $SqlCmdSUM.Connection = $SqlConnectionSUM

    $SqlAdapterSUM = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapterSUM.SelectCommand = $SqlCmdSUM

    $DataSetSUM = New-Object System.Data.DataSet
    $null = $SqlAdapterSUM.Fill($DataSetSUM) 
    $DataSetSUM.Tables[0] | select * -ExcludeProperty RowError, RowState, 
   HasErrors, ItemArray,Table | ConvertTo-Html -Head $HtmlHead | Out-File 
   "$PSScriptRootfilelocationexample.html"

The above command works perfectly in SQL, but when I run on my powershell I get the error:

    + ... .example3 from (select count (distinct ValueA) AS "example1 ...
    +                                                            ~~~~~~
    Unexpected token 'example' in expression or statement.
        + CategoryInfo          : ParserError: (:) [], ParseException
        + FullyQualifiedErrorId : UnexpectedToken

Please can anyone advise?

Thank you.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Thank you to Luuk and Astentx for your replies.

I have now working ok, the issue was the " and spaces on example 1

I replaced the spaces with _ and removed the " and t works absolutely fine.

Here is the result:

$DBSUM =     select x.example_1, y.example2, z.example3
from (select count (distinct valueA) AS example_1
      from dbo.table1
     ) as x,
     (select count (distinct ValueB) AS example2
      from dbo.table2
     ) as y,
     (select count (distinct ValueC) AS example3
      from dbo.table3
     ) as z

Thank you all.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to WuJiGu Developer Q&A Community for programmer and developer-Open, Learning and Share
...