PowerShell Commands

Add-OdbcDsn

Add-OdbcDsn [-Name*] <String> [-CimSession <CimSession[]>] [-PassThru] [-Platform {32-bit | 64-bit}][-SetPropertyValue <String[]>] [-ThrottleLimit <Int32>] -DriverName* <String> -DsnType* {User | System}[<CommonParameters>]

The Add-OdbcDsn cmdlet adds an Open Database Connectivity (ODBC) data source name (DSN) to the computer. You can specify the properties of the DSN by using the SetPropertyValue parameter.

Do not use the Set-OdbcDsn cmdlet to add a new DSN.

For more information about ODBC, data source names, and drivers, see Microsoft Open Database Connectivity (ODBC) (http://msdn.microsoft.com/en-us/library/ms710252.aspx), Data Sources (http://msdn.microsoft.com/en-us/library/ms711688.aspx), and Drivers (http://msdn.microsoft.com/en-us/library/ms715383.aspx) on the Microsoft Developer Network.

Parameters

-Name <String>

  • This value is required
  • Accepts pipeline input ByPropertyName

Specifies the name of an ODBC DSN. This cmdlet creates a DSN that has the name that this parameter specifies.

-DsnType <String>

  • This value is required
  • Accepts pipeline input ByPropertyName

Specifies the type of an ODBC DSN. This cmdlet adds a DSN of the type that this parameter specifies. The acceptable values for this parameter are:

-- User-- System

-Platform [<String>]

  • Accepts pipeline input ByPropertyName

Specifies the platform architecture. This cmdlet adds an ODBC DSN that belongs to the platform that this parameter specifies. The acceptable values for this parameter are:

-- 32-bit-- 64-bit

The default value is 32-bit on a 32-bit process. The default value is 64-bit on a 64-bit process. If you run this cmdlet in a remote Common Information Model (CIM) session, this parameter refers to the platform architecture on the remote computer.

-DriverName <String>

  • This value is required
  • Accepts pipeline input ByPropertyName

Specifies the name of a driver. This cmdlet assigns the new ODBC DSN to the driver that this parameter specifies.

-SetPropertyValue [<String[]>]

  • Accepts pipeline input ByPropertyName

Specifies an array of property values. This cmdlet specifies these property values for the ODBC DSN. Specify an array of strings of the form <key>=<value>.

-PassThru [<SwitchParameter>]

Returns an object representing the item with which you are working. By default, this cmdlet does not generate any output.

-CimSession [<CimSession[]>]

Runs the cmdlet in a remote session or on a remote computer. Enter a computer name or a session object, such as the output of a New-CimSession or Get-CimSession cmdlet. The default is the current session on the local computer.

-ThrottleLimit [<Int32>]

Specifies the maximum number of concurrent operations that can be established to run the cmdlet. If this parameter is omitted or a value of 0 is entered, then Windows PowerShellr calculates an optimum throttle limit for the cmdlet based on the number of CIM cmdlets that are running on the computer. The throttle limit applies only to the current cmdlet, not to the session or to the computer.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Outputs
Microsoft.Management.Infrastructure.CimInstance#MSFT_OdbcDsn[]
Examples
  1. Add a 32-bit ODBC User DSN:
    PS C:\>  Add-OdbcDsn -Name "MyPayroll" -DriverName "Microsoft Access Driver (*.mdb, *.accdb)" -DsnType "User" -Platform "32-bit" -SetPropertyValue 'Dbq=C:\mydatabase.accdb'
    

    This command adds a 32-bit ODBC User DSN named MyPayroll that uses the specified 32-bit driver with the specified properties.

  2. Add an ODBC System DSN:
    PS C:\>  Add-OdbcDsn -Name "MyPayroll" -DriverName "SQL Server Native Client 10.0" -DsnType "System" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", "Database=Payroll")
    

    This command adds the ODBC System DSNs named MyPayroll that use SQL Server Native Client 10.0 with the specified DSN properties. Because the command does not include the Platform parameter, the platform architecture is the default, native platform.

  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.

  4. Migrates DSNs to a newer version of a driver:
    PS C:\>  $DsnArray = Get-OdbcDsn -DriverName 'SQL Server Native Client 10.0'
    PS C:\>  ForEach ($Dsn in $ DsnArr) {
    
                 Remove-OdbcDsn -InputObject $Dsn 
                 # You can change the property array as well, 
                 # if DSN attributes have been changed in the new driver version
    
    PS C:\>  Add-OdbcDsn -Name $Dsn.Name -DsnType $Dsn.DsnType -Platform $Dsn.Platform -DriverName 'SQL Server Native Client 12.0' -SetPropertyValue $Dsn.PropertyValue
    }
    

    The first command gets ODBC data source names by using the Get-OdbcDsn cmdlet, and then stores them in the $DsnArray variable.

    The second command uses the ForEach Windows PowerShell keyword to step through each member of $DsnArray. For each member, the command uses the current cmdlet to migrate DSNs using the SQL Server Native Client 10.0 driver to a newer version of that driver. This command works for the SQL Server Native Client ODBC driver.

This work is licensed under a Creative Commons Attribution 4.0 International. It is attributed to Microsoft Corporation and can be found here.

PowerShell Commands