cant get the correct num of rows from Oracle. in 2 SQL servers ( sql 7.0 and SQL 2000)

I ve got an Oracle 9. server running on AIx.
and One SQL Server 2000 Sp 3on w2k
and SQL server 7 on Winnt 4.0
i did link this Oracle server on both SQL serevrs. When run an simple SQL
query i got different results.
Num of rows are always different In SQL server 7.0 i got 49430 rows( when i
check in oracle its correct)
In SQL SErver 2000 i got
49336 rows.
Any idea ?
Possibly handling of NULLS can cause a difference. Can you post the two queries?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:%23szluxctEHA.3156@.TK2MSFTNGP12.phx.gbl...
>I ve got an Oracle 9. server running on AIx.
> and One SQL Server 2000 Sp 3on w2k
> and SQL server 7 on Winnt 4.0
>
> i did link this Oracle server on both SQL serevrs. When run an simple SQL
> query i got different results.
> Num of rows are always different In SQL server 7.0 i got 49430 rows( when i
> check in oracle its correct)
> In SQL SErver 2000 i got
> 49336 rows.
> Any idea ?
>
>
|||Ty tibor..
Query is the same
select count(*) from openquery(baanb,'select t$item from baan.ttiitm001501')
in SQL server 7.0 this query returns 49439
in SQL server 2000 it returns 49340
and not only in this query .. if number of rows returned from the openquery
statement get biggers number of lost rows get bigger too
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oi34g5ctEHA.636@.TK2MSFTNGP09.phx.gbl...
> Possibly handling of NULLS can cause a difference. Can you post the two
queries?[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:%23szluxctEHA.3156@.TK2MSFTNGP12.phx.gbl...
SQL[vbcol=seagreen]
when i[vbcol=seagreen]
got
>
|||Is there a tool like Profiler available for Oracle? If so, can you trace the query submitted to
Oracle and see if they are the same? What if you execute the same query directly on the Oracle
Server? What number of rows is the correct number?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:%23pKPi9ctEHA.2520@.tk2msftngp13.phx.gbl...
> Ty tibor..
> Query is the same
> select count(*) from openquery(baanb,'select t$item from baan.ttiitm001501')
> in SQL server 7.0 this query returns 49439
> in SQL server 2000 it returns 49340
>
> and not only in this query .. if number of rows returned from the openquery
> statement get biggers number of lost rows get bigger too
>
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oi34g5ctEHA.636@.TK2MSFTNGP09.phx.gbl...
> queries?
> SQL
> when i
> got
>
|||This Query is just an Example..
When i run this query in Oracle number of rows is the same as SQL server 7.0
it returns 49439 ..
Both MDAC versions are 2.8
Both Oracle ODBC Drivers r same 9.2.0.5
i did try all attributes.. and check hundreds of pages from MSDN or OTN but
no solution yet ..
when i try to get records from Oracle there is always 99 rows difference.
between SQL 2000 and SQL 7.0
Not according to rowsize, or table or other things...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23c3xDdtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> Is there a tool like Profiler available for Oracle? If so, can you trace
the query submitted to
> Oracle and see if they are the same? What if you execute the same query
directly on the Oracle[vbcol=seagreen]
> Server? What number of rows is the correct number?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:%23pKPi9ctEHA.2520@.tk2msftngp13.phx.gbl...
baan.ttiitm001501')[vbcol=seagreen]
openquery[vbcol=seagreen]
in[vbcol=seagreen]
i
>
|||The reason I asked to post the query was to see whether there is anything in the query that can
explain the difference. As I said in the original post, one such possibility id difference regarding
handling of NULLs.
Also, did you try to catch the SQL statement submitted form SQL Server to Oracle to see if they are
the same from 7.0 and 2000?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:usBSPMdtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> This Query is just an Example..
> When i run this query in Oracle number of rows is the same as SQL server 7.0
> it returns 49439 ..
> Both MDAC versions are 2.8
> Both Oracle ODBC Drivers r same 9.2.0.5
> i did try all attributes.. and check hundreds of pages from MSDN or OTN but
> no solution yet ..
> when i try to get records from Oracle there is always 99 rows difference.
> between SQL 2000 and SQL 7.0
> Not according to rowsize, or table or other things...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23c3xDdtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> the query submitted to
> directly on the Oracle
> baan.ttiitm001501')
> openquery
> in
> i
>
|||Hi Tibor..
I did Trace it on Oracle..
In trace Oracle Send number of 49439 rows for all queries, but SQL 200 and
SQL 7.0 get rows different.
And Also i did try it with an Test Oracle Server..
Its the same..
SQL 2000 get 99 rows less then SQL 7.0
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e0kGfVdtEHA.2072@.tk2msftngp13.phx.gbl...
> The reason I asked to post the query was to see whether there is anything
in the query that can
> explain the difference. As I said in the original post, one such
possibility id difference regarding
> handling of NULLs.
> Also, did you try to catch the SQL statement submitted form SQL Server to
Oracle to see if they are[vbcol=seagreen]
> the same from 7.0 and 2000?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:usBSPMdtEHA.4040@.TK2MSFTNGP09.phx.gbl...
7.0[vbcol=seagreen]
but[vbcol=seagreen]
difference.[vbcol=seagreen]
in[vbcol=seagreen]
trace[vbcol=seagreen]
wrote[vbcol=seagreen]
two[vbcol=seagreen]
simple[vbcol=seagreen]
rows([vbcol=seagreen]
2000
>
|||Hi Melih,
I've seen this once and i my case it turns out to be a missing primary key
( which was my fault, I accidentely deleted it ). Any Indexes involved ?
Regards,
Meinhard
"Melih SARICA" <melihsarica@.hotmail.com> schrieb im Newsbeitrag
news:elS%23DKetEHA.2072@.tk2msftngp13.phx.gbl...
> Hi Tibor..
> I did Trace it on Oracle..
> In trace Oracle Send number of 49439 rows for all queries, but SQL 200 and
> SQL 7.0 get rows different.
> And Also i did try it with an Test Oracle Server..
> Its the same..
> SQL 2000 get 99 rows less then SQL 7.0
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:e0kGfVdtEHA.2072@.tk2msftngp13.phx.gbl...
> in the query that can
> possibility id difference regarding
> Oracle to see if they are
> 7.0
> but
> difference.
> in
> trace
> wrote
> two
> simple
> rows(
> 2000
>
|||I was referring to tracing the SQL query, not the result.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:elS%23DKetEHA.2072@.tk2msftngp13.phx.gbl...
> Hi Tibor..
> I did Trace it on Oracle..
> In trace Oracle Send number of 49439 rows for all queries, but SQL 200 and
> SQL 7.0 get rows different.
> And Also i did try it with an Test Oracle Server..
> Its the same..
> SQL 2000 get 99 rows less then SQL 7.0
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e0kGfVdtEHA.2072@.tk2msftngp13.phx.gbl...
> in the query that can
> possibility id difference regarding
> Oracle to see if they are
> 7.0
> but
> difference.
> in
> trace
> wrote
> two
> simple
> rows(
> 2000
>
|||its not about a specific table. I got this result in every Query.
SQL 2000 get 99 rows less then the rowcount of the table
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> wrote in message
news:2tknamF207lttU1@.uni-berlin.de...[vbcol=seagreen]
> Hi Melih,
> I've seen this once and i my case it turns out to be a missing primary key
> ( which was my fault, I accidentely deleted it ). Any Indexes involved ?
> Regards,
> Meinhard
> "Melih SARICA" <melihsarica@.hotmail.com> schrieb im Newsbeitrag
> news:elS%23DKetEHA.2072@.tk2msftngp13.phx.gbl...
and[vbcol=seagreen]
anything[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
SErver
>