cannot initialize the datasource object of oledb provider "P

[morisbozzetto]

Hi

I have installed PGNP-Postgres-DE-Trial-1.4.0.3106 on windows 2012 64 bit.
When I've created a linked server in SQLServer 2012 (with the following script), I've received an error

Cannot initialize the datasource object of oledb provider "PGNP".. Microsost SQL server Error 7303

when I've tried to view Postgres tables list od default catalog in SQLServer Management Studio.

Any Ideas?

Thanks in advance


-- Allow In Procees = true
-- DynamicParameters = true
EXEC master.dbo.sp_MSset_oledb_prop N'PGNP.1', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'PGNP.1', N'DynamicParameters', 1

declare @LINKED_SERVER_NAME varchar(max);
declare @PRODUCT_NAME varchar(max);
declare @PGPROVIDER varchar(max);
declare @DATA_SOURCE varchar(max);
declare @CN_STR varchar(max);
declare @SAMPLE_CATALOG varchar(max)

-- postgres database info
set @LINKED_SERVER_NAME = N'PGPOOL';
set @PRODUCT_NAME = N'PGNP';
set @PGPROVIDER = N'PGNP';
set @DATA_SOURCE = N'10.0.0.195';
set @CN_STR = 'PORT=9999;'
set @SAMPLE_CATALOG = N'texasimple';

/* DROP LINKED SERVER */
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = @LINKED_SERVER_NAME)
EXEC master.dbo.sp_dropserver @server=@LINKED_SERVER_NAME, @droplogins='droplogins'

/* CREATE LINKED SERVER */
EXEC master.dbo.sp_addlinkedserver
@server = @LINKED_SERVER_NAME,
@srvproduct = @PRODUCT_NAME,
@provider = @PGPROVIDER,
@datasrc = @DATA_SOURCE,
@provstr = @CN_STR,
@catalog = @SAMPLE_CATALOG

/* set up Extended properties of the Linked Server */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@LINKED_SERVER_NAME,@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='pwd'

EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
@optname='data access', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
@optname='rpc', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
@optname='rpc out', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
@optname='use remote collation', @optvalue='true'

[Moderator]

The script looks good. Maybe issue is caused by a firewall, or smth else.

Could you test connection from the computer with SQL Server to Postgres using .UDL file? Create an empty test.udl file, double click on it, provider connection parameters in the DataLink dialog, click Test button.

[bobwhitley]

I am having the same issue. I created a UDL file and tried the Test Connection button and I just get a dialog reading "Test connect failed". I know my server name, port, user name, password & database are correct because I connect with them successfully using ODBC and JDBC from the same server, I just can't get the OLEDB driver to connect. Any suggestions?

[Moderator]

Please make sure that the Postgres server is configured to accept remote connections. pg_hba.conf should have entries like:

Code: Select all

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all         all         192.168.1.0           255.255.253.0    md5
host    all         all         fe80:0:0:0:0:0:0:0    ffff:0:0:0:0:0:0:0    md5


Check if firewall is blocking connection.