Order by with select into

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didnt see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <anonymous@.devdex.com> wrote in message
news:3f0b0ac2$0$202$75868355@.news.frii.net...
> I have a Microsoft SQL Server 7.0.
> I wrote a sql command that creates a temporary table with a ORDER BY
> clause.
> When a execute a SELECT on this temporary table sometimes the result is
> ok, but sometimes is not ordered. I didnt see anything like that. Any
> clue?
> Is there any kind of limits with temporary tables ? Because the command
> that creates the temporary table is working and the rsults is always
> ordered. But when I create a table with it, sometimes the table is not
> ordered.
> Paulo

Rows in tables (temporary or permanent) never have an order, even if you
used ORDER BY when you did the INSERT, and even if there's a clustered index
on the table. The only way to be sure you get ordered data is to use ORDER
BY when you SELECT it.

Some tables, especially with clustered indexes, may look like the data is
ordered, but you can't assume it will always work. You could think of it
like this - when you SELECT from the table, you create a result set, and
ORDER BY only works on the result set, not on the table.

Is there some specific reason that you want to order data in the table,
instead of using ORDER BY in your queries? If there is, then maybe you could
give some more details about what you are trying to do, and someone might be
able to suggest a different solution.

Simon|||Paulo,

Tables do not have an order. In other words, tables by definition are
logically an unordered set of rows. Using ORDER BY in a INSERT...SELECT or
SELECT...INTO does not mean that the data in the table is 'ordered'. The
order of rows which you see when you do a SELECT without an ORDER BY clause
is a undefined/arbitrary order chosen by the optimizer based on the physical
characteristics, indexes, access paths, complexity of joins if any, other
statistical information etc. & many undocumented factors. Hence you should
not rely on the 'order' of rows in a table which you see on the screen.

To repeat, rows in a table do not have a logical order. The only guaranteed
way of retrieving rows in a specific order is to use an ORDER BY clause in
your SELECT statement.

--
- Anith
( Please reply to newsgroups only )sql

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde84
1.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:

> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be th
e
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde
841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
>|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:
SELECT * FROM TopView
ORDER BY LastName
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
Regards
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
> Cheers, Peter
> "John Bell" wrote:
>|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDE
R
> BY in a view definition"
> John
> "Peter Jones" wrote:
>|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the co
de
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
>|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905
-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...[vbcol=seagreen]
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:
> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
> > Hi,
> >
> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > which was converted to: "Select Top (100) Percent..." when I recreated my
> > views.
> >
> > However any ORDER BY's in my views are not executed until I remove the
> > keyword Percent, e.g. Select Top (100)... works fine,
> >
> > Is this a known difference between 2000 and 2005. If so is there a list of
> > these differences?
> >
> > Peter
> >|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:
SELECT * FROM TopView
ORDER BY LastName
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us :) So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Regards :)
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> Have you checked the backward compatibility sections in books online?
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> John
>>
>> "Peter Jones" wrote:
>> > Hi,
>> >
>> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as
>> > it
>> > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > Percent...."
>> > which was converted to: "Select Top (100) Percent..." when I recreated
>> > my
>> > views.
>> >
>> > However any ORDER BY's in my views are not executed until I remove the
>> > keyword Percent, e.g. Select Top (100)... works fine,
>> >
>> > Is this a known difference between 2000 and 2005. If so is there a list
>> > of
>> > these differences?
>> >
>> > Peter
>> >|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > Have you checked the backward compatibility sections in books online?
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> >
> > John
> >
> >
> > "Peter Jones" wrote:
> >
> > > Hi,
> > >
> > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > views.
> > >
> > > However any ORDER BY's in my views are not executed until I remove the
> > > keyword Percent, e.g. Select Top (100)... works fine,
> > >
> > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > these differences?
> > >
> > > Peter
> > >|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> BY in a view definition"
> John
> "Peter Jones" wrote:
> > Hello John,
> >
> > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > Have you checked the backward compatibility sections in books online?
> > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > >
> > > John
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hi,
> > > >
> > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > views.
> > > >
> > > > However any ORDER BY's in my views are not executed until I remove the
> > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > >
> > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > these differences?
> > > >
> > > > Peter
> > > >|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > The issue David and Bobby refer to is documented at "Breaking Changes to
> > Database Engine Features in SQL Server 2005"
> > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > BY in a view definition"
> >
> > John
> >
> > "Peter Jones" wrote:
> >
> > > Hello John,
> > >
> > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > Have you checked the backward compatibility sections in books online?
> > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > >
> > > > John
> > > >
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > views.
> > > > >
> > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > >
> > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > these differences?
> > > > >
> > > > > Peter
> > > > >|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the code
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
> >
> > Gentlemen,
> >
> > Thanks for your input. All is clear although it is perculiar that removing
> > the keyword PERCENT made things work.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > Database Engine Features in SQL Server 2005"
> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > BY in a view definition"
> > >
> > > John
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hello John,
> > > >
> > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > Have you checked the backward compatibility sections in books online?
> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > views.
> > > > > >
> > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > >
> > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > these differences?
> > > > > >
> > > > > > Peter
> > > > > >|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> I am not sure why it put that in for you, I prefer to recompile all the
>> code
>> (stored procedure/functions/views) from source when doing an upgrade
>> which
>> have avoided this. You may want to consider using a version control
>> system
>> and using that as the source of the code.
>> John
>>
>> "Peter Jones" wrote:
>> >
>> > Gentlemen,
>> >
>> > Thanks for your input. All is clear although it is perculiar that
>> > removing
>> > the keyword PERCENT made things work.
>> >
>> > Cheers, Peter
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi Peter
>> > >
>> > > The issue David and Bobby refer to is documented at "Breaking Changes
>> > > to
>> > > Database Engine Features in SQL Server 2005"
>> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under
>> > > "ORDER
>> > > BY in a view definition"
>> > >
>> > > John
>> > >
>> > > "Peter Jones" wrote:
>> > >
>> > > > Hello John,
>> > > >
>> > > > Thanks - I hadn't done that but now that I have I haven't found
>> > > > anything. I
>> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem
>> > > > to be the
>> > > > same from 2000 to 2005. There is certainly nothing in the 2005
>> > > > documentation
>> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY
>> > > > clause.
>> > > >
>> > > > Cheers, Peter
>> > > >
>> > > > "John Bell" wrote:
>> > > >
>> > > > > Hi Peter
>> > > > >
>> > > > > Have you checked the backward compatibility sections in books
>> > > > > online?
>> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> > > > > or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> > > > >
>> > > > > John
>> > > > >
>> > > > >
>> > > > > "Peter Jones" wrote:
>> > > > >
>> > > > > > Hi,
>> > > > > >
>> > > > > > I've noticed that ORDER BY in views does not work in SQL Server
>> > > > > > 2005 as it
>> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > > > > > Percent...."
>> > > > > > which was converted to: "Select Top (100) Percent..." when I
>> > > > > > recreated my
>> > > > > > views.
>> > > > > >
>> > > > > > However any ORDER BY's in my views are not executed until I
>> > > > > > remove the
>> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
>> > > > > >
>> > > > > > Is this a known difference between 2000 and 2005. If so is
>> > > > > > there a list of
>> > > > > > these differences?
>> > > > > >
>> > > > > > Peter
>> > > > > >|||Peter,
When you realize why you can use TOP xxx ORDER BY in a view, you will
understand.
If your view definition does a TOP selection, the ORDER BY will
determine which rows will be part of the result.
If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
my_table ORDER BY my_primary_key_column" and you select from the view,
the query plan will show the TOP operator. The same is true if you
specify SELECT TOP 100.
If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
will be eliminated from the resultset, regardless of any sorting, so it
does not add a TOP operator to the query plan, nor does it see need to
retrieve the table rows in an ordered fashion (unless you add an ORDER
BY clause to the SELECT that selects from the view). So from an
optimizer perspective it makes perfect sense to disregard the TOP 100
PERCENT .. ORDER BY from the view.
HTH,
Gert-Jan
Peter Jones wrote:
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > I am not sure why it put that in for you, I prefer to recompile all the code
> > (stored procedure/functions/views) from source when doing an upgrade which
> > have avoided this. You may want to consider using a version control system
> > and using that as the source of the code.
> >
> > John
> >
> >
> >
> > "Peter Jones" wrote:
> >
> > >
> > > Gentlemen,
> > >
> > > Thanks for your input. All is clear although it is perculiar that removing
> > > the keyword PERCENT made things work.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > Database Engine Features in SQL Server 2005"
> > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > BY in a view definition"
> > > >
> > > > John
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hello John,
> > > > >
> > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > >
> > > > > Cheers, Peter
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Peter
> > > > > >
> > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > >
> > > > > > John
> > > > > >
> > > > > >
> > > > > > "Peter Jones" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > views.
> > > > > > >
> > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > >
> > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > these differences?
> > > > > > >
> > > > > > > Peter
> > > > > > >|||Hi Gert-Jan,
Thank you for taking the time to add to this thread. I was having trouble
understanding why ORDER BY was even permitted as part of a view - your
comment makes it clear.
Thanks - Peter
"Gert-Jan Strik" wrote:
> Peter,
> When you realize why you can use TOP xxx ORDER BY in a view, you will
> understand.
> If your view definition does a TOP selection, the ORDER BY will
> determine which rows will be part of the result.
> If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
> my_table ORDER BY my_primary_key_column" and you select from the view,
> the query plan will show the TOP operator. The same is true if you
> specify SELECT TOP 100.
> If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
> will be eliminated from the resultset, regardless of any sorting, so it
> does not add a TOP operator to the query plan, nor does it see need to
> retrieve the table rows in an ordered fashion (unless you add an ORDER
> BY clause to the SELECT that selects from the view). So from an
> optimizer perspective it makes perfect sense to disregard the TOP 100
> PERCENT .. ORDER BY from the view.
> HTH,
> Gert-Jan
>
> Peter Jones wrote:
> >
> > Hi John,
> >
> > No - the conversion was fine - my original code had "Top 100 Percent"
> > already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> > would not work without it.
> >
> > My comment related to the fact that when the keyword "Percent" was removed
> > the ORDER BY worked in SQL Server 2005.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > I am not sure why it put that in for you, I prefer to recompile all the code
> > > (stored procedure/functions/views) from source when doing an upgrade which
> > > have avoided this. You may want to consider using a version control system
> > > and using that as the source of the code.
> > >
> > > John
> > >
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > >
> > > > Gentlemen,
> > > >
> > > > Thanks for your input. All is clear although it is perculiar that removing
> > > > the keyword PERCENT made things work.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > > Database Engine Features in SQL Server 2005"
> > > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > > BY in a view definition"
> > > > >
> > > > > John
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hello John,
> > > > > >
> > > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > > >
> > > > > > Cheers, Peter
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Peter
> > > > > > >
> > > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > >
> > > > > > > "Peter Jones" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > > views.
> > > > > > > >
> > > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > > >
> > > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > > these differences?
> > > > > > > >
> > > > > > > > Peter
> > > > > > > >
>sql

Order By

Hi,
I'm trying to understand a particular behaviour of ORDER BY.

Suppose I have 3 tables with a similar column name "samecol".

When I execute something like :

SELECT Tab1.a, Tab2.b, Tab3.c
FROM Tab1, Tab2, Tab3
ORDER BY samecol

Surprisingly, without specifying if it must order by Tab1.samecol or Tab2.samecol for instance, the query returns me a recordset.

Do you know if it chooses a table by default ?
Or it randomly order my datas ?

ThanksOriginally posted by Linirlan
Hi,
I'm trying to understand a particular behaviour of ORDER BY.

Suppose I have 3 tables with a similar column name "samecol".

When I execute something like :

SELECT Tab1.a, Tab2.b, Tab3.c
FROM Tab1, Tab2, Tab3
ORDER BY samecol

Surprisingly, without specifying if it must order by Tab1.samecol or Tab2.samecol for instance, the query returns me a recordset.

Do you know if it chooses a table by default ?
Or it randomly order my datas ?

Thanks
The answer is DBMS-specific. For example, the above is not possible in Oracle:

SQL> select emp.ename, dept.dname
2 from emp, dept
3 order by deptno;
order by deptno
*
ERROR at line 3:
ORA-00918: column ambiguously defined

(Both tables have a column called deptno.)

Oracle query with &

I have a SQL task that I want to execute a query like:

SELECT firstField FROM schema.table WHERE fieldName = 'M&M'

The problem is when I put that in, it won't run.

For SQL*Plus I can stick SET DEFINE OFF before it and it work, not so in Integration Services.

The issue apparently is that &M is treated by Oracle as a variable of some sort.

Any help is appreciated.

Try using char(38) ... SELECT firstField FROM schema.table WHERE fieldName = 'M'||char(38)||'M'|||Thanks. That worked as does 'M' || '&' || 'M'. I'm glad I normally just work with SQL Server |||

Larry Charlton wrote:

Thanks. That worked as does 'M' || '&' || 'M'. I'm glad I normally just work with SQL Server

Do you work at a chocolate manufacturer Larry

http://www.m-ms.com/

-Jamie

Oracle Query

Good day
I am having trouble with getting an Oracle query right for SQL
reporting service.
I wrote a front end to execute the query and it works fine. Now the
next step is to allow users to get a report on any number of clients.
The way I do this for normal SQL Server, is to build SQL with "AND"s
and "OR"s in code, and then send it to reporting services as a
parameter and append it.
This is the text
="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
And the value of my parameter is:
ID = 'ALE' OR ID = 'ALO'
I keep getting errors like "SQL command not properly ended".
Have anybody done something similar? Am I missing something?
Any help will be appreciated.
Kind regards,
KarlKarl,
You need to use a (:) colon instead of the @. sign.
For example,
Select *
From tblYours
Where ID=:ID
Hope this helps,
rwiethron
"karlbasson@.gmail.com" wrote:
> Good day
> I am having trouble with getting an Oracle query right for SQL
> reporting service.
> I wrote a front end to execute the query and it works fine. Now the
> next step is to allow users to get a report on any number of clients.
> The way I do this for normal SQL Server, is to build SQL with "AND"s
> and "OR"s in code, and then send it to reporting services as a
> parameter and append it.
> This is the text
> ="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
> And the value of my parameter is:
> ID = 'ALE' OR ID = 'ALO'
> I keep getting errors like "SQL command not properly ended".
> Have anybody done something similar? Am I missing something?
> Any help will be appreciated.
> Kind regards,
> Karl
>

Oracle Provider and Batch Statements

I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:

Code Snippet

TRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;

I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.

When I run the above statement in SQL Plus or SQL Workbench, it works just fine.

In fact, it works in SQL Plus and SQL Workbench like this as well:

Code Snippet

TRUNCATE TABLE FactTable1

/
TRUNCATE TABLE FactTable2

/
TRUNCATE TABLE FactTable3

/
TRUNCATE TABLE FactTable4

/
TRUNCATE TABLE FactTable5

Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:

...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?

Many thanks,

Rick

Well, I have tried every permutation that I can come up with and it seems that both Oracle providers simply do not support multiple statements in an Execute SQL Task.

If anyone knows otherwise, I would very much appreciate your input.

Rick

|||This is shot in the dark; but have you tried putting the staments in a file; then tell the execute sql task to use the file.|||

I haven't tried this and am happy to give it a shot, but can we talk about where this would be going? Obviously, reading the statements from a text file would not be a production solution but I imagine it would give you some insight as to where the issue might be?

Also, kind of along the same lines, I considered resorting to a ForEach Lookup tasks which certainly is more elegant than creating a bunch of sepereate Execute SQL Tasks but geez, something this fundamental (that works against SQL) shouldn't be this hard.

Many thanks.

Rick

|||Here's another suggestion... have you tried separating your statements with "GO"? GO is how most T-SQL clients parse for batches and although the ExecSQL works with other databases besides SQL Server, it uses the same parser as some of the other T-SQL clients we ship, so GO may work (unless the ExecSQL task is special casing T-SQL).|||My workaround is to create an Oracle stored proc wrappering in the statements in one SQL task, execute it in a second and drop the proc. in a third.

Oracle Provider and Batch Statements

I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:

Code Snippet

TRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;

I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.

When I run the above statement in SQL Plus or SQL Workbench, it works just fine.

In fact, it works in SQL Plus and SQL Workbench like this as well:

Code Snippet

TRUNCATE TABLE FactTable1

/
TRUNCATE TABLE FactTable2

/
TRUNCATE TABLE FactTable3

/
TRUNCATE TABLE FactTable4

/
TRUNCATE TABLE FactTable5

Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:

...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?

Many thanks,

Rick

Well, I have tried every permutation that I can come up with and it seems that both Oracle providers simply do not support multiple statements in an Execute SQL Task.

If anyone knows otherwise, I would very much appreciate your input.

Rick

|||This is shot in the dark; but have you tried putting the staments in a file; then tell the execute sql task to use the file.|||

I haven't tried this and am happy to give it a shot, but can we talk about where this would be going? Obviously, reading the statements from a text file would not be a production solution but I imagine it would give you some insight as to where the issue might be?

Also, kind of along the same lines, I considered resorting to a ForEach Lookup tasks which certainly is more elegant than creating a bunch of sepereate Execute SQL Tasks but geez, something this fundamental (that works against SQL) shouldn't be this hard.

Many thanks.

Rick

|||Here's another suggestion... have you tried separating your statements with "GO"? GO is how most T-SQL clients parse for batches and although the ExecSQL works with other databases besides SQL Server, it uses the same parser as some of the other T-SQL clients we ship, so GO may work (unless the ExecSQL task is special casing T-SQL).|||My workaround is to create an Oracle stored proc wrappering in the statements in one SQL task, execute it in a second and drop the proc. in a third.

Oracle link performance is horible

I have a simple select statement that I execute against an Oracle database i
n
PL/SQL and the results are, for the purposes of this discuaaion,
instantaneous.
I have that Database linked to my SQL server. If I perform that same select
statement to the oracle server:
SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
it takes 1 minute 14 seconds.
What's up with that? Any ideas, folks?select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> I have a simple select statement that I execute against an Oracle database
in
> PL/SQL and the results are, for the purposes of this discuaaion,
> instantaneous.
> I have that Database linked to my SQL server. If I perform that same
select
> statement to the oracle server:
> SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
> it takes 1 minute 14 seconds.
> What's up with that? Any ideas, folks?|||That helped a lot. Thanks.
However. I can't use a parameterized query...can I?
DECLARE @.someValue varchar(30)
select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
x='+@.someValue)
"CK" wrote:

> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
> "David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
> news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> in
> select
>
>|||You can do it, but you'd need to make the statement dynamic sql. I don't
have an oracle connection to test it. I've done this before and you really
have to play with the quoting to get it right...but it will work.
Something like this:
DECLARE @.someValue varchar(30)
declare @.cmd varchar(1000)
set @.cmd = 'select X,Y,Z FROM openquery(oraserver,'''select X,Y,Z from
oraTABLE WHERE x='''+@.someValue)''
exec (@.cmd)
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:13E1AA87-0199-4CFA-BCA5-DB09D0945321@.microsoft.com...
> That helped a lot. Thanks.
> However. I can't use a parameterized query...can I?
> DECLARE @.someValue varchar(30)
> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
> x='+@.someValue)
>
>
> "CK" wrote:
>
database

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i linked server

Hi, I'm using the Oracle Ole db provider and receive the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute returned 0x80040155].
Any ideas?
TiAFor all SQL statements? Or a specific query?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pacific" <anonymous@.discussions.microsoft.com> wrote in message
news:F1B5E60E-BE89-448E-8FCB-01C9C0C70FCB@.microsoft.com...
> Hi, I'm using the Oracle Ole db provider and receive the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute returned 0x80040155].
> Any ideas?
> TiA|||for all statements

Oracle 9i linked server

Hi, I'm using the Oracle Ole db provider and receive the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].
Any ideas?
TiA
For all SQL statements? Or a specific query?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pacific" <anonymous@.discussions.microsoft.com> wrote in message
news:F1B5E60E-BE89-448E-8FCB-01C9C0C70FCB@.microsoft.com...
> Hi, I'm using the Oracle Ole db provider and receive the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute returned 0x80040155].
> Any ideas?
> TiA
|||for all statements