Sql Server 2000 to Postgres 9.1.3 Data Type Conversion Issue

More
11 years 9 months ago #6453 by sridiculous
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.

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

More
11 years 9 months ago #6463 by Moderator
We will be working on the issues in the next few days. We'll let you know about progress. Thank you!

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

More
11 years 9 months ago #6472 by sridiculous
Any updates on this issue?

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

More
11 years 9 months ago #6474 by Moderator
I tried to add the mapping, but the following error was returned:
Could not find stored procedure 'dbo.sp_add_datatype_mapping'.

Does the procedure exist in SQL Server 2005 or 2008? I will find computer with SQL Server 2000 next, and will give it a try. Any details will be appreciated.

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

More
11 years 9 months ago #6475 by sridiculous
I think in 2005 it is sp_setdefaultdatatypemapping. The following link provides the details msdn.microsoft.com/en-us/library/ms184298.aspx . Please let me know if you need any further information.

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

More
11 years 9 months ago #6482 by Moderator
I tried to execute the following commands in SQL Server 2008:
insert into msdb.dbo.MSdbms(dbms) values ('POSTGRESQL');

sp_setdefaultdatatypemapping
@source_type = 'decimal'
, @source_dbms = 'MSSQLSERVER'
, @destination_dbms = 'POSTGRESQL'
, @destination_type = 'numeric'
, @destination_precision = 255
, @destination_nullable = 1
, @source_precision_min = 1
, @source_precision_max = 38
, @source_scale_min = 0
, @source_scale_max = 38
, @source_nullable = 1
, @destination_scale = -1
, @destination_length = -1

But the error returned:
[i:17l7lt6g][color=#FF0000:17l7lt6g]"The data type NUMERIC does not exist. Verify the supported data types and mappings by querying msdb.dbo.sysdatatypemappings."[/color:17l7lt6g][/i:17l7lt6g]

Manual mapping creation was not allowed:
insert into msdb.dbo.sysdatatypemappings(source_dbms,source_type,source_precision_min,source_precision_max,source_scale_min,source_scale_max,source_nullable,source_createparams,
destination_dbms,destination_type,destination_precision,destination_scale,destination_nullable,destination_createparams,is_default)
values ('MSSQLSERVER','decimal',1,38,0,38,1,3,'POSTGRESQL','number',-1,-1,1,3,1)
returned error:
[i:17l7lt6g][color=#FF0000:17l7lt6g]Ad hoc updates to system catalogs are not allowed.[/color:17l7lt6g][/i:17l7lt6g]

Googling did not help much. Are some of the parameters incorrect?

I'm still trying to find an old MSSQL 2000.

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

Time to create page: 0.166 seconds
Powered by Kunena Forum