ODBC Redux

I asked the following a few days ago. Am I asking the wrong group?
I am attempting to put a couple of Oracle 8i views in a report project I am
building. When I attempt to build the connection I have two drivers to
choose from one is the Microsoft OLE DB provider for Oracle and the other is
"Oracle Provider for OLE DB". When I use the first I do not get all of the
views and when I get the second I get an error saying that the service name
cannot be resolved. I have created a regular DSN and looked at the data in
Access so I know the views I want are there but I cannot seem to make it
happen in Reporting services. Any Ideas?
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor CompanySometimes when you ask a question there isn't anyone who knows the solution.
I don't know the solution but I do have some ideas on what you can try.
First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
will be trying to use OLEDB from the query designer and managed dotnet
provider when you run it. The dotnet provider requires the 9i client (not
sure if a 9i client can go against 8i). If you pick the Microsoft one it
might work from the designer but it would most likely not run when you
preview the report.
A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as the
Oracle OLEDB provider you are missing a step in configuring, i.e. you have
not setup the service name (which is what the error says). I haven't worked
for awhile with Oracle so I can't give you the exact steps. Google OLEDB and
Oracle.
My suggestion is to go with the oledb provider for odbc because then you can
pick the DSN that you setup previously.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Oracle behaves differently when you use the QBE layout and the text layout.
More precisely, two different mechanisms are used to get to the database. I
think you're supposed to use the text layout for entering your query into
the datasource.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
>I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
> am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
> is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
> name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Sorry if I sounded testy. I plead innocent due to the limitations of text.
There are a lot of messages that get posed here and I assumed that mine
either got lost in the mass or it was considered off topic hence the first
sentence of the message.
Now, that being said I am running the Oracle 9i client and when I try to use
the Microsoft OLE DB driver for ODBC I get the same error that the service
name cannot be resolved. I can connect any other tool using the ODBC DSN
except for the MS tool.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Sometimes when you ask a question there isn't anyone who knows the
solution.
> I don't know the solution but I do have some ideas on what you can try.
> First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
> will be trying to use OLEDB from the query designer and managed dotnet
> provider when you run it. The dotnet provider requires the 9i client (not
> sure if a 9i client can go against 8i). If you pick the Microsoft one it
> might work from the designer but it would most likely not run when you
> preview the report.
> A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
the
> Oracle OLEDB provider you are missing a step in configuring, i.e. you have
> not setup the service name (which is what the error says). I haven't
worked
> for awhile with Oracle so I can't give you the exact steps. Google OLEDB
and
> Oracle.
> My suggestion is to go with the oledb provider for odbc because then you
can
> pick the DSN that you setup previously.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||Hmm, there should not be any difference between Access (which you says works
with the DSN you have created) and using MS OLEDB Driver for ODBC. Is this a
system DSN or a user DSN. I used ODBC against Sybase and I see no
difference between using the DSN with Access (which I have done) and using
it in RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23rFT7wX%23EHA.2580@.TK2MSFTNGP15.phx.gbl...
> Sorry if I sounded testy. I plead innocent due to the limitations of text.
> There are a lot of messages that get posed here and I assumed that mine
> either got lost in the mass or it was considered off topic hence the first
> sentence of the message.
> Now, that being said I am running the Oracle 9i client and when I try to
use
> the Microsoft OLE DB driver for ODBC I get the same error that the service
> name cannot be resolved. I can connect any other tool using the ODBC DSN
> except for the MS tool.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Sometimes when you ask a question there isn't anyone who knows the
> solution.
> > I don't know the solution but I do have some ideas on what you can try.
> > First, do not pick the Microsoft OLE DB Provider for Oracle. If you do
it
> > will be trying to use OLEDB from the query designer and managed dotnet
> > provider when you run it. The dotnet provider requires the 9i client
(not
> > sure if a 9i client can go against 8i). If you pick the Microsoft one it
> > might work from the designer but it would most likely not run when you
> > preview the report.
> >
> > A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
> the
> > Oracle OLEDB provider you are missing a step in configuring, i.e. you
have
> > not setup the service name (which is what the error says). I haven't
> worked
> > for awhile with Oracle so I can't give you the exact steps. Google OLEDB
> and
> > Oracle.
> >
> > My suggestion is to go with the oledb provider for odbc because then you
> can
> > pick the DSN that you setup previously.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||Ok this suggestion worked (in a kluge kind of way) even though I could not
see the view in the picker list if I entered the name manually it then
appeared. Thanks for the help.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Oracle behaves differently when you use the QBE layout and the text
layout.
> More precisely, two different mechanisms are used to get to the database.
I
> think you're supposed to use the text layout for entering your query into
> the datasource.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> >I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> > am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> > is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> > name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||So did you end up using Oracle OLEDB provider?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Ok this suggestion worked (in a kluge kind of way) even though I could not
> see the view in the picker list if I entered the name manually it then
> appeared. Thanks for the help.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Oracle behaves differently when you use the QBE layout and the text
> layout.
> > More precisely, two different mechanisms are used to get to the
database.
> I
> > think you're supposed to use the text layout for entering your query
into
> > the datasource.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > >I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||What I did:
I had created a DSN Called EMC. For the Data Source I used Microsoft OLE DB
connector for ODBC and selected the DSN. This gave me a list of tables and
views in the Table Picker but the views I wanted were not in the list so I
had to not use the picker and enter the SQL manually by using the syntax:
userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered that
the table picker then showed that table.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> So did you end up using Oracle OLEDB provider?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > Ok this suggestion worked (in a kluge kind of way) even though I could
not
> > see the view in the picker list if I entered the name manually it then
> > appeared. Thanks for the help.
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > Oracle behaves differently when you use the QBE layout and the text
> > layout.
> > > More precisely, two different mechanisms are used to get to the
> database.
> > I
> > > think you're supposed to use the text layout for entering your query
> into
> > > the datasource.
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > >I asked the following a few days ago. Am I asking the wrong group?
> > > >
> > > > I am attempting to put a couple of Oracle 8i views in a report
project
> I
> > > > am
> > > > building. When I attempt to build the connection I have two drivers
to
> > > > choose from one is the Microsoft OLE DB provider for Oracle and the
> > other
> > > > is
> > > > "Oracle Provider for OLE DB". When I use the first I do not get all
of
> > the
> > > > views and when I get the second I get an error saying that the
service
> > > > name
> > > > cannot be resolved. I have created a regular DSN and looked at the
> data
> > in
> > > > Access so I know the views I want are there but I cannot seem to
make
> it
> > > > happen in Reporting services. Any Ideas?
> > > >
> > > >
> > > > --
> > > > Andrew C. Madsen
> > > > Network Specialist
> > > > Harley-Davidson Motor Company
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I wondered what ended up working for you in case I see someone else
with the problem. I wonder if the views have some special naming to them
that causes them to not show in the list.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:e2wfiXZ%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> What I did:
> I had created a DSN Called EMC. For the Data Source I used Microsoft OLE
DB
> connector for ODBC and selected the DSN. This gave me a list of tables and
> views in the Table Picker but the views I wanted were not in the list so I
> had to not use the picker and enter the SQL manually by using the syntax:
> userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered
that
> the table picker then showed that table.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> > So did you end up using Oracle OLEDB provider?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > > Ok this suggestion worked (in a kluge kind of way) even though I could
> not
> > > see the view in the picker list if I entered the name manually it then
> > > appeared. Thanks for the help.
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > > Oracle behaves differently when you use the QBE layout and the text
> > > layout.
> > > > More precisely, two different mechanisms are used to get to the
> > database.
> > > I
> > > > think you're supposed to use the text layout for entering your query
> > into
> > > > the datasource.
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > > >I asked the following a few days ago. Am I asking the wrong group?
> > > > >
> > > > > I am attempting to put a couple of Oracle 8i views in a report
> project
> > I
> > > > > am
> > > > > building. When I attempt to build the connection I have two
drivers
> to
> > > > > choose from one is the Microsoft OLE DB provider for Oracle and
the
> > > other
> > > > > is
> > > > > "Oracle Provider for OLE DB". When I use the first I do not get
all
> of
> > > the
> > > > > views and when I get the second I get an error saying that the
> service
> > > > > name
> > > > > cannot be resolved. I have created a regular DSN and looked at the
> > data
> > > in
> > > > > Access so I know the views I want are there but I cannot seem to
> make
> > it
> > > > > happen in Reporting services. Any Ideas?
> > > > >
> > > > >
> > > > > --
> > > > > Andrew C. Madsen
> > > > > Network Specialist
> > > > > Harley-Davidson Motor Company
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>