OLEDB Provider options
- Last UpdatedFeb 07, 2023
- 2 minute read
- PI System
- PI OLEDB Enterprise 2019
- Developer
A fourth category of parameters is available outside of the environment where the Linked Server is being created; that is because these parameters are shared by all Linked Servers configured with the same OLE DB provider (for example, PI OLEDB Enterprise). OSIsoft recommends that you use these OLE DB provider options:
|
Option name |
Recommended Setting |
Description |
|---|---|---|
|
Dynamic parameter |
Yes |
Allows SQL placeholders (represented by '?') in parameterized queries. |
|
Nested queries |
Yes |
Allows nested SELECT statements in the FROM clause, that is, sub-queries. |
|
Level zero only |
No |
Only base level OLEDB interfaces are invoked against the provider. PI OLEDB Enterprise supports more than base level interfaces, so do not enable this option. |
|
Allow inprocess |
Yes |
If set, Microsoft SQL Server allows the provider to be instantiated as an in-process server. For performance reasons, OSIsoft strongly recommends that this option be enabled so that PI OLEDB Enterprise is run as an in-process server. Should you decide to not set this option, additional security settings must be configured in order to initialize the provider out-of-process. For details contact OSIsoft Technical support, OSIsoft Customer Portal Contact Us page. Also, run SSMS as administrator. |
|
Non transacted updates |
No |
If non-zero, SQL Server allows updates, even if the ITransactionLocal interface is not available. PI OLEDB Enterprise supports the ITransactionLocal interface, so OSIsoft recommends that you do not enable this option. |
|
Index as access path |
False |
If non-zero, SQL Server attempts to use indexes of the provider to fetch data, which PI OLEDB Enterprise does not support. |
|
Disallow adhoc access |
False |
If set, SQL Server does not allow executing pass-through queries against the OLEDB provider. While this option can be checked, it is sometimes appropriate to execute pass-through queries. |
|
Supports LIKE operator |
Yes |
Indicates that the provider supports queries using the LIKE keyword. |
If you are running SQL Management Studio Express, the Provider Options button is not available. You must run the scripts manually from SQL Server Management Studio to enter these settings. Use this script to enter a new query:
EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'AllowInProcess', 1
EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'DynamicParameters', 1
EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'NestedQueries', 1
EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', N'SqlServerLIKE', 1