Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
Showing posts with label 4-part-tablenames. Show all posts
Showing posts with label 4-part-tablenames. Show all posts
Friday, March 30, 2012
OPENQUERY vs 4-part-tablenames with linked server
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
JochenJochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delet
e all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that th
e server answers with correct results when I start the query on itsself (as
whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520
@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I s
uggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||In this case OPENQUERY should return the same result as the straight query..
.
Do as Tibor says, and check the query plan for both to see if you can learn
anything from that...Also check/play with the collation order options on the
linked server(although that should not matter with an integer comparison.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
JochenJochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delet
e all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that th
e server answers with correct results when I start the query on itsself (as
whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520
@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I s
uggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||In this case OPENQUERY should return the same result as the straight query..
.
Do as Tibor says, and check the query plan for both to see if you can learn
anything from that...Also check/play with the collation order options on the
linked server(although that should not matter with an integer comparison.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
OPENQUERY vs 4-part-tablenames with linked server
This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table = where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero = hits.
Can anybody help?
Jochen
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
what is the difference between =
a) select * from = server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, = 'select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the = correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_000A_01C45DBF.3F502450--This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition =connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Jochen
That's strange. I hace just tested it on my box and =it works fine
As far as I know when we use OPENQUERY SQL Server =opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" wrote =in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0261_01C45DD6.DCF0CE50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I =delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that =the server answers with correct results when I start the query on =itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag =news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition =connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from =database.owner.table where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Here are some additional =information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but =the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. Withselect * from server.database.owner.table where [id] < 6000the result =table ist still empty. Withselect * from server.database.owner.table where =[id] < 6001all rows are given back with [id] < 60001. Further it is =strange that the server answers with correct results when I start the query on =itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" schrieb im =Newsbeitrag news:Os4al2bXEHA.2520=@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box =and it works fine
As far as I know when we use OPENQUERY SQL Server =opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" =wrote in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0043_01C45DD2.46A106D0--|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brüggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||This is a multi-part message in MIME format.
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In this case OPENQUERY should return the same result as the straight =query...
Do as Tibor says, and check the query plan for both to see if you can =learn anything from that...Also check/play with the collation order =options on the linked server(although that should not matter with an =integer comparison.)
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In this case OPENQUERY should return =the same result as the straight query...
Do as Tibor says, and check the query =plan for both to see if you can learn anything from that...Also check/play with the =collation order options on the linked server(although that should not matter with =an integer comparison.)
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Jochen Br=FCggemann" wrote =in message news:udF3I4aXEHA.736@.T=K2MSFTNGP10.phx.gbl...
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0050_01C45DAD.F4088160--
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table = where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero = hits.
Can anybody help?
Jochen
--=_NextPart_000_000A_01C45DBF.3F502450
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
what is the difference between =
a) select * from = server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, = 'select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the = correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_000A_01C45DBF.3F502450--This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition =connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0261_01C45DD6.DCF0CE50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Jochen
That's strange. I hace just tested it on my box and =it works fine
As far as I know when we use OPENQUERY SQL Server =opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann"
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0261_01C45DD6.DCF0CE50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I =delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that =the server answers with correct results when I start the query on =itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag =news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition =connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from =database.owner.table where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0043_01C45DD2.46A106D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Here are some additional =information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but =the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. Withselect * from server.database.owner.table where [id] < 6000the result =table ist still empty. Withselect * from server.database.owner.table where =[id] < 6001all rows are given back with [id] < 60001. Further it is =strange that the server answers with correct results when I start the query on =itsself (as whith OPENQUERY).
Jochen
"Uri Dimant"
Jochen
That's strange. I hace just tested it on my box =and it works fine
As far as I know when we use OPENQUERY SQL Server =opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Br=FCggemann"
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0043_01C45DD2.46A106D0--|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brüggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||This is a multi-part message in MIME format.
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In this case OPENQUERY should return the same result as the straight =query...
Do as Tibor says, and check the query plan for both to see if you can =learn anything from that...Also check/play with the collation order =options on the linked server(although that should not matter with an =integer comparison.)
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Br=FCggemann" <brueggemann@.ifap.de> wrote in message =news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, 'select * from database.owner.table =where [id] =3D 15')
I have the effect that b) gives the correct result while a) has zero =hits.
Can anybody help?
Jochen
--=_NextPart_000_0050_01C45DAD.F4088160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In this case OPENQUERY should return =the same result as the straight query...
Do as Tibor says, and check the query =plan for both to see if you can learn anything from that...Also check/play with the =collation order options on the linked server(although that should not matter with =an integer comparison.)
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Jochen Br=FCggemann"
Hi,
what is the difference between =
a) select * from =server.database.owner.table where [id] =3D 15
and
b) select * from openquery(server, ='select * from database.owner.table where [id] =3D 15')
I have the effect that b) gives the =correct result while a) has zero hits.
Can anybody help?
Jochen
--=_NextPart_000_0050_01C45DAD.F4088160--
Subscribe to:
Posts (Atom)