Oracle Database and Microsoft SQL Link Server rocords not matching

Hi,

can you share us your code.

there are different possibilities.

Primary Key or Large Data....

Certainly you did not issue an DISTINCT when importing?

Here is the code using at the Oracle SQL Developer for testing!

SELECT
      serial_num,
      sta_cd_assgn,
      sta_cd_rpting,
      wing,
      mds,
      alpha_sr_status,
      etic_edic_dt,
      sch_m_dt,
      blk_in_dt,
      tt_code,
      remks_lst,
      park_loc,
      transient_base,
      mission_nbr,
      due_home_dt,
      fuel_on_board,
      config_a_c,
      msn_category,
      i_d_number,
      a_c_assign_cd,
      command_cd,
      arrival_dt,
      lst_hsc,
      organ_possess,
      call_sign,
      remarks_data,
      majcom,
      nitro_l,
      nitro_r,
      lox_crew,
      lox_pac,
      dt_lst_preflight,
      srd,
      weap_sys_cntl,
      curr_a_c_stat,
      fuel_req,
      status_wuc,
      next_hsc,
      config_next_miss,
      depart_date,
      ground_time,
      date_next_iso,
      armor,
      ads,
      next_HSC_dd,
      iso_day_incr,
      iso_due_date,
      priority,
      status_chng_dt,
      as_of_dt
FROM database.table

HERE is the code use on the SSIS (Microsoft SSIS)

Insert into database.dbo.table1
(
[comma separated list of columns]
)
select *
from
openquery(DISAORACLE,
'SELECT
      [comma separated list of columns]
FROM database.table [tabel alias]
')

hi,

there is any trigger on database.dbo.table1

Might that prevent to insert rows.

do you getting any errors?

There is no trigger and no errors when importing!  Not sure if the ODBC driver can cause this problem.

I would compare
select count(*) from openquery ( ... )
with what you get in SQL Developer.

I did the count too!  The results are the same for Oracle SQL developer is 1459 and the Microsoft Openquery is 1359.  

So the problem now is when it go through ODBC connection, records are missing.  I used Oracle client 11g home edition for ODBC driver and not sure if that will caused the problem.

sorry was 1458 for Oracle and 1359 for the linked database (Microsoft database)!

That's really strange, since you do not perform any restriction. What if you exec
   select count(*) from [connection]..[database].[table]
?

which will be 1359 for the count!  

Do you use the "Oracle Provider for OLEDB", or MS OLE DB for Oracle, or "OLEDB for ODBC" with an ODBC DSN? The first one is the native, and should be used if possible.

Used Oracle in OraClient11g home edition!   for ODBC (System DSN)  which I created called ORAODBC
then at the Microsoft SQL Server linked server I used Microsoft OLE DB Provider Driver connect to ODBC (ORAODBC)

Let me give that a try!

what do I put in  the in Oracle Provider for OLE DB driver interface in Linked Server
OralceProduct name?  Data Source?  and Provider string?

given following information
I have these information when using the Oracle connection!
---------------------------------------
Hostname:  myhostname
SID: CDBP
Port: 1521

The Oracle connection details
myname@//myhostname:1521/CDBP
----------------------------------------------

Thanks!

Provider: Oracle Provider for OLE DB
Product name:  Arbitrary, e.g. just "Oracle"
Data Source:   myhostname:1521/CDBP
(or, since 1521 is the default port)
Data Source:   myhostname/CDBP

Leave all other settings empty on that page.

Under security, use the option at the bottom to supply  myname  and the password.

Got it to work!  It was the ODBC driver causing it.  I used the OLE DB driver and it is working!  Thank you.