- Code: Select all
+======================+======================================================+
| Property | Description |
+======================+======================================================+
| Provider | Provider name. Always equals to PGNP.1. |
+----------------------+------------------------------------------------------+
| User ID | User name defined for the database login. |
+----------------------+------------------------------------------------------+
| Password | User password. |
+----------------------+------------------------------------------------------+
| Initial Catalog | Database alias. |
+----------------------+------------------------------------------------------+
| Data Source | PostgreSQL Computer name or IP address. |
+----------------------+------------------------------------------------------+
| Extended Properties | Additional parameters as a string in double quotes. |
| | See details below. |
+======================+======================================================+
Connection string extended properties:
- Code: Select all
+======================+======================================================+
| Property | Description |
+======================+======================================================+
| PORT | Integer. PostgreSQL port. Can be any positive integer|
| | value. |
| | Default: 5432. |
+----------------------+------------------------------------------------------+
| LOWERCASESCHEMA | Boolean. When enabled it translates database schema |
| | related SQL elements (table name, field name, etc. ) |
| | into lower case. Must be enabled during DTS/SSIS |
| | conversion. |
| | Enabled : LOWERCASESCHEMA=ON |
| | Disabled: LOWERCASESCHEMA=OFF |
| | Default : Disabled |
+----------------------+------------------------------------------------------+
| FORCEUTF8 | Boolean. When enabled it forces client encoding to be|
| | UTF8 regardless of encoding reported by PostgreSQL |
| | (PGNP 1.2.6 compatibility mode). When disabled it |
| | uses client encoding reported by PostgreSQL. |
| | Enabled : FORCEUTF8=ON |
| | Disabled: FORCEUTF8=OFF |
| | Default : Disabled |
+----------------------+------------------------------------------------------+
| CNV_SPECIAL_FLTVAL | Boolean. When enabled the following conversion rules |
| | will apply: |
| | +Infinity will convert into MAX_FLOAT/MAX_REAL, |
| | -Infinity will convert into -MAX_FLOAT/-MAX_REAL, |
| | NaN will convert into zero. |
| | It helps to avoid "invalid data for column" bug in |
| | Linked Servers. |
| | Enabled : CNV_SPECIAL_FLTVAL=ON |
| | Disabled: CNV_SPECIAL_FLTVAL=OFF |
| | Default : Disabled |
+----------------------+------------------------------------------------------+
| SEARCH_PATH | String. When set it enables provider to resolve |
| | schema the same way as in PostgreSQL i.e. |
| | "SET search_path". |
| | Enabled : set SEARCH_PATH to non empty string |
| | Disabled: do not set SEARCH_PATH |
| | Default : Disabled |
+----------------------+------------------------------------------------------+
| BULK_INSERT | Integer. Can be any positive integer value from 1 to |
| | 1000. When set it enables provider to speedup |
| | DTS/SSIS conversion by combining number of separate |
| | inserts (specified by BULK_INSERT) into one bulk |
| | insert statement. |
| | Enabled : From BULK_INSERT=2 to BULK_INSERT=1000 |
| | Disabled: BULK_INSERT=1 or do not set BULK_INSERT |
| | Default : Disabled |
+======================+======================================================+
Example #1. Connection string with default extended properties:
Provider=PGNP.1;Password=12345;Persist Security Info=True;User ID=postgres;
Initial Catalog=postgres;Data Source=localhost;Extended Properties=""
Example #2. Connection string with non default extended properties:
Provider=PGNP.1;Password=12345;Persist Security Info=True;User ID=postgres;
Initial Catalog=postgres;Data Source=localhost;
Extended Properties="PORT=5433;LOWERCASESCHEMA=ON;FORCEUTF8=ON;"
The way to create PGNP connection string via .UDL file is described here:
viewtopic.php?f=11&t=9
