Setting up a SQL Server to connect connect to Oracle

[mit99mh]

I'm migrating DTS packages that copy data from Oracle to a Sql server. I've installed the Oracle 10 G client on the server and I'm using the Microsoft Ole DB for Oracle provider to connect to an oralce database - the package runs but only selects part of the data and excludes data of type varchar2 - on the old server the package runs completely - has anyone else experienced this before? Am I missing something?

Any help appreciated.

 

[ptheriault]

There is a known issue with SQL and Oracle that can cause this problem. I ran into it with datatypes of numerics. Here is an example of the work around. I had to conver everything to char then back to the correct datatype in SQL.

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] MavResource.dbo.MONTHLY_EARNED(
ERP_ID 
,POLICY_ID
,YEAR_EARN 
,MONTH_EARN 
,STATE 
,ELEMENT_NAME 
,CLASS_CODE
,WRITTEN_PREMIUM
,EARNED_PREMIUM 
,WRITTEN_EXPOSURE 
,EARNED_EXPOSURE 
,EVAL_PERIOD
)
[COLOR=blue]select[/color]
	[COLOR=#FF00FF]convert[/color](bigint, ERP_ID)
	,[COLOR=#FF00FF]convert[/color](bigint, POLICY_ID)
	,[COLOR=#FF00FF]convert[/color](bigint, YEAR_EARN)
	,[COLOR=#FF00FF]convert[/color](bigint, MONTH_EARN)
	,STATE
   ,ELEMENT_NAME
  ,CLASS_CODE  
  ,[COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]money[/color],WRITTEN_PREMIUM)
  ,[COLOR=#FF00FF]convert[/color]([COLOR=blue]money[/color],EARNED_PREMIUM )
  ,WRITTEN_EXPOSURE 
  ,EARNED_EXPOSURE 
  ,[COLOR=#FF00FF]convert[/color](bigint, EVAL_PERIOD)
[COLOR=blue]from[/color] OPENQUERY
(RSDEV,
[COLOR=red]'select
[/color]    [COLOR=#FF00FF]to_char[/color](ERP_ID) [COLOR=blue]as[/color] ERP_ID
	,[COLOR=#FF00FF]to_char[/color](POLICY_ID) [COLOR=blue]as[/color] policy_id
	,[COLOR=#FF00FF]to_char[/color](YEAR_EARN) [COLOR=blue]as[/color] year_earn
	,[COLOR=#FF00FF]to_char[/color](MONTH_EARN) [COLOR=blue]as[/color] month_earn
	,STATE
   ,ELEMENT_NAME
  ,CLASS_CODE  
  ,[COLOR=#FF00FF]to_char[/color](WRITTEN_PREMIUM) [COLOR=blue]as[/color] written_premium
  ,[COLOR=#FF00FF]to_char[/color](EARNED_PREMIUM ) [COLOR=blue]as[/color] earned_premium
  ,WRITTEN_EXPOSURE 
  ,EARNED_EXPOSURE 
  ,[COLOR=#FF00FF]to_char[/color](EVAL_PERIOD) [COLOR=blue]as[/color] eval_period
   [COLOR=blue]FROM[/color] MONTHLY_EARNED
   [COLOR=blue]WHERE[/color] EVAL_PERIOD = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](EVAL_PERIOD)
                         [COLOR=blue]FROM[/color] MONTHLY_EARNED)[COLOR=red]'
[/color]   )

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mit99mh]

Thanks for taking the time to reply - the field that's missing is VARCHAR2 in the oracle database and I'm trying to save it as VARCHAR - the strange thing is the package runs fine on the old server - I'll try the convert.

Thanks once again