Q: Under what license PGNP is published?
  A: The PostgreSQL Native Provider (PGNP) is published under a commercial software product license. It is a closed-code proprietary software. It is not freeware, shareware, or open-source software. To use it, you would need to purchase a license.
     
  Q: Is license perpetual, or subscription based?
  A: PGNP version 1.4 is a software product offering perpetual licensing, meaning the license never expires. When you purchase this software, you can rest assured that it is a one-time purchase with no hidden or recurring charges. As part of the initial purchase, you will receive 180 days of technical support and product updates, which will help to ensure that your software remains up-to-date and that any issues are resolved promptly and effectively. This support is included in the purchase price of the software. You can purchase an annual support contract separately if you require additional technical support beyond the initial 180-days period. This contract is optional and is not required to continue using the software.
     
  Q: Can license activation be moved to a different server/user, and how to request the license reset?
  A:

To reduce the number of active licenses for the product, kindly follow these steps:

1. Uninstall the product from your current computer. You can do this by opening the Control Panel, selecting Programs and Features, and then choosing the product from the list of installed programs.

2. Once you have uninstalled the product, please send an email to PGNP Support requesting a decrease in the license use counter. In your email, please mention the first four characters of the license key.

Our support team will process your request as soon as possible and confirm the updated license count with you. If you have any questions or concerns, please don't hesitate to reach out to us via email or phone.

     
  Q: Which versions of PostgreSQL are compatible with or supported by the Provider?
  A: We are testing PGNP Provider with PostgreSQL 8.4 to 16. Some customers said they successfully used the Provider with versions 8.0 and 8.1. However, please ensure the Provider works with your version of PostgreSQL before purchasing.
     
  Q: How can I disable trial limitations in the trial version of the provider? Do I need to purchase a license for it?
  A: There is no way to deactivate or disable trial limitations in the trial version of the provider. If you need non trial version you would need to purchase a license and you would get non trial version from us.
     
  Q: What kind of limitations do you have in the trial version?
  A:

The trial version has the following limitations:
1. SELECT statements return up to 100 rows.
2. Database conversion in DTS/SSIS Wizard handles only first 100 rows in every table.
The trial is also popping up a reminder dialog once a day (when connection to a database is being made).

     
  Q: How can I move the activated license over to a new server?
  A: Please request the license use counter reset/decrement by sending email (with whole or part of the license key) to our Support. After receiving response from Support, proceed with license activation on the new server. Then uninstall the product from the old server.
     
  Q: My application is written in C#, I am using .NET provider to access my data, what would I get if I switch to PGNP instead?
  A: Any .NET provider supports only subset of OLEDB interfaces. There is no way for example to use .NET providers for DTS/SSIS transformations. Also many reporting and management tools can only use OLEDB providers to access data in databases. .NET providers do not support server side cursors. .NET providers are written in managed code and can not outperform hand optimized native code. Obviously if you need to access PostgreSQL from native code (C++, Delphi, VB) then PGNP would be the only choice.
     
  Q: Do you support DTS/SSIS transformation from X to Y database?
  A: We have tested many databases transformations from SQL Server and Oracle to PostgreSQL. We believe this represents the most common scenario for DTS/SSIS. We have tested transformations from PostgreSQL to SQL Server and Oracle as well, but not as extensively. Since DTS/SSIS transformations rely on OLEDB data types mapping PGNP should work with any DB with properly written OLEDB provider.
     
  Q: What do you map SQL Server GUID data type to?
  A: After extensive research we decided to map it to Base64 encoded fixed-length (22) character field. Starting with version 1.2.8 and PostgreSQL 8.3 GUID is mapped to UUID field.
     
  Q: What are the System Requirements?
  A: PGNP provider is compatible with Windows Servers 2003 SP2 - 2019, and Windows XP Windows 1X. We recommend MDAC 2.8 or later to be installed. Since provider does not consume any significant amount of memory or CPU cycles, the specific hardware requirements should be defined by an application used. For example, replicatrion of a large MS SQL Server database may require 8GB or more memory on the server.
     
  Q: Any implementation details?
  A: PGNP is written using MS C++ (VS 2019) with key functions implemented in hand optimized assembler. COM implemented using ATL and SQL is handled by state of the art SQL parser. For a fee more details can be obtained under NDA including purchase of the source code.
     
   Q: My application cannot open connection to a database using PGNP. What is the reason?
   A:

First, check if the server is accepting connections on the standard port: 5432. If non-standard port is used then specify it in the connection string: Extended Properties="PORT=7656".

Secondly, in case PostgreSQL server is running on a remote computer, check firewall settings and configuration in pg_hba.conf (to enable remote connections). See PostgreSQL documentation for more details.

Thirdly, run PGNProfiler as Administrator and collect trace. The trace usually contains additional details about reason of an issue.

We usually recommend using a UDL file for testing connectivity via PGNP provider. For this, create an empty file test.udl on Desktop. Double click on the file and, in Data Link Properties dialog, select "PostgreSQL Native Provider" and the parameters of connection in second tab. Click "Test Connection" button.

If connection test fails please check Postgres logs, and see if connection attempt was made, and why it was refused.

     
   Q:

Why a simple query on a Linked Server table with 100 million rows results in unusually long execution time? Here is query example: SELECT TOP 20 * FROM [LINKSVR]...[bigtable]

   A: Please rewrite the query using openquery syntax:
SELECT * FROM openquery(LINKSVR, "SELECT TOP 10 * FROM bigtable")

The original query with "4-part identifiers" caches all rows from a table. The openquery is much more efficient. For stored procedures and SQL commands consider using Exec ... At... syntax.
     
   Q: Why updating a very large table via Linked Server results in Timeout error? Here is command example: SELECT 1 FROM OPENQUERY(LINKSVR,'UPDATE "bigtable" SET "IsActive"=True WHERE "UserId"=960 AND "Client"=True AND "IsActive"= False and "ris"=True and "rwcl"=True')
OLE DB provider "PGNP" for linked server "LINKSVR" returned message "timeout expired".
   A: The issue is caused by default command timeout that is equal to 30 seconds. Please add COMMAND_TIMEOUT=0 parameter to the Extended Properties in connection string.
     
   Q:

Is OLE DB driver caching the schema for tables/views?  If so how do we make the driver reload the schema to get the new column?

    A:  Yes, the provider is caching the schema. The easiest way to recycle the cache is to restart the application. Alternatively, you can connect to the same DB, and execute statement “NOTIFY pgnp_rtx”, or via an OLEDB connection execute the internal stored procedure pgnp_refreshmetadata('','table'). Details are available in the Developer’s Manual.

Here is a sample VBs that automates the cache reset (refreshmetadata.vbs):
option explicit

'' recycle internal metadata cache in the PGNP OLEDB Provider

Dim oCon, sCon, cmdObj, rsObj

If WScript.Arguments.Count <> 1 Then
  WScript.Echo "Expecting exactly 1 parameter - table name."
  WScript.Quit
End If

'' create connection & command objects
set oCon = CreateObject("ADODB.Connection")

sCon = "Provider=PGNP.1;Persist Security Info=True;Data Source=localhost;" & _
       "Initial Catalog=postgres;User ID=postgres;Password=12345;" & _
       "Extended Properties=""PORT=5432;"""

'' connect
oCon.Open sCon

WScript.Echo "Refreshing metadata for: " & WScript.Arguments.Item(0)
                                
Set cmdObj = CreateObject("ADODB.Command")
Set cmdObj.ActiveConnection = oCon
cmdObj.CommandText = "pgnp_refreshmetadata('','" & WScript.Arguments.Item(0) & "');"
cmdObj.Execute

WScript.Echo "Done."

It can be executed from command line: cscript refreshmetadata.vbs tblname

     
   Q:  What’s the difference between server edition vs desktop edition?
   A:  The difference is described in the Developer's Manual. Basically, the Server Edition supports additional OLE DB interfaces (for the Two Phase Commit, FastLoad, etc.), and is intended for work with SSIS, SSAS, SSRS, IIS and other types of servers. Server Edition allows better integration with SSIS, better scalability and performance.