cannot initialize the datasource object of oledb provider "P

More
6 years 8 months ago #9876 by morisbozzetto
morisbozzetto created the topic: cannot initialize the datasource object of oledb provider "P
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'

Please Log in or Create an account to join the conversation.

More
6 years 8 months ago #9877 by Moderator
Moderator replied the topic: Re: cannot initialize the datasource object of oledb provide
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.

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #9909 by bobwhitley
bobwhitley replied the topic: Re: cannot initialize the datasource object of oledb provide
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?

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #9911 by Moderator
Moderator replied the topic: Re: cannot initialize the datasource object of oledb provide
Please make sure that the Postgres server is configured to accept remote connections. pg_hba.conf should have entries like:
# 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.

Please Log in or Create an account to join the conversation.

Time to create page: 0.059 seconds
Powered by Kunena Forum