executing Powershell cmdlets from shell() commands

PowerShell Help
1

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: (:slight_smile: , 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

2

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

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

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

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

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. :slight_smile:

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

5

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.

6

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.

7

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

Not conducive to a long term solution. :slight_smile: