Sql Server 2000 to Postgres 9.1.3 Data Type Conversion Issue

More
7 years 4 months ago #6453 by sridiculous
sridiculous created the topic: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion Issue
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.
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.
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.
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
7 years 4 months ago #6463 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
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
7 years 3 months ago #6472 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Any updates on this issue?

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

More
7 years 3 months ago #6474 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
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
7 years 3 months ago #6475 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
I think in 2005 it is sp_setdefaultdatatypemapping. The following link provides the details http://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
7 years 3 months ago #6482 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
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.

More
7 years 3 months ago #6483 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
How about trying to map it to 'decimal' instead of 'numeric'. The other option would probably be to define a custom data type of 'numeric' in the SQL Server. On SQL Server 2000, there are no such errors.

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

More
7 years 3 months ago #6504 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Are you still looking into it to solve this issue? Please help...

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

More
7 years 3 months ago #6565 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Yes. Our old SQL Server 2000 machine was no longer working; so, I had to rebuild it. However, I have not been able to work on the issue lately. Will do in the next few days (I hope). Thank you for your patience!

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

More
7 years 3 months ago #6572 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Thanks, I will wait to see if you can figure it out as long as there is a definitive timeline.

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

More
7 years 3 months ago #6573 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
We were able to reproduce the issue on SQL Server 2000. We are working on the fix.

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

More
7 years 2 months ago #6637 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Thanks for the update, any ETA on when the fix would be available?

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

More
7 years 2 months ago #6638 by Moderator
Moderator replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
I cannot tell exact date when the fix will be available. The issue is in developers queue now. I'll update you when more information is available. Thank you for your patience.

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

More
7 years 1 month ago #6646 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Any updates on the status yet? Its been a while and the management is now on my case to get it working asap. Please provide an update and an ETA so that I can convey the same to them.

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

More
7 years 1 month ago #6647 by admin
admin replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
We are sorry for the delay in answering your question, and providing the fix. Unfortunately, we had to work on other higher priority defects during last few weeks. I will try to elevate the issue priority, and put more attention on it. Please check this thread during the next week for more information (or contact Support via e-mail). Thank you for your patience.

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

More
7 years 1 month ago #6648 by sridiculous
sridiculous replied the topic: Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I
Thanks for the update, I will keep watching in hope of getting a resolution this week, or at least for an update.

--Sri

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

Time to create page: 0.081 seconds
Powered by Kunena Forum