Linked server sample using SQL Server 2005

[Moderator]

Since many people are asking about "linked servers with Postgres", we are publishing the linked server sample

Steps:

1) Create sample Postgres DB to be used with a linked server.
2) Create a linked server in SQL Management Studio.
3) SQL statements for testing the sample linked server.


1. Create sample Postgres DB to be used with a linked server.

In pgAdmin III execute the following script for 'linkedtest' database.

Code: Select all

CREATE DATABASE linkedtest WITH OWNER = postgres ENCODING = 'UTF8';

--
-- Definition of test tables.
--

CREATE TABLE account
(
accountid uuid NOT NULL,
account varchar(128) NOT NULL,
description varchar(256),
employees integer,
webaddress varchar(128),
createdate timestamp without time zone NOT NULL,
modifydate timestamp without time zone NOT NULL,
CONSTRAINT account_pk PRIMARY KEY (accountid)
);

CREATE TABLE contact
(
contactid uuid NOT NULL,
accountid uuid,
lastname varchar(64),
firstname varchar(64),
createdate timestamp without time zone NOT NULL,
modifydate timestamp without time zone NOT NULL,
CONSTRAINT contact_pk PRIMARY KEY (contactid)
);

CREATE TABLE address
(
addressid uuid NOT NULL,
entityid uuid,
entitytype varchar(16),
line1 varchar(128),
line2 varchar(128),
line3 varchar(128),
city varchar(64),
state varchar(16),
postalcode varchar(16),
countryname varchar(64),
CONSTRAINT address_pk PRIMARY KEY (addressid)
);

CREATE TABLE gadgets
(
manufacturerid bigint NOT NULL,
productid integer NOT NULL,
features xml,
CONSTRAINT pk_id PRIMARY KEY (manufacturerid, productid)
);

CREATE TABLE eventlog
(
createdate timestamp without time zone NOT NULL,
"type" integer NOT NULL,
message character varying(512)
);

--
-- Content of test tables.
--

INSERT INTO account values('00000001-0000-0000-0000-000000000000', 'Mertvye Dushi', 'by Gogol, 1842', 8, 'www.revisor.ru', '2000-01-01', '2001-01-01');

INSERT INTO contact values('00000001-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Chichikov', 'Pavel', '2000-01-01', '2001-01-01');
INSERT INTO contact values('00000002-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Nozdrev', 'Roman', '2000-01-01', '2001-01-01');
INSERT INTO contact values('00000003-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Manilov', 'Ivan', '2000-01-01', '2001-01-01');
INSERT INTO contact values('00000004-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Sobakevich', 'Boris', '2000-01-01', '2001-01-01');
INSERT INTO contact values('00000005-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Plushkin', 'Vladimir', '2000-01-01', '2001-01-01');
INSERT INTO contact values('00000006-0001-0000-0000-000000000000', '00000001-0000-0000-0000-000000000000', 'Korobochka', 'Nastasia', '2000-01-01', '2001-01-01');

INSERT INTO address values('00000001-0002-0000-0000-000000000000', '00000001-0001-0000-0000-000000000000', 'CA', 'ul. Nevski 314', NULL, NULL, 'S. Petersbugh', 'SP', 'Russia');
INSERT INTO address values('00000002-0002-0000-0000-000000000000', '00000002-0001-0000-0000-000000000000', 'CA', 'ul. Kutezh 5', NULL, NULL, 'Nozdrevka', 'VG', 'Russia');
INSERT INTO address values('00000003-0002-0000-0000-000000000000', '00000003-0001-0000-0000-000000000000', 'CA', 'ul. Mechty 1', NULL, NULL, 'Zamanilovka', 'VG', 'Russia');
INSERT INTO address values('00000004-0002-0000-0000-000000000000', '00000004-0001-0000-0000-000000000000', 'CA', 'ul. Dzhunglei 22', NULL, NULL, 'Sobakeevo', 'VG', 'Russia');
INSERT INTO address values('00000005-0002-0000-0000-000000000000', '00000005-0001-0000-0000-000000000000', 'CA', 'ul. Golodnaya 26', NULL, NULL, 'Plushkovka', 'VG', 'Russia');
INSERT INTO address values('00000006-0002-0000-0000-000000000000', '00000006-0001-0000-0000-000000000000', 'CA', 'ul. Sluzhebnaya 97', NULL, NULL, 'Truhmachevka', 'VG', 'Russia');

INSERT INTO gadgets values(1000,10,'<FEATURES>Nokia 1234</FEATURES>');
INSERT INTO gadgets values(1000,11,'<FEATURES>Nokia EZII</FEATURES>');

INSERT INTO eventlog values(localtimestamp, 1, 'test message 1');
INSERT INTO eventlog values(localtimestamp, 2, 'test message 2');

2. Create a linked server in SQL Management Studio.

Open SQL Management Studio 2005 and execute the following SQL statemens. The statments will create a linked server with name 'PGNP_SAMPLES'. They will also ensure that PGNP provider is configured to run as "In Process DLL" in the SQLServer process. Feel free to change the connection string as desired.

Code: Select all

-- 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'PGNP_SAMPLES';
set @PRODUCT_NAME = N'PGNP';
set @PGPROVIDER = N'PGNP';
set @DATA_SOURCE = N'localhost';
set @CN_STR = 'PORT=5444;'
set @SAMPLE_CATALOG = N'linkedtest';

/* 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'postgres',@rmtpassword='48-09'

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'

3. SQL statements for testing the sample linked server.

Open "Query Window" in SQL Server Mgmt Studio 2005 (with any active DB, e.g. master). Execute the following statements one by one to see how the linked server works.
Note: it could be handy to use a 'ssmssqlproj' project/solution if you want to develop further the tests for the linked server.

Code: Select all

-- Sample1. Test select, insert, update and delete

/*
* S1. SELECT all rows from a table
*/
select * from [PGNP_SAMPLES]...contact;
select * from [PGNP_SAMPLES]...gadgets;
select * from [PGNP_SAMPLES]...eventlog;

/*
* S2. SELECT a row by given uuid
*/
declare @contactid uniqueidentifier;
set @contactid = '00000003-0001-0000-0000-000000000000';

select * from [PGNP_SAMPLES]...contact where contactid=@contactid;

/*
* S3. SELECT rows by uuid literal wildcard
*/
select * from [PGNP_SAMPLES]...contact where contactid like '00000003-0001-0000-0000-000000000000%';


/*
* I1. INSERT a row using parameters with matching types
*/
declare @contactid uniqueidentifier;
set @contactid = '000000A7-0001-0000-0000-000000000000';
declare @accountid uniqueidentifier;
set @accountid = '00000001-0000-0000-0000-000000000000';
declare @createdate datetime;
set @createdate = getutcdate();

insert into [PGNP_SAMPLES]...contact (contactid, accountid, lastname, firstname, createdate, modifydate)
values (@contactid,@accountid,'Manilov','Femistoklius',@createdate,@createdate);

/*
* I2. INSERT a row using all literals
*/
insert into [PGNP_SAMPLES]...contact (contactid, accountid, lastname, firstname, createdate, modifydate)
values ('000000A8-0001-0000-0000-000000000000','00000001-0000-0000-0000-000000000000','Manilov','Alkid','20080422 11:14:55','20080422 11:14:55');

/*
* U1. UPDATE a row by given uuid
*/
declare @contactid uniqueidentifier;
set @contactid = '00000007-0001-0000-0000-000000000000';
declare @accountid uniqueidentifier;
set @accountid = '00000020-0000-0000-0000-000000000000';

update [PGNP_SAMPLES]...contact set accountid=@accountid where contactid=@contactid;

/*
* U2. UPDATE rows using uuid literals
*/
update [PGNP_SAMPLES]...contact set accountid='00000033-0000-0000-0000-000000000000'
where contactid like '00000008-0001-0000-0000-000000000000%';

/*
* U3. UPDATE all records (no WHERE clause)
*/
declare @modifydate datetime;
set @modifydate = getutcdate();

update [PGNP_SAMPLES]...contact set modifydate=@modifydate;

/*
* U4. UPDATE xml field for row with composite primary key
*/
update [PGNP_SAMPLES]...gadgets set features='<FEATURES>Nokia 1234-M</FEATURES>' where productid=10;

update [PGNP_SAMPLES]...eventlog set message='qwerty'
where message like 'test message 1%';

/*
* D1. DELETE a row by given uuid
*/
declare @contactid uniqueidentifier;
set @contactid = '00000007-0001-0000-0000-000000000000';

delete from [PGNP_SAMPLES]...contact where contactid=@contactid;

/*
* D2. DELETE rows uuid literal wildcard
*/
delete from [PGNP_SAMPLES]...contact where contactid like '00000008-0001-0000-0000-000000000000%';
delete from [PGNP_SAMPLES]...contact where contactid like '000000A%';

The alternative syntax (openquery) is also supported, e.g.

Code: Select all

select * from openquery(PGNP_SAMPLES, 'select * from contact');

[tysonfmr]

Hi,

I tried as per your suggestion, but the connection failed.
Error: Cannot find data source, cannot connect to DB.
The PostGreSQL is installed on an another machine:FTCNSSLAB001.
I am trying to access it via FTCNSSQA001 where i have MS SQL 2005 installed.
I downloaded the PGNP exe file this morning and ran it.

Here is my code:

/****** Object: LinkedServer [FTCNSSLAB001WIN] Script Date: 09/03/2009 09:35:16 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FTCNSSLAB001WIN', @srvproduct=N'PGNP', @provider=N'PGNP', @datasrc=N'FTCNSSLAB001WIN', @provstr=N'PORT=5483;', @catalog=N'linkedtest'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FTCNSSLAB001WIN',@useself=N'False',@locallogin=NULL,@rmtuser='myuser',@rmtpassword='mypassword'

GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FTCNSSLAB001WIN', @optname=N'use remote collation', @optvalue=N'true'[/color]


Please suggest, how to make this work.

Thank you,
Tysonfrm

[Moderator]

I've noticed from the first glance that @datasrc=N'FTCNSSLAB001WIN' should actualy be @datasrc=N'FTCNSSLAB001' (no WIN in the end). The @datasrc is name of computer with PostgreSQL server.

Other than that it looks good. Please do not forget to check "Allow inprocess" option.

[tysonfmr]

The servername which hosts the postgresql is named as 'ftcnsslab001win' the 'win' is a part of the computer name itself.

[Moderator]

Have you enabled remote access to Postgres server in pg_hba.conf?

[jmfreier]

How would you configure in a situation where the remote PostgreSQL server has a specific db that your tables reside in? The linked server gets created just fine, but I'm not able to get any information back.

Just using the impartial 4 dot notation with the server name in brackets, followed by placeholders and then the table name is not working in my situation:

[myPostgreSQLserver]...test_data.

Typically for SQL you would do the following:

[linked_server_name].[db_name].[user].[tbl_name]

Example:
[mySQLServer].test_db.dbo.test_data

[Moderator]

hi jmfreier,
Catalog parameter is responsible for specifying Postgres database name.
There could be several reasons why Linked Server does not return data.
Try alternative syntax:
Select * from Openquery(MYLINKEDSERVER, 'select * from test_data')

Assuming that Allow inprocess is checked, it should run fine.

[jmfreier]

But that's the odd thing, I know the catalog is there, I know the user it's under, I know the db the catalog is contained in and the user account I'm using has the right permissions (we use other applications that do not have any issues).

I'll try the openquery method, but I'm just wondering if there's something I'm missing. And yes, Allow InProcess is enabled.

----edit-----
I now get the following error:

OLE DB provider "PGNP" for linked server "IBNTEST2" returned message "".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "PGNP" for linked server "IBNTEST2". The provider supports the interface, but returns a failure code when it is used.

[Moderator]

Jason,
I'm almost positive that there is an issue in configuring the Linked Server. Could you please send us script that you used for creation of the Linked Server and sample table schema?

I hope we can either find reason of the issue in the configuration or will reproduce bug in provider if any.

Thank you!
Konstantin

[jmfreier]

Sure, sorry I didn't respond sooner but I have been pretty busy at work on some other items. I'll get the script sent today so you can look at it.

-Jason

-Script below (edited out username and password for security)-

Code: Select all

-- 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'IBN_TEST';
set @PRODUCT_NAME = N'PGNP';
set @PGPROVIDER = N'PGNP';
set @DATA_SOURCE = N'qs395.pair.com';
set @CN_STR = 'PORT=5432;'
set @SAMPLE_CATALOG = N'ibn_healthpartners';

/* 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'AQerazfxdgW',@rmtpassword='!@#$%aSASDE5'

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'