OK to use one stored procedure for inserts and updates?

I have seen a couple examples where the person used ONE stored procedure (call it SaveOrder) was used for both Inserts and Updates. This would be opposed to having a InsertOrder and UpdateOrder. I'm assuming there was some branching code in there to determine whether the record already existed or not.

My question is this: Is this a good practice? It seems like it might save some duplicate coding.

Just looking for some opinions-

Thanks,

Jim

its ok....

if exist...update

else insert

|||Hi,

I would alway prefer a more granular solution rather than a monolithic approach. Otherwise procedure could get (depending on your code) hard to read and debug.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thank you both-

Jim

sql

Odd scheduled job behavior

I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc....haven't seen that one, but you could always check the sysjobhistory table in msdb (if you have the rights) to see what the rundate column has stored.

What version of SQL Server are you on?

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx
"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred
Try updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||ITFred wrote:
> *
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment that this is a behavioral issue with
> SQL2K that needs to be addressed.
> - ITFred *
I've been running into the exact same issue recently with my database.
My configuration is a MS Access/VBA front end with MSSQL as the
backend, and I see similar behavior when I allow ODBC to pick the
indices to use.
One workaround I've found for this problem is to encapsulate the query
into a stored procedure, then call the stored procedure via ODBC in
pass-through mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems
to resolve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with
queries which are dynamically generated, and it also places queries in
a second location outside of your client code where you might have a
debugging error.
Its not the most elegant solution, and may not work for you at all if
you are constructing the SQL string dynamically in VB, but maybe it can
give you some inspiration on what else you can try (i.e. dynamically
call CREATE and DROP PROCEDURE statements to construct a customized
stored procedure, run it, and kill it when you're done, et. al.).
Hope this helps.
Jim
Jim Tran
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message163387.html
|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.
|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......
|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

ODBC Timeout Linked to Optimizer?

I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFredTry updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||

quote:

---

Originally posted by ITFred

So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred

---

I've been running into the exact same issue recently with my database. My c
onfiguration is a MS Access/VBA front end with MSSQL as the backend, and I s
ee similar behavior when I allow ODBC to pick the indices to use.
One workaround I've found for this problem is to encapsulate the query into
a stored procedure, then call the stored procedure via ODBC in pass-through
mode with something like this:
EXECUTE sp_my_query
This takes ODBC out of the picture as far as indexing goes, and seems to res
olve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with queries w
hich are dynamically generated, and it also places queries in a second locat
ion outside of your client code where you might have a debugging error.
Its not the most elegant solution, and may not work for you at all if you ar
e constructing the SQL string dynamically in VB, but maybe it can give you s
ome inspiration on what else you can try (i.e. dynamically call CREATE and D
ROP PROCEDURE statements to construct a customized stored procedure, run it,
and kill it when you're done, et. al.).
Hope this helps.
Jim|||I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.
hope this helps..
A.|||With respect to the issue brought up by A. Finkler above, that's actually a
separate cause of index failure through ODBC (though still noteworthy).
In my most recent situation, my query had the following construction:
UPDATE [Table A]
SET [Table A].[Column 1] = X
FROM
[Table A] INNER JOIN
([Table B] INNER JOIN
[Table C] ON [Table B].[Column G] = [Table C].[Column H])
ON [Table A].[Column P1] = [Table B].[Column P2] AND
[Table A].[Column Q1] = [Table B].[Column Q2] AND
[Table A].[Column R1] = [Table B].[Column R2] AND
[Table A].[Column S1] = [Table C].[Column S2]
Running this query through the Query Analyzer always produced an appropriate
indexing scheme that resulted in fast performance, but launching the exact
same query string through ODBC would randomly cause a table scan.
Jim|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.
Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.
The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!
Still very frustrating......|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> I ran into the same problem recently, and the reason was the way the SQL
> queries were written.
> In order to have the optimzier process a query correctly, the where-clause
> has to follow a few rules:
> 1. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant use
> indexes and has to perform a table scan. This works as long as a table is
> small enough, but once it has too many rows you run into timeouts.
> hope this helps..
> A.
>
Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices -- makes for some serious code maintenance
issues.
Nevertheless I will continue to explore this... thank you!

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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>