executing Powershell cmdlets from shell() commands

This topic contains 6 replies, has 3 voices, and was last updated by  John Tregea 3 weeks, 2 days ago.

  • Author
    Posts
  • #103210

    John Tregea
    Participant

    Hi,

    I am developing an application to migrate non-table objects between various databases (across platform and cross versions) and need to be able to create and delete ODBC DSN records on the fly (for security and automation purposes).

    I have been researching PowerShell on lots of forums and in the MS docs but have not been able to resolve what is going on with the following.

    I am able to successfully execute

    PowerShell -Command $Result = Remove-OdbcDsn -Name "scriptBroker" -DsnType "User"

    from the command line but while

     Add-OdbcDsn -Name "scriptBroker" -DriverName "SQL Server" -DsnType "User" -SetPropertyValue @("Server=IBT-DEV-DELL\IBT_DEMO", "Trusted_Connection=Yes")

    works in PowerShell, if I pre-pend the "PowerShell -Command" syntax and execute it from the command line it fails. (error message below)

    C:\Users\jtregea>PowerShell -Command $Result = Add-OdbcDsn -Name "scriptBroker" -DriverName "SQL Server" -DsnType "User" -SetPropertyValue @("Server=IBT-DEV-DELL\IBT_DEMO", "Trusted_Connection=Yes")
    At line:1 char:127
    + ... snType User -SetPropertyValue @(Server=IBT-DEV-DELL\IBT_DEMO, Trusted ...
    + ~
    Missing argument in parameter list.
    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingArgument

    The IDE I am using (LiveCode) will allow me to write to shell() but I have no direct access to PowerShell from the IDE environment.

    Any advice on this appreciated.

    Regards

    John

  • #103249

    Rob Simmers
    Participant

    -SetPropertyValue should be a hash table {}, not an array ().

    -SetPropertyValue @{"Server=IBT-DEV-DELL\IBT_DEMO", "Trusted_Connection=Yes"}
    
  • #103252

    Joel Sallow
    Participant

    If that were the case, that's not correct hashtable syntax either.

    -SetPropertyValue @{Server="IBT-DEV-DELL\IBT_DEMO", Trusted_Connection="Yes"}
  • #103270

    John Tregea
    Participant

    Thank you both for your advice. I am still seeing the same error with both alternatives but will delve deeper into the formatting and see if I can resolve it. 🙂

    I will drop the successful code here once it is working.

  • #103274

    Rob Simmers
    Participant

    Looking at the help, Get-Help Add-OdbcDsn -Full:

    Example 3: Add and store an ODBC System DSN
        
        PS C:\> $NewDsn = Add-OdbcDsn -Name "MyPayroll" -DriverName "SQL Server Native Client 10.0" -DsnType "System" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", 
        "Database=Payroll") -PassThru
        
        This command adds the ODBC System DSNs named MyPayroll that use SQL Server Native Client 10.0 with the specified DSN properties, and then stores the results in the $NewDsn variable. The 
        command includes the PassThru parameter. Without PassThru, the cmdlet does not return anything.
    

    The only thing that stands out is you are not providing the database (e.g. "Database=Payroll"). It is also indicates that it wants an array, which is a bit odd, but try just adding the database and see if it clears the error up.

  • #103277

    John Tregea
    Participant

    Hi Rob,

    Unfortunately the addition of the database to the arguments still returns an error;

    At line:1 char:127
    + ... snType User -SetPropertyValue @(Server=IBT-DEV-DELL\IBT_DEMO, Trusted ...
    +                                                                 ~
    Missing argument in parameter list.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : MissingArgument

    I will try providing additional arguments from the docs and see what I can get to work.

    Thanks for your time.

  • #103279

    John Tregea
    Participant

    Honestly I feel trapped by the IDE having to try and script PowerShell from the Command Line anyway. :/

    Not conducive to a long term solution. 🙂

You must be logged in to reply to this topic.