Order By Start_Date

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Quote:

Originally Posted by ljbuxton

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Please post the DDL scripts and query you are running, otherwise it is hard to guess what issues do you have.
start_date field may be even of type varchar with 'DD-MM-YY' format

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...

quote:

---

> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

---

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
--
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

order by killing me

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Ive got a Clustered Primary Key on
the column that Im ordering by. Any ideas?
TIA, Chris"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
What does the output from Query Analyzer > Show Plan indicate? How many rows
do you have?
Steve|||Please post DDL including index and with Execution plan and Statistics IO.
--
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hi,
Do the select statement with out Order by clause,Since you have primary key
on that field , automatically data will be arranged in that order. In this
case you do not require a order by clause.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hari,
For your information: if you want the rows of the resultset in a
particular order, you must specify an ORDER BY clause. Without it,
SQL-Server is at liberty to return the rows in any order it likes. At
one occasion this might accidentally be the order you desired, but at
another occasions a different order might result.
Gert-Jan
Hari Prasad wrote:
> Hi,
> Do the select statement with out Order by clause,Since you have primary key
> on that field , automatically data will be arranged in that order. In this
> case you do not require a order by clause.
> Thanks
> Hari
> MCDBA
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> > sql2k sp2
> >
> > Im aware that it normally slows everone down. But Ive got
> > a view, and when I select top 1 from it without an order
> > by it returns in 1 second. With order by takes over 4
> > minutes. Quite a jump. Ive got a Clustered Primary Key on
> > the column that Im ordering by. Any ideas?
> >
> > TIA, Chris
> >

Order By clause problem

Hello,

Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8...98...0,0,0

My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?

Thanks, Curt.

Do you have just one 0 record or more than 1?

|||

Use Case in your Order By clause:

OrderbyCASEWhen column_name=0then 201else column_nameEND

|||

i think you'll have to use union something like

select PID, Name from tt2 where PID%10 = 0
union
select PID, Name from tt2 where PID%10 <>0

thanks,

satish.

|||

Thanks limno, that works perfect.

sql

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David
|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen
|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen
|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen
|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen
|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen
|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>

Oracle linked server : won't run if driver AllowInProc is off

I've tested a link server from msde to oracle on a win2k server test machine
and after some effort, it worked properly. I noted down the steps i took to
get it to work. I could enable or disable AllowInProc for the Microsoft OLE
DB Oracle driver i was using to interface with Oracle. This machine is in a
test domain.
Now i wanted to configure the same thing on a server in our real domain.
That server is a true SQL server and is running multiple instances. The
thing is, i can't seem to get it to work if i disable the driver's
AllowInProc. When i disable AllowInProc i get this error mesage when trying
to see the tables of the linked server using Enterprise Manager:
Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
returned 0x80070005: Access denier.].
I tried reinstalling the latest MDAC (2.8) and rebooting the server
afterwards but it didn't change anything. I checked the Microsoft article
280106 and tried
If anybody has any ideas on this i'd be happy to read about them since i am
kind of running out of ideas right now...
After much screaming and crying i found the potential cause. It seems the
driver doesn't receive the domain login hence returning "Access Denied". We
made a regular sql login and when used, everything worked perfectly. Any
idea if there are some services related to the driver or the oracle stuff
that might need to run under a different user account ?
Gonna try using odbc instead...
I'm posting all this in the hope that someone might help me or that it might
help someone.
"/dev/null" wrote:

> I've tested a link server from msde to oracle on a win2k server test machine
> and after some effort, it worked properly. I noted down the steps i took to
> get it to work. I could enable or disable AllowInProc for the Microsoft OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i am
> kind of running out of ideas right now...
|||Why don't you use oracle driver instead? You can install ORACLE client on
the SQL box and you should be able to get it working with no issues.
HTH
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:9B5EF51E-2CBA-4B2F-8841-0A400396288A@.microsoft.com...
> I've tested a link server from msde to oracle on a win2k server test
machine
> and after some effort, it worked properly. I noted down the steps i took
to
> get it to work. I could enable or disable AllowInProc for the Microsoft
OLE
> DB Oracle driver i was using to interface with Oracle. This machine is in
a
> test domain.
> Now i wanted to configure the same thing on a server in our real domain.
> That server is a true SQL server and is running multiple instances. The
> thing is, i can't seem to get it to work if i disable the driver's
> AllowInProc. When i disable AllowInProc i get this error mesage when
trying
> to see the tables of the linked server using Enterprise Manager:
> Error 7399: OLE DB provider 'MSDAORA' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IUnknown::QueryInterface
> returned 0x80070005: Access denier.].
> I tried reinstalling the latest MDAC (2.8) and rebooting the server
> afterwards but it didn't change anything. I checked the Microsoft article
> 280106 and tried
> If anybody has any ideas on this i'd be happy to read about them since i
am
> kind of running out of ideas right now...
|||"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Why don't you use oracle driver instead? You can install ORACLE client on
> the SQL box and you should be able to get it working with no issues.
>
The Oracle Client is required for MSDAORA as well, so it is already
installed on the box. I assume that the OP is uncomfortable running the
Oralce OleDb drivers and the Oracle Client inside the SqlServer address
space for fear it could crash Sql Server.
And quite honestly, I would not allow ad-hoc queries to an Oracle linked
server with the Oracle Client in-process in a production Sql Server. With
canned queries and testing, ok, but it's still a concern.
David
|||We don't want to run the driver in process for the reason you thought: sql
server crashes = we better run damn fast for our lives.
It's not that i didn't try the driver provided by oracle but i never got it
to work. It seemed even more trouble than the microsoft driver. Even then i
suspect we'd have to be running it in-process.
"David Browne" wrote:

> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:%23sqDSm3tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> The Oracle Client is required for MSDAORA as well, so it is already
> installed on the box. I assume that the OP is uncomfortable running the
> Oralce OleDb drivers and the Oracle Client inside the SqlServer address
> space for fear it could crash Sql Server.
> And quite honestly, I would not allow ad-hoc queries to an Oracle linked
> server with the Oracle Client in-process in a production Sql Server. With
> canned queries and testing, ok, but it's still a concern.
> David
>
>
|||I just tested using an ODBC data source using the Microsoft ole db for Oracle
driver and it's the same issue. Is it because we set up SQL server to run
under a specific user account (not localsystem or whatever the default is) ?
the linked server was set up to use an odbc source, a source which used the
same driver as before...
and the fun won't stop!
"/dev/null" wrote:
[vbcol=seagreen]
> We don't want to run the driver in process for the reason you thought: sql
> server crashes = we better run damn fast for our lives.
> It's not that i didn't try the driver provided by oracle but i never got it
> to work. It seemed even more trouble than the microsoft driver. Even then i
> suspect we'd have to be running it in-process.
> "David Browne" wrote:
|||I think you hit the problem. I was having the same issues until I decided to
test it under the local system account instead of the domain account my SQL
Server was originally set.
Under the local system account, I had no problems setting a linked Oracle
server, in proc or out of proc, but when SQL Server is running under the
domain account, the connection doesn't work.
I've try to place the domain account in my local adminstrator group, but
that doesn't work either.
I've left my SQL Server running under my local system account for now, if
someone knows a fix for this issue, please post it.
H.Rosental
"/dev/null" wrote:
[vbcol=seagreen]
> I just tested using an ODBC data source using the Microsoft ole db for Oracle
> driver and it's the same issue. Is it because we set up SQL server to run
> under a specific user account (not localsystem or whatever the default is) ?
> the linked server was set up to use an odbc source, a source which used the
> same driver as before...
> and the fun won't stop!
> "/dev/null" wrote:

Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron Lowe
In some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking components
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji
When YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas

"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle 9i OLE DB data source connection

help - I’ve been stuck on this for days.

My software vendor limits the sessions to one (1) per log-in to Oracle 9i and I’m having tons of problems trying to extract data through an SSIS package. Everytime I run the package SSIS already logged in and the OLE DB source fails because of the sessions exceeded error.

Is there any way to limit the connection to just when I need the data to pump across on the OLE DB source?

Thank you very much for any help.

Regads,

Nestor

Moving to the "SQL Server Integration Services" forum.