Avatar of 60MXG
60MXG asked on

Oracle Database and Microsoft SQL Link Server rocords not matching

I have a link server setup on my Microsoft SQL 2005 to a Oracle database.  The problem I have is that the record showing on the Oracle SQL Developer is 1458 rows and once imported into Microsoft SQL server the record is 1359 rows.  Can anyone tell me what's wrong?  
Microsoft SQL Server 2005Oracle DatabaseDatabases

Avatar of undefined
Last Comment
60MXG

8/22/2022 - Mon
Bhavesh Shah

Hi,

can you share us your code.

there are different possibilities.

Primary Key or Large Data....
Qlemo

Certainly you did not issue an DISTINCT when importing?
ASKER
60MXG

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
60MXG

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]
')
Bhavesh Shah

hi,

there is any trigger on database.dbo.table1

Might that prevent to insert rows.

do you getting any errors?
ASKER
60MXG

There is no trigger and no errors when importing!  Not sure if the ODBC driver can cause this problem.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

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

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.
ASKER
60MXG

sorry was 1458 for Oracle and 1359 for the linked database (Microsoft database)!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Qlemo

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

which will be 1359 for the count!  
Qlemo

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
60MXG

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)
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
60MXG

Let me give that a try!
ASKER
60MXG

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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Qlemo

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.
ASKER
60MXG

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