Sql Server 2000 to Postgres 9.1.3 Data Type Conversion Issue

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

Sql Server 2000 to Postgres 9.1.3 Data Type Conversion Issue

Postby sridiculous » Mon May 07, 2012 2:04 am

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: Select all
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: Select all
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: Select all
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.
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby Moderator » Wed May 16, 2012 8:36 pm

We will be working on the issues in the next few days. We'll let you know about progress. Thank you!
User avatar
Moderator
Site Admin
 
Posts: 303
Joined: Wed Oct 29, 2008 11:27 pm

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby sridiculous » Thu May 24, 2012 1:20 am

Any updates on this issue?
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby Moderator » Thu May 24, 2012 10:33 pm

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

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby sridiculous » Fri May 25, 2012 5:27 am

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.
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby Moderator » Sat May 26, 2012 11:25 am

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:
"The data type NUMERIC does not exist. Verify the supported data types and mappings by querying msdb.dbo.sysdatatypemappings."

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:
Ad hoc updates to system catalogs are not allowed.

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

I'm still trying to find an old MSSQL 2000.
User avatar
Moderator
Site Admin
 
Posts: 303
Joined: Wed Oct 29, 2008 11:27 pm

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby sridiculous » Sat May 26, 2012 11:03 pm

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.
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby sridiculous » Wed Jun 06, 2012 7:56 am

Are you still looking into it to solve this issue? Please help...
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby Moderator » Sun Jun 10, 2012 11:54 pm

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

Re: Sql Server 2000 to Postgres 9.1.3 Data Type Conversion I

Postby sridiculous » Wed Jun 13, 2012 8:20 am

Thanks, I will wait to see if you can figure it out as long as there is a definitive timeline.
sridiculous
 
Posts: 9
Joined: Mon May 07, 2012 1:57 am

Next

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

Who is online

Users browsing this forum: No registered users and 2 guests