MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!
Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online.
Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you a
re using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you n
eed to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we
have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WA
RNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>sql

MDAC error on VFP linked server

I have a linked server pointing to a folder where foxpro tables exist.
The tables are listed when I click on the linked server/tables.
However I get this error when in query analyer I say: select * from
vfplinked...clients
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Any help? What am I missing? I got this working before.
This is on SQLServer 2000
Thanks everyone!Hi Michael,
Have you tried using the latest Visual FoxPro OLE DB data provider? It's
downloadable from
http://msdn.microsoft.com/vfoxpro/d...es/default.aspx and works
with all versions of FoxPro tables.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Michael" <Michael.Rodriguez@.nospam> wrote in message
news:etkRi$bBFHA.2180@.TK2MSFTNGP10.phx.gbl...
> I have a linked server pointing to a folder where foxpro tables exist.
> The tables are listed when I click on the linked server/tables.
> However I get this error when in query analyer I say: select * from
> vfplinked...clients
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
>
> Any help? What am I missing? I got this working before.
> This is on SQLServer 2000
> Thanks everyone!
>

MDAC error on VFP linked server

I have a linked server pointing to a folder where foxpro tables exist.
The tables are listed when I click on the linked server/tables.
However I get this error when in query analyer I say: select * from
vfplinked...clients
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Any help? What am I missing? I got this working before.
This is on SQLServer 2000
Thanks everyone!
Hi Michael,
Have you tried using the latest Visual FoxPro OLE DB data provider? It's
downloadable from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx and works
with all versions of FoxPro tables.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Michael" <Michael.Rodriguez@.nospam> wrote in message
news:etkRi$bBFHA.2180@.TK2MSFTNGP10.phx.gbl...
> I have a linked server pointing to a folder where foxpro tables exist.
> The tables are listed when I click on the linked server/tables.
> However I get this error when in query analyer I say: select * from
> vfplinked...clients
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
>
> Any help? What am I missing? I got this working before.
> This is on SQLServer 2000
> Thanks everyone!
>
sql

MDAC and Oracle9

Hei guys, today we have encountered a problem with connectivity from MS SQL
server to ORACLE. We have been using MDAC for linked Oracle DB and all
worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link doesn't
work any longer. It is created (even dropped and recreated), but when we try
SELECT from whatever table, it returns known Error 7356: "OLE DB provider
'MSDASQL' supplied inconsistent metadata for a column.Metadata information
was changed at execution time......"
We used time to go through newsgroups and found references to this error
message. For example
http://support.microsoft.com/defaul...kb;en-us;264012 or
http://groups.google.com/groups?hl=...hreadm=UORMZEmf
DHA.2332%40cpmsftngxa06.phx.gbl&rnum=9&prev=/groups%3Fhl%3Dcs%26lr%3D%26ie%3
DUTF-8%26oe%3DUTF-8%26selm%3DUORMZEmfDHA.2332%2540cpmsftngxa06.phx.gbl%26rnu
m%3D9
or
http://groups.google.com/groups?q=E...-8&oe=UTF-8&sel
m=OW5q0QuLDHA.1552%40TK2MSFTNGP10.phx.gbl&rnum=3 - just our problem but with
newer ORACLE vrsion.
All suggestions concern registry. We checked the registry but there are no
registry entries for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI on
the server. In addition, ORACLE 9 is not mentioned in articles we've found.
We run SQL Server 2000 and ORACLE 9.2.0.4.0
Any ideas how to fix this problem? Thanks in advance.
JiriHello. I am seeing a similar problem. I have SQL 2000 server running
Oracle 9i client 9.2.0.1 and linking to an Oracle 9.2.0.3 server. I
can create the link to the Oracle server, but when I do a SELECT
against the Oracle tables I get the error 7356 with the MSDAORA
provider, same Metadata references.
I followed this article but it still didn't work:
http://www.databasejournal.com/feat...cle.php/3290801
I have not found a solution yet either. I tried installing MDAC 2.8
but that didn't make a difference.
"feli" <jife@.email.cz> wrote in message news:<erFkIG3BEHA.684@.tk2msftngp13.phx.gbl>...[colo
r=darkred]
> Hei guys, today we have encountered a problem with connectivity from MS SQ
L
> server to ORACLE. We have been using MDAC for linked Oracle DB and all
> worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
> days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link doesn'
t
> work any longer. It is created (even dropped and recreated), but when we t
ry
> SELECT from whatever table, it returns known Error 7356: "OLE DB provider
> 'MSDASQL' supplied inconsistent metadata for a column.Metadata information
> was changed at execution time......"
> We used time to go through newsgroups and found references to this error
> message. For example
> http://support.microsoft.com/defaul...kb;en-us;264012 or
> [url]http://groups.google.com/groups?hl=cs&lr=&ie=UTF-8&oe=UTF-8&threadm=UORMZEmf[/ur
l]
> DHA.2332%40cpmsftngxa06.phx.gbl&rnum=9&prev=/groups%3Fhl%3Dcs%26lr%3D%26ie
%3
> DUTF-8%26oe%3DUTF-8%26selm%3DUORMZEmfDHA.2332%2540cpmsftngxa06.phx.gbl%26r
nu
> m%3D9
> or
> [url]http://groups.google.com/groups?q=Error+7356&hl=cs&lr=&ie=UTF-8&oe=UTF-8&sel[/ur
l]
> m=OW5q0QuLDHA.1552%40TK2MSFTNGP10.phx.gbl&rnum=3 - just our problem but wi
th
> newer ORACLE vrsion.
> All suggestions concern registry. We checked the registry but there are no
> registry entries for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI on
> the server. In addition, ORACLE 9 is not mentioned in articles we've found
.
> We run SQL Server 2000 and ORACLE 9.2.0.4.0
> Any ideas how to fix this problem? Thanks in advance.
> Jiri[/color]|||We are having, apparently, the same problem here. Also with Oracle
9.2.0.4.0.
We have tried Oracle's native driver, the MS OLE DB driver for Oracle
and the MS OLE DB Driver for ODBC Drivers using either the Microsoft
or Oracle ODBC drivers. We have had some success retriving data with
a linked server connection using Microsoft OLE DB Provider for ODBC
Drivers using Microsoft ODBC for Oracle {version 2.573.9030.00}. [
Not
sure what MDAC provided this]
All other methods fail with error messages like:
Server: Msg 7356, Level 16, State 1, Line 2
OLE DB provider 'OraOLEDB.Oracle' supplied inconsistent metadata for a
column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'MIN_EXTENTS'
(compile-time ordinal 12) of object '"SYS"."ALL_TABLES"' was reported
to have a DBTYPE of 130 at compile time and 5 at run time].
or
Server: Msg 7318, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' returned an invalid column definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid column definition.].
We are getting error messages like this from W2K Server SQL Server
2000 machines whether the Oracle 8i or 9i drivers are installed if the
linked target is Oracle 9.2.0.4.0.
We suspect that SQL Server 2000 is using old methods to communicate
with Oracle that are not appropriate for Oracle 9.2.0.4.0 and perhaps
other releases around that version. Since Oracle is working fine and
connections to it to retrieve data using a Windows script file are
successful [using ODBC and UDL files], my current suspicion is that a
patch will be required for MS SQL Server 2000 to work with more recent
versions of Oracle.
Anyone else having this experience or perhaps information on a fix for
this problem?|||Has anyone resolved this error?

quote:

---

[i] ... Error 7356: "OLE DB provider
'MSDASQL' supplied inconsistent metadata for a column.Metadata information w
as changed at execution time......"
[/B]

---

|||"feli" <jife@.email.cz> wrote in message
news:erFkIG3BEHA.684@.tk2msftngp13.phx.gbl...
> Hei guys, today we have encountered a problem with connectivity from MS
SQL
> server to ORACLE. We have been using MDAC for linked Oracle DB and all
> worked fine on ORACLE8 and early ORACLE9 DBs (for example 9.0.1.0.0). Some
> days ago, the ORACLE DB has been upgraded to 9.2.0.4.0 and the link
doesn't
> work any longer. It is created (even dropped and recreated), but when we
try
> SELECT from whatever table, it returns known Error 7356: "OLE DB provider
> 'MSDASQL' supplied inconsistent metadata for a column.Metadata information
> was changed at execution time......"
Do you have to use the MS supplied oledb driver - the Oracle supplied
provider works just fine for me.
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/

MDAC 2.8 on SQL 2000 Sp3a

Any reason why MDAC 2.8 should NOT be installed on the above server? A
colleague has an error linking a Excel spreadsheet (linked server) and the
MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
and that is the minimum required to add SP8! I thought MDAC 2.8 would get me
bang up to date with Jet.
Thanks for any input.
SimonJet is no longer included with MDAC. For Jet updates see
http://www.aspfaq.com/2342
http://www.aspfaq.com/
(Reverse address to reply.)
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:#7j$g7RXEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Any reason why MDAC 2.8 should NOT be installed on the above server? A
> colleague has an error linking a Excel spreadsheet (linked server) and the
> MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
> and that is the minimum required to add SP8! I thought MDAC 2.8 would get
me
> bang up to date with Jet.
> Thanks for any input.
> Simon
>

MDAC 2.8 on SQL 2000 Sp3a

Any reason why MDAC 2.8 should NOT be installed on the above server? A
colleague has an error linking a Excel spreadsheet (linked server) and the
MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
and that is the minimum required to add SP8! I thought MDAC 2.8 would get me
bang up to date with Jet.
Thanks for any input.
Simon
Jet is no longer included with MDAC. For Jet updates see
http://www.aspfaq.com/2342
http://www.aspfaq.com/
(Reverse address to reply.)
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:#7j$g7RXEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Any reason why MDAC 2.8 should NOT be installed on the above server? A
> colleague has an error linking a Excel spreadsheet (linked server) and the
> MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
> and that is the minimum required to add SP8! I thought MDAC 2.8 would get
me
> bang up to date with Jet.
> Thanks for any input.
> Simon
>
|||Jet is no longer included with MDAC. For Jet updates see
http://www.aspfaq.com/2342
http://www.aspfaq.com/
(Reverse address to reply.)
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:#7j$g7RXEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Any reason why MDAC 2.8 should NOT be installed on the above server? A
> colleague has an error linking a Excel spreadsheet (linked server) and the
> MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
> and that is the minimum required to add SP8! I thought MDAC 2.8 would get
me
> bang up to date with Jet.
> Thanks for any input.
> Simon
>

MDAC 2.8 on SQL 2000 Sp3a

Any reason why MDAC 2.8 should NOT be installed on the above server? A
colleague has an error linking a Excel spreadsheet (linked server) and the
MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
and that is the minimum required to add SP8! I thought MDAC 2.8 would get me
bang up to date with Jet.
Thanks for any input.
SimonJet is no longer included with MDAC. For Jet updates see
http://www.aspfaq.com/2342
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:#7j$g7RXEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Any reason why MDAC 2.8 should NOT be installed on the above server? A
> colleague has an error linking a Excel spreadsheet (linked server) and the
> MS site suggests Jet 4 SP8. However it claims I do not have Jet 4 SP3 yet
> and that is the minimum required to add SP8! I thought MDAC 2.8 would get
me
> bang up to date with Jet.
> Thanks for any input.
> Simon
>sql

max(tag) for pair of matched rows (was "Need help on query")

I've got a table of transactions which are linked up in pairs based on the column 'Ref'. 'F's are the identifiers of each transaction while 'S's points to the 'F' of its matching pair. I need to select all the transactions with the larger 'Tag' for each pair, can someone point me in the right direction? :confused:

Tag Ref Type
-- -- --
1 200 F
1 201 S
2 201 F
2 200 S
3 202 F
3 203 S
4 203 F
4 202 S
5 204 F
5 205 S
6 205 F
6 204 SIs this what you are after?

select t.tag, t.ref, t.type
from t
join (select max(tag)as tag, ref from t
group by ref) b
on t.tag = b.tag
and t.ref = b.ref

giving you the result set

6 205 F
6 204 S
4 203 F
4 202 S
2 201 F
2 200 S|||Yes it is, thanks!