none
MIMWAL Powershell activity is not getting data from SQL server RRS feed

  • Question

  • Hi Everyone,

    I am using MIMWAL PowerShell Activity for one of my customer's requirement. On MIM Portal server I have tested on script in Powershell IDE by running as MIM Service account. Script runs just fine and giving expected output. Just FYIP: We have local SQL user to connect with SQL. While using it in Activity to make SQL connection, It is not bringing any data out of SQL. Below is the part of query not getting executed:

    $dataSource = "X.X.X.X"
                $user = "Username"
                $pwd = "Password"
                $database = "Database Name"
                $connectionString="Server=$dataSource;uid=$user;pwd=$pwd;Database=$database;Integrated Security=False;"
                $connection = New-Object System.Data.SqlClient.SqlConnection
                $connection.ConnectionString = $connectionString
                $connection.Open()
                $command = $connection.CreateCommand()
                $query = $("SELECT * FROM [Table] where Filter")
               
                $command.CommandText = $query
                $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
                $SqlAdapter.SelectCommand = $command
                $DataSet = New-Object System.Data.DataSet
                $SqlAdapter.Fill($DataSet)
                $connection.Close()
                $table=$DataSet.Tables[0]
                $All_Values=$table.ItemArray
                $Template_Value=$All_Values[16]

    NOTE: No Output in $Template_Value

    I have also tried to create one table in MIM SQL server and MIM Service account has permission on that but still getting same error. 

    Kindly help and suggest how to make SQL connectivity in MIMWAL PowerShell Activity. Thanks in Advance.


    If My Answer helps you do not forget to check helpful post and If answers your question do not forget to "Mark it as an Answer" Thanks~ Giriraj Singh Bhamu

    Monday, January 27, 2020 11:51 AM

All replies

  • Do you see any errors in the WAL event log?
    Wednesday, January 29, 2020 9:29 AM
    Owner
  • Hi Nilesh,

    Not anything related to SQL query. I have enabled verbose as well. Only error I am getting related to Array Indexing for this -"$Template_Value=$All_Values[16]".

    "$SqlAdapter.Fill($DataSet)" is not getting any data.


    If My Answer helps you do not forget to check helpful post and If answers your question do not forget to "Mark it as an Answer" Thanks~ Giriraj Singh Bhamu

    Thursday, January 30, 2020 6:55 AM
  • ..."$All_Values=$table.ItemArray"

    Don't think DataTable has ItemArrary property. Do you mean to use a DataRow there? However, if that is the case, I'm clueless as to how this script is working in PowerShell ISE.

    Thursday, January 30, 2020 10:27 AM
    Owner
  • Hi Nilesh,

    Table is just a variable name. I am using Dataset and getting values out of that. It is getting correct data while running script in Powershell ISE.

    Any further help will be great.


    If My Answer helps you do not forget to check helpful post and If answers your question do not forget to "Mark it as an Answer" Thanks~ Giriraj Singh Bhamu

    Monday, February 3, 2020 7:33 AM
  • Seems like you have no clue what your script is supposed to do :) $table variable is a DataTable as its set as $table=$DataSet.Tables[0]. You can check the .NET documentation to see what methods are available to this class. So if this script is running properly, it must be due to some PowerShell smarts. And if it's not running in MIMWAL, it must be because the PowerShell 2.0 did not have those smarts. Open a regular windows cmd prompt, run PowerShell.exe -Version 2.0 and then run your script block and see where it fails. I bet it's due to $table.ItemArray.
    • Proposed as answer by Leo Erlandsson Thursday, February 6, 2020 3:26 PM
    • Unproposed as answer by GirirajSingh Wednesday, February 19, 2020 6:32 AM
    Tuesday, February 4, 2020 10:33 PM
    Owner
  • Seems like you have no clue what your script is supposed to do :) $table variable is a DataTable as its set as $table=$DataSet.Tables[0]. You can check the .NET documentation to see what methods are available to this class. So if this script is running properly, it must be due to some PowerShell smarts. And if it's not running in MIMWAL, it must be because the PowerShell 2.0 did not have those smarts. Open a regular windows cmd prompt, run PowerShell.exe -Version 2.0 and then run your script block and see where it fails. I bet it's due to $table.ItemArray.
    Nilesh, I am very well aware of what this script is doing. Anyhow, I was able to get it working with some additional RnD on mu own. Thanks a lot for your help and guidance.

    If My Answer helps you do not forget to check helpful post and If answers your question do not forget to "Mark it as an Answer" Thanks~ Giriraj Singh Bhamu


    • Edited by GirirajSingh Wednesday, February 19, 2020 6:39 AM
    Wednesday, February 19, 2020 6:34 AM
  • Good to see you got it working. It will be really helpful for other folks stumbling on this if you post the solution and revised script.
    Wednesday, February 19, 2020 8:06 AM
    Owner