cannot initialize the datasource object of oledb provider "P

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

cannot initialize the datasource object of oledb provider "P

Postby morisbozzetto » Fri Jan 25, 2013 12:50 am

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'
morisbozzetto
 
Posts: 1
Joined: Fri Jan 25, 2013 12:39 am

Re: cannot initialize the datasource object of oledb provide

Postby Moderator » Fri Jan 25, 2013 9:41 am

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.
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm

Re: cannot initialize the datasource object of oledb provide

Postby bobwhitley » Wed Mar 13, 2013 12:37 pm

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?
bobwhitley
 
Posts: 2
Joined: Wed Mar 13, 2013 12:34 pm

Re: cannot initialize the datasource object of oledb provide

Postby Moderator » Sun Mar 17, 2013 12:31 am

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.
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron