I downloaded the trial version of PGNP and installed it only my server. I then created a Linked Server pointing to my Postgres Server by following the developer guide. I am trying to setup replication from MS SQL (2000) to Postgres using this Linked Server and Heterogenous Subscription options. Everything seems to be working fine, except for some data type conversion issues.
Datetime gets converted to Timestamp with timezone
bit gets converted to character(1)
decimal gets converted to character(40)
I was able to get the datetime and bit get converted correctly by adding the mapping explicitly to the 'msdb.dbo.MSDatatype_mappings' table by using the following stored procedures.
Code:
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'datetime', 'timestamp without time zone', 26, 0, 1
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'bit', 'boolean', 1, 0, 1
For the bit to boolean conversion, I also had to ensure that Postgres did an implicit cast from int to boolean as SQL Server treats bit as 0 & 1 and Postgres treats it as true and false. So, I did the following.
Code:
UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
SELECT c.oid
FROM pg_cast c
inner join pg_type src ON src.oid = c.castsource
inner join pg_type tgt ON tgt.oid = c.casttarget
WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%'
)
The only datatype I still have an issue with is decimal to numeric conversion. By default the datatype created on Postgres is character. I tried the same approach as above, by forcing it to both numeric and to decimal.
Code:
exec dbo.sp_add_datatype_mapping 'PostgreSQL', 'decimal', 'numeric, 255, 3, 1
I had some success where in the table being created in Postgres had the correct data type (numeric (precision, scale)), however, when the distributor tries to copy the data, it throws the following error:
"Invalid Parameters"
Any help on solving this would be greatly appreciated.
