This instruction provide steps to install and configure 64-bit ODBC driver for old version of PostgreSQL, setup a linked server connection in Microsoft SQL Server Management Studio, and run a sample SQL query. It includes downloading and installing the ODBC driver, configuring a new connection, setting advanced options, setting up the provider, creating a linked server connection, and running a SQL query to retrieve data from PostgreSQL.

1. Install ODBC

Download and install psqlodbc_09_05_0400-x64.zip from PostgreSQL ODBC drivers.

2. Configure ODBC

2.1 Add new connection

1. Open ODBC Data Source Administrator (64-bit) %windir%\system32\odbcad32.exe

2. Select System DSN tab and click Add...

3. Choose PostgreSQL Unicode(x64) in Create new Data Source window

4. Enter details

• Data Source name – it will be used in SQL Server connection e.g. POSTGRES
• SSL Mode - default disabled
• Database - name of your database
• Server - ip/host address of the database
• Port - default 5432
• User Name/Password - database user credentials

💡 Click Test and check if connection is successful. You could remove User Name and Password for now.

2.2 Configure advanced options

Click Datasource button and check if options described below are ✔️ selected.

Page 1

• Recognize Unique Indexes
• Use Declare/Fetch
• Unknowns as LongVarChar
• Max Varchar: 1024
• Max Long VarChar: 1000000

Page 2

• bytea as LO
• uncheck Server Side prepare

👌 Now you can close the configuration window. You will see the newly created connection (with it's name and version) on the System DSN list.

3. Configure SQL Server

3.1 Setup provider

1. Open Microsoft SQL Server Management Studio and connect to your SQL Server.
2. Expand in Object Explorer tree: Server Objects > Linked Servers > Providers and double click on MSDASQL.
3. On General page enable Level zero only provider option.
4. Click OK.

3.2 Create linked server connection

1. While staying in the Object Explorer, right-click on the Linked Servers and select New Linked Server.
2. In General page select Microsoft OLE DB Provider for ODBC Drivers.
3. Enter Linked server name (e.g. POSTGRES).
4. Enter Data source which is the name of your newly created ODBC in step 2.4 (e.g. POSTGRES).
5. You could write PostgreSQL as the Product name.
6. Open Security page and select Be made using this security context – enter database username and password.
7. Open Server Options and check if True only at Data Access, RPC, RPC Out, Use Remote Collation, Enable Promotion of Distributed Transaction.
8. Click OK and close the window. Now you should be able to see new icon in Linked Server folder.

4. Write first SQL

SELECT TOP 100 * FROM [POSTGRES]...[information_schema.sql_features];