order by in a view in sqlserver2005

Hi,

I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

This is the view :

CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !

Has anyone have a solution for it ?

Thanks

Hi I have found out that the percent directive gives this problem.
I guess it is a bad solution but replacing this with a ridiculus high number solves the problem say

Alter VIEW hsarti01_VD1 AS
SELECT TOP 10000000000 *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

solves this. I guess however that ordering in views is not a realy a good thing, emagine you select on the view with an order statment, wais a few cpu cycles

Walter

|||

Order of rows in a result is guaranteed only if you specify an ORDER BY clause in the outer-most SELECT statement. Anywhere else the optimizer is free to remove it or preserve order only within that scope. So you will be relying on a particular plan behavior and the expected output will change if the plan changes. This can happen between service packs or releases or hotfixes. It is hard to tell. The following warning has been added to the CREATE VIEW topic in SQL Server 2005 to reflect the correct behavior:

The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

So modify the SELECT that queries the view to include appropriate ORDER BY clause. This is the only sure way. Please see the link below for additional information on ordering guarantees in SQL Server:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

I think this is a horrible *BUG*. I think if an Order By is specified in the view, than the results should ALWAYS be returned in that order unless an outer query is used to resort the view (just as you can use Where to further filter results). Otherwise you constantly have to re-specify the order of the view everywhere it is used. This greatly reduces the value of using the view. With SQL 2000, I could simply open a view in my application and navigate through it, confident that the records were in the correct order. Now with 2005 I have to RESPECIFY the order by statement EVERYWHERE the view is used. This has introduced a number of logical errors in my application, and I think it was a horrible oversight and bug. You can try to call it a "feature" but that's garbage.

|||

Brent, I understand how this behavior is inconvenient, but it is consistent with ANSI/ISO standards, and it is consistent with Microsoft SQL Server documentation, which has recently been made clearer about this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm 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. In my opinion, the confusion is caused by the syntax Microsoft chose for its proprietary TOP clause. Instead of using ORDER BY, which already has a meaning in standard SQL, to specify the ordering used by TOP (thereby adding a second meaning to ORDER BY), the syntax should have been something like CREATE VIEW V AS SELECT TOP (10) OVER (ORDER BY someColumn) * FROM T Then an ORDER BY clause (at the end of the query statement) could have been prohibited completely from view definitions, and there would be no question about this. But unfortunately, that's not the way T-SQL is, and there is a lot of misunderstanding. In any case, the only way to guarantee a result set's ordering is to specify ORDER BY in the query that returns the result set. The view definition is not the query that returns the result set, so ORDER BY in the view definition for V does not control the order of the result set generated by SELECT * FROM V. The workaround you suggest may seem to solve the problem, but you should be aware that it is not guaranteed to work. Unless you want to risk going through this trouble again in the future (perhaps after a service pack that introduces new optimizer enhancements), you should specify an ORDER BY clause every time you want an ordered result set. You are not "re"specifying the ORDER BY clause, since the one in the view definition is not a specification for the order of the results - it is a specification (that works with TOP) of what rows the view contains, regardless of the order in which you might want to retrieve them. Steve Kass Drew University Brent Mullet@.discussions.microsoft.com wrote:
> I think this is a horrible *BUG*. I think if an Order By is specified
> in the view, than the results should ALWAYS be returned in that order
> unless an outer query is used to resort the view (just as you can use
> Where to further filter results). Otherwise you constantly have to
> re-specify the order of the view everywhere it is used. This greatly
> reduces the value of using the view. With SQL 2000, I could simply open
> a view in my application and navigate through it, confident that the
> records were in the correct order. Now with 2005 I have to RESPECIFY
> the order by statement EVERYWHERE the view is used. This has introduced
> a number of logical errors in my application, and I think it was a
> horrible oversight and bug. You can try to call it a "feature" but
> that's garbage.
>
>
>
>
>
>

|||

I still think it's stupid and makes no sense. You specify SELECT, WHERE, and GROUP BY statements in views and those are all respected but ORDER BY is not. There is no reason for this. If I want ordered results, I should be able to specify it in a view and be confident that wherever the view is used the Order By is respected. This is a basic programming principle. If the same view is used in many places in an application, and for some reason the order needs to change, I should be able to do that globally just as I can with WHERE. This is a BUG that needs FIXED.

If it's specified that way in the ANSI/ISO standards, than those standards need fixed. This is ludicrous.

|||

A prime problem is that what happens when you joint this "ordered view" with another "ordered view"? Who comes first?

A view is a table, which is by definition, unordered. The SELECT, WHERE, and GROUP BY clauses shape the data in the table, but the order is not a part of a table. To change that you would have to change the root of the theory that relational databases have been built upon for years. Not to mention the definition of a SET would need to be changed and all of the optimizers of database servers rethought, since this table's order could affect the users of the table.

If you want the data to be consistently returned in an order via code, it is best use a stored procedure.

|||

Ahah! Now at least I think it makes a little more sense. I did not think about using stored procedues; I've been using views for a long time, and that worked fine in 2000. So part of the issue was my ignorance (blush). Converting to stored procedures is a perfectly acceptable solution. (And there are probably other benefits to that as well?)

So it's no longer a STUPID BUG it's a DANGEROUS TRAP that IGNORANT USERS can fall into when they UPGRADE

If the ORDER BY is not respected in it's intuitive sense, I think it should not be specifyable unless it is somehow tied directly to the TOP statement.

Thanks for the response Louis.

|||

This is currently the case. Try to build a view with an order by any you get a nasty message:

create view test
as
select *
from sysobjects
order by 1

In 2000:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

In 2005:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I know how you feel though. I had used this in my views in past versions, (as well as the fact that clustered data was naturally returned in clustered order) and for the most part it still holds true, especially in testing, because it is usually easier for to return the data in order because if the query processor needed it in order to determine the TOP rows, it is unlikely to be faster to reorder. But as the query optimizer/processor gets more and more sophisicated, the more likely it is that they will find ways to maximize output and lose the ordering in the process.

In all cases it is better to either use ORDER BY, or just let the client sort the data.

|||Yes but creating a new view in Enterprise Manager defaults to "TOP (100) PERCENT" - and when this is present, the Order By is accepted. In this case, the Order By is meaningless and dangerously misleading.|||

Now you've nailed it. This behavior, which unfortunately persists in SQL Server 2005 Enterprise Manager's Query and View Designer, is idiotic. A good place to add your voice to the chorus is at the Microsoft Product Feeback center, where you can vote on the at least three bugs/suggestions about this behavior (URL may wrap). http://lab.msdn.microsoft.com/productfeedback/SearchResults.aspx?text=view+%26quot%3border+by%26quot%3b+percent&stype=1&fields=1&type=0&witId=0&pId=0&category=0&os=0&oslang=0&status=0&msstatus=0&resolution=0&chgdays=&validation=0&votes=&voterating=0&workarounds=False&attachments=False SK Brent Mullet@.discussions.microsoft.com wrote:
> Yes but creating a new view in Enterprise Manager defaults to "TOP (100)
> PERCENT" - and when this is present, the Order By is accepted. In this
> case, the Order By is meaningless and dangerously misleading.
>

order by in a view in sqlserver2005

Hi,

I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

This is the view :

CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !

Has anyone have a solution for it ?

Thanks

Hi I have found out that the percent directive gives this problem.
I guess it is a bad solution but replacing this with a ridiculus high number solves the problem say

Alter VIEW hsarti01_VD1 AS
SELECT TOP 10000000000 *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4

solves this. I guess however that ordering in views is not a realy a good thing, emagine you select on the view with an order statment, wais a few cpu cycles

Walter

|||

Order of rows in a result is guaranteed only if you specify an ORDER BY clause in the outer-most SELECT statement. Anywhere else the optimizer is free to remove it or preserve order only within that scope. So you will be relying on a particular plan behavior and the expected output will change if the plan changes. This can happen between service packs or releases or hotfixes. It is hard to tell. The following warning has been added to the CREATE VIEW topic in SQL Server 2005 to reflect the correct behavior:

The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

So modify the SELECT that queries the view to include appropriate ORDER BY clause. This is the only sure way. Please see the link below for additional information on ordering guarantees in SQL Server:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

|||

I think this is a horrible *BUG*. I think if an Order By is specified in the view, than the results should ALWAYS be returned in that order unless an outer query is used to resort the view (just as you can use Where to further filter results). Otherwise you constantly have to re-specify the order of the view everywhere it is used. This greatly reduces the value of using the view. With SQL 2000, I could simply open a view in my application and navigate through it, confident that the records were in the correct order. Now with 2005 I have to RESPECIFY the order by statement EVERYWHERE the view is used. This has introduced a number of logical errors in my application, and I think it was a horrible oversight and bug. You can try to call it a "feature" but that's garbage.

|||

Brent, I understand how this behavior is inconvenient, but it is consistent with ANSI/ISO standards, and it is consistent with Microsoft SQL Server documentation, which has recently been made clearer about this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm 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. In my opinion, the confusion is caused by the syntax Microsoft chose for its proprietary TOP clause. Instead of using ORDER BY, which already has a meaning in standard SQL, to specify the ordering used by TOP (thereby adding a second meaning to ORDER BY), the syntax should have been something like CREATE VIEW V AS SELECT TOP (10) OVER (ORDER BY someColumn) * FROM T Then an ORDER BY clause (at the end of the query statement) could have been prohibited completely from view definitions, and there would be no question about this. But unfortunately, that's not the way T-SQL is, and there is a lot of misunderstanding. In any case, the only way to guarantee a result set's ordering is to specify ORDER BY in the query that returns the result set. The view definition is not the query that returns the result set, so ORDER BY in the view definition for V does not control the order of the result set generated by SELECT * FROM V. The workaround you suggest may seem to solve the problem, but you should be aware that it is not guaranteed to work. Unless you want to risk going through this trouble again in the future (perhaps after a service pack that introduces new optimizer enhancements), you should specify an ORDER BY clause every time you want an ordered result set. You are not "re"specifying the ORDER BY clause, since the one in the view definition is not a specification for the order of the results - it is a specification (that works with TOP) of what rows the view contains, regardless of the order in which you might want to retrieve them. Steve Kass Drew University Brent Mullet@.discussions.microsoft.com wrote:
> I think this is a horrible *BUG*. I think if an Order By is specified
> in the view, than the results should ALWAYS be returned in that order
> unless an outer query is used to resort the view (just as you can use
> Where to further filter results). Otherwise you constantly have to
> re-specify the order of the view everywhere it is used. This greatly
> reduces the value of using the view. With SQL 2000, I could simply open
> a view in my application and navigate through it, confident that the
> records were in the correct order. Now with 2005 I have to RESPECIFY
> the order by statement EVERYWHERE the view is used. This has introduced
> a number of logical errors in my application, and I think it was a
> horrible oversight and bug. You can try to call it a "feature" but
> that's garbage.
>
>
>
>
>
>

|||

I still think it's stupid and makes no sense. You specify SELECT, WHERE, and GROUP BY statements in views and those are all respected but ORDER BY is not. There is no reason for this. If I want ordered results, I should be able to specify it in a view and be confident that wherever the view is used the Order By is respected. This is a basic programming principle. If the same view is used in many places in an application, and for some reason the order needs to change, I should be able to do that globally just as I can with WHERE. This is a BUG that needs FIXED.

If it's specified that way in the ANSI/ISO standards, than those standards need fixed. This is ludicrous.

|||

A prime problem is that what happens when you joint this "ordered view" with another "ordered view"? Who comes first?

A view is a table, which is by definition, unordered. The SELECT, WHERE, and GROUP BY clauses shape the data in the table, but the order is not a part of a table. To change that you would have to change the root of the theory that relational databases have been built upon for years. Not to mention the definition of a SET would need to be changed and all of the optimizers of database servers rethought, since this table's order could affect the users of the table.

If you want the data to be consistently returned in an order via code, it is best use a stored procedure.

|||

Ahah! Now at least I think it makes a little more sense. I did not think about using stored procedues; I've been using views for a long time, and that worked fine in 2000. So part of the issue was my ignorance (blush). Converting to stored procedures is a perfectly acceptable solution. (And there are probably other benefits to that as well?)

So it's no longer a STUPID BUG it's a DANGEROUS TRAP that IGNORANT USERS can fall into when they UPGRADE

If the ORDER BY is not respected in it's intuitive sense, I think it should not be specifyable unless it is somehow tied directly to the TOP statement.

Thanks for the response Louis.

|||

This is currently the case. Try to build a view with an order by any you get a nasty message:

create view test
as
select *
from sysobjects
order by 1

In 2000:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

In 2005:
Msg 1033, Level 15, State 1, Procedure test, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I know how you feel though. I had used this in my views in past versions, (as well as the fact that clustered data was naturally returned in clustered order) and for the most part it still holds true, especially in testing, because it is usually easier for to return the data in order because if the query processor needed it in order to determine the TOP rows, it is unlikely to be faster to reorder. But as the query optimizer/processor gets more and more sophisicated, the more likely it is that they will find ways to maximize output and lose the ordering in the process.

In all cases it is better to either use ORDER BY, or just let the client sort the data.

|||Yes but creating a new view in Enterprise Manager defaults to "TOP (100) PERCENT" - and when this is present, the Order By is accepted. In this case, the Order By is meaningless and dangerously misleading.|||

Now you've nailed it. This behavior, which unfortunately persists in SQL Server 2005 Enterprise Manager's Query and View Designer, is idiotic. A good place to add your voice to the chorus is at the Microsoft Product Feeback center, where you can vote on the at least three bugs/suggestions about this behavior (URL may wrap). http://lab.msdn.microsoft.com/productfeedback/SearchResults.aspx?text=view+%26quot%3border+by%26quot%3b+percent&stype=1&fields=1&type=0&witId=0&pId=0&category=0&os=0&oslang=0&status=0&msstatus=0&resolution=0&chgdays=&validation=0&votes=&voterating=0&workarounds=False&attachments=False SK Brent Mullet@.discussions.microsoft.com wrote:
> Yes but creating a new view in Enterprise Manager defaults to "TOP (100)
> PERCENT" - and when this is present, the Order By is accepted. In this
> case, the Order By is meaningless and dangerously misleading.
>

ORDER BY Aggregate and Grouping

I apologize for this simple question. I'm trying to create a query such tha
t
the results are grouped by 'City' and the cities are returned in decending
order by the Sum(cash).
I'd like the data returned like this:
city nonAgg1 nonAgg2 'sum'
----
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name1 'some value' 'some value' 11
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
I've been using a query like below:
SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
GROUP BY City, nonAgg1, nonAgg2
ORDER BY SUM(cash) DESC, City
The (poorly written) query is, of course, ordering the values as such:
city nonAgg1 nonAgg2 'sum'
----
name1 'some value' 'some value' 11
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
How can I rewrite the query so that it appears in the order I'm trying to
get it in?Hi, Newbie412
You should post DDL and sample data as "CREATE TABLE ..." statements
and "INSERT INTO ... VALUES ..." statements, like this:
CREATE TABLE TheTable (
TheDate datetime,
city varchar(30),
nonAgg1 varchar(20),
nonAgg2 varchar(20),
cash money,
PRIMARY KEY (TheDate, city, nonAgg1, nonAgg2)
)
INSERT INTO TheTable VALUES ('20051220','name1', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name1', 'a', 'x', 6)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'x', 10)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'y', 9)
INSERT INTO TheTable VALUES ('20051220','name3', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name3', 'a', 'x', 4)
INSERT INTO TheTable VALUES ('20051220','name3', 'b', 'y', 1)
The following query returns the expected results:
SELECT t.City, nonAgg1, nonAgg2, SUM(cash) SumOfCash
FROM TheTable t INNER JOIN (
SELECT City, SUM(cash) as CityCash
FROM TheTable
GROUP BY City
) x ON t.City=x.City
GROUP BY t.City, x.CityCash, nonAgg1, nonAgg2
ORDER BY CityCash DESC, t.City
Razvan|||Hi
CREATE TABLE #Test (col1 CHAR(1) NOT NULL, col2 INT NOT NULL)
INSERT INTO #Test VALUES ('A',10)
INSERT INTO #Test VALUES ('A',100)
INSERT INTO #Test VALUES ('A',20)
INSERT INTO #Test VALUES ('B',500)
INSERT INTO #Test VALUES ('C',1)
INSERT INTO #Test VALUES ('C',8)
SELECT col1,col2 FROM #test ORDER BY col1 ASC,col2 DESC
DROP TABLE #Test
"Newbie412" <Newbie412@.discussions.microsoft.com> wrote in message
news:6F96CBC0-E42D-460B-8B4B-417E575350DA@.microsoft.com...
>I apologize for this simple question. I'm trying to create a query such
>that
> the results are grouped by 'City' and the cities are returned in decending
> order by the Sum(cash).
> I'd like the data returned like this:
> city nonAgg1 nonAgg2 'sum'
> ----
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name1 'some value' 'some value' 11
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> I've been using a query like below:
> SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
> GROUP BY City, nonAgg1, nonAgg2
> ORDER BY SUM(cash) DESC, City
> The (poorly written) query is, of course, ordering the values as such:
> city nonAgg1 nonAgg2 'sum'
> ----
> name1 'some value' 'some value' 11
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> How can I rewrite the query so that it appears in the order I'm trying to
> get it in?|||Newbie412,
Here are solutions for SQL Server 2000 and 2005, if I
understood your requirements correctly.
CREATE TABLE #Test (
City VARCHAR(5) NOT NULL,
nonagg INT,
cash INT NOT NULL
)
INSERT INTO #Test VALUES ('name1',1,6)
INSERT INTO #Test VALUES ('name1',1,1)
INSERT INTO #Test VALUES ('name1',1,4)
INSERT INTO #Test VALUES ('name2',1,10)
INSERT INTO #Test VALUES ('name2',2,4)
INSERT INTO #Test VALUES ('name2',2,5)
INSERT INTO #Test VALUES ('name3',1,9)
INSERT INTO #Test VALUES ('name3',2,1)
-- SQL Server 2000
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM (
SELECT
City,
nonagg,
cash,
(select sum(cash) from #Test as T2
where T2.City = T1.City) as orderKey
from #Test as T1
) AS T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
-- SQL Server 2005
;
WITH T(City,nonagg,cash,orderKey) AS (
SELECT
City,
nonagg,
cash,
sum(cash) over (partition by City) as orderKey
from #Test
)
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
DROP TABLE #Test
-- Steve Kass
-- Drew University
Newbie412 wrote:

>I apologize for this simple question. I'm trying to create a query such th
at
>the results are grouped by 'City' and the cities are returned in decending
>order by the Sum(cash).
>I'd like the data returned like this:
>city nonAgg1 nonAgg2 'sum'
>----
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name1 'some value' 'some value' 11
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>I've been using a query like below:
>SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
>GROUP BY City, nonAgg1, nonAgg2
>ORDER BY SUM(cash) DESC, City
>The (poorly written) query is, of course, ordering the values as such:
>city nonAgg1 nonAgg2 'sum'
>----
>name1 'some value' 'some value' 11
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>How can I rewrite the query so that it appears in the order I'm trying to
>get it in?
>

Order by & case

I am trying to use a case statment to indicate sort order. Everything worked fine untill I added "desc".

create table #tmp(f1 varchar(25), dtObs datetime, dtCnt datetime)
insert into #tmp values('ABC','01-Jan-2003','02-Jan-2003')
insert into #tmp values('BCD','01-Jan-2003','03-Jan-2003')
insert into #tmp values('CDE','01-Jan-2003','04-Jan-2003')
insert into #tmp values('DEF','02-Jan-2003','03-Jan-2003')
insert into #tmp values('EFG','02-Jan-2003','04-Jan-2003')
insert into #tmp values('FGH','02-Jan-2003','05-Jan-2003')

declare @.Order tinyint
set @.Order = 1
select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
when 3 then 'dtObs desc'
when 4 then 'dtCnt desc'
else null end

any suggestion on how to do this?select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
else null end
,case @.Order when 3 then 'dtObs'
when 4 then 'dtCnt'
else null end DESC|||Thanks!|||Pleasure to help you.|||I am using a Case statement within my Order By clause, but I have columns that I want to use that were created in my Select clause as expressions. I cannot seem to use those columns in my Case statement. Is there a way to do this?? Thank you very much for your help!!!sql

ORDER BY - parameter

In order to allow our users to create custom report queries, we've created a report with two parameters: "Where" and "OrderBy". In the Where parameter we pass the columns to be checked (Division = 'south' AND price > 100). This works great. The "OrderBy" parameter also works, if and only if, one column name is passed. If we attempt to pass a parameter (both in the designer and URL string) formatted as "col1, col2", the preview fails when the comma is encountered. Is it not possible to create a parameter that contains more than one column for the ORDER BY clause (parameter)?
ThanksThis works, I used this type of parameter all the time. Is you query an
expression? Or are you using an @. parameter. It will only work as an
expression. To pass the parameter on the ULR it must be encoded.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> In order to allow our users to create custom report queries, we've created
a report with two parameters: "Where" and "OrderBy". In the Where parameter
we pass the columns to be checked (Division = 'south' AND price > 100). This
works great. The "OrderBy" parameter also works, if and only if, one column
name is passed. If we attempt to pass a parameter (both in the designer and
URL string) formatted as "col1, col2", the preview fails when the comma is
encountered. Is it not possible to create a parameter that contains more
than one column for the ORDER BY clause (parameter)?
> Thanks|||John.,
I changed your Where parameter name to WherePrm and added OrderBy1, OrderBy2
parameters. If needed assign default values.
SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE @.WherePrm ORDER BY @.OrderBy1, @.OrderBy2
Cem
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >|||Remove the parens in the order by. It is not valid SQL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >

Oracle with Reporting Services

I am trying to connect to an Oracle database using Reporting Services.
I create a new Shared Data Source, give it a name, then hit the edit
button. Set the provider to Microsoft OLE DB Provider for Oracle, on
connection tab I enter
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID = ServiceName)))
I enter the username and password, hit the test and everything tests
out ok. I ok out, go to create a new report and when I go to use the
DataSource I get an error of
A connection cannot be made to the database.
Set and test the connection string.
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater.
Any help would be appreciated. Thanks.OK, are you using RS 2000. Here is how it works in RS 2000. When creating
the query it uses OLE DB if you are using the graphical editor (4 panes). If
you use the generic it uses the dotnet provider. The generic is 2 panes
(there is a button to switch to generic to the right of the ...). When
running the report it uses the dotnet provider. The reason for this was that
VS 2003 query designer did not have support for the dotnet provider so they
did this workaround. The dotnet provider requires 8.1.7 client to be
installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdcamp" <camsam50@.hotmail.com> wrote in message
news:1145550738.077059.160290@.t31g2000cwb.googlegroups.com...
>I am trying to connect to an Oracle database using Reporting Services.
> I create a new Shared Data Source, give it a name, then hit the edit
> button. Set the provider to Microsoft OLE DB Provider for Oracle, on
> connection tab I enter
> (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST => server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID => ServiceName)))
> I enter the username and password, hit the test and everything tests
> out ok. I ok out, go to create a new report and when I go to use the
> DataSource I get an error of
> A connection cannot be made to the database.
> Set and test the connection string.
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater.
> Any help would be appreciated. Thanks.
>|||I ran into this exact same problem yesterday...you need to install the
Oracle Client Tools on the report server. w/o the client tools your
windows box doesn't natively understand how to connect to the oracle
db.
After you install the Oracle Client on the report server you will need
to update your tnsnames.ora and sqlnet.ora files with the correct host
settings. Your Oracle dba's should have these but here's some examples
to get you going in case they dont...
If you use the default install of the client tools you will find these
files in the following location (C:\oracle\ora90\network\ADMIN)
tnsnames.ora example
================================<Server Alias> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.somewhere.com)(PORT =9999))
)
(CONNECT_DATA = (SERVICE_NAME = <ServiceName>)
)
)
sqlnet.ora example
================================SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
Hope this helps!
Cheers
--
Ben Sullins

Oracle to SQL SERVER

Oracle 9i to SQL 2K
Can someone shed some light , how can I create SQL server db objects from
the dmp file (export from Oracle).
I need to create the schema and later load data in SQL SERVER..
Any links or related topics will be highly appreciated.
Thanks
ShThis should help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part2/c0761.asp
I don't believe you can go directly from the dmp file to a sql schema but
you should be able to use DTS to get you going. The doc will point you in
the right direction though.
--
Andrew J. Kelly
SQL Server MVP
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:una9y6abDHA.2476@.tk2msftngp13.phx.gbl...
> Oracle 9i to SQL 2K
> Can someone shed some light , how can I create SQL server db objects from
> the dmp file (export from Oracle).
> I need to create the schema and later load data in SQL SERVER..
> Any links or related topics will be highly appreciated.
> Thanks
> Sh
>
>

Oracle SQL *Plus

Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way in SQL Server 2005 to do that same by using Oracle
SQL syntax in a SQL Server 2005 query?If you use proprietary syntax like TO_CHAR/TO_DATE/TO_NUMBER/SEQUENCE etc.,
then you will have a very hard time automating that conversion to valid SQL
Server Transact-SQL. However, if your SQL is fairly standard, your code
should port without difficulty. I don't fully understand how you integrate
SQL*Plus and Access using a pass-through query, but as far as syntax is
concerned, it either works or you will need to change it. I don't know of
any tools out there that will reliably change proprietary syntax from Oracle
PL/SQL to Transact-SQL or the other way. But I haven't exactly been in the
market for one of those, either. :-)
You don't need to post in HTML. Most people here won't see it that way
anyway; most of us will just get two copies of your content, which is pretty
useless, imho.
A
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access
database. Is there a way in SQL Server 2005 to do that same by using Oracle
SQL syntax in a SQL Server 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access
database. Is there a way in SQL Server 2005 to do that same by using Oracle
SQL syntax in a SQL Server 2005 query?|||Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?|||Yes, I want to access on an Oracle server from the SQL Server. Specifically
save as views. Right now when the data is pulled, it is very slow. I have
found that in Access if I change the SQL syntax to be Oracle SQL syntax by
creating a SQL Pass Thru Query, it pulls the data faster.
Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?|||Then you should use the OPENQUERY function, which allow you to do pass-throu
gh.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eFZk4$IPGHA.3576@.TK2MSFTNGP15.phx.gbl...
Yes, I want to access on an Oracle server from the SQL Server. Specifically
save as views. Right
now when the data is pulled, it is very slow. I have found that in Access i
f I change the SQL
syntax to be Oracle SQL syntax by creating a SQL Pass Thru Query, it pulls t
he data faster.
Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Yes, I want to access on an Oracle server from the SQL Server. Specifically
save as views. Right
now when the data is pulled, it is very slow. I have found that in Access i
f I change the SQL
syntax to be Oracle SQL syntax by creating a SQL Pass Thru Query, it pulls t
he data faster.
Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?|||Ok, I will research on that. Thanks a lot!
Then you should use the OPENQUERY function, which allow you to do pass-throu
gh.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eFZk4$IPGHA.3576@.TK2MSFTNGP15.phx.gbl...
Yes, I want to access on an Oracle server from the SQL Server. Specifically
save as views. Right
now when the data is pulled, it is very slow. I have found that in Access i
f I change the SQL
syntax to be Oracle SQL syntax by creating a SQL Pass Thru Query, it pulls t
he data faster.
Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Yes, I want to access on an Oracle server from the SQL Server. Specifically
save as views. Right
now when the data is pulled, it is very slow. I have found that in Access i
f I change the SQL
syntax to be Oracle SQL syntax by creating a SQL Pass Thru Query, it pulls t
he data faster.
Do you want to access data on an Oracle server from SQL Server through a lin
ked server? If so, check
out the OPENQUERY function. If the data will actually be stored on the SQL s
erver, then see Aaron's
post. And, MS has released (I believe), a tool to assist in Oracle to SQL Se
rver migration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:OsA$CqIPGHA.3924@.TK2MSFTNGP14.phx.gbl...
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?
----
--
Currently, I use Oracle SQL *Plus to create pass thru queries in an Access d
atabase. Is there a way
in SQL Server 2005 to do that same by using Oracle SQL syntax in a SQL Serve
r 2005 query?

Oracle Sequence/Link 2 MS SQL SERVER 2005

hi,

1. is there a statement in ms sql, what creates a sequence? cant find
anything in web :-(
-oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE
CACHE 20;
-ms sql: ?

2. hwo do i create a link to another ms-sql database

thx a lot need help, urgend :-)

Quote:

Originally Posted by

2. hwo do i create a link to another ms-sql database

From one MS SQL to another MS SQL server? Or database?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||

Quote:

Originally Posted by

1. is there a statement in ms sql, what creates a sequence? cant find
anything in web :-(
-oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE
CACHE 20;
-ms sql: ?

Microsoft SQL Server doesn't have sequences. It does have some sort
of auto-increment integer thingy for columns, check out "identity" in the
documentation.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||thx Martijn|||Martijn Tonies schrieb:

Quote:

Originally Posted by

Quote:

Originally Posted by

2. hwo do i create a link to another ms-sql database

>
From one MS SQL to another MS SQL server? Or database?
>
>
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

the link is between two ms-sql servers|||ruediger.michels@.feltengmbh.de wrote:

Quote:

Originally Posted by

hi,
>
1. is there a statement in ms sql, what creates a sequence? cant find
anything in web :-(
-oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE
CACHE 20;
-ms sql: ?
>
2. hwo do i create a link to another ms-sql database
>
thx a lot need help, urgend :-)

The only other databases that have abilities similar to those of
an Oracle SEQUENCE are DB2 and Informix (only the most recent release).
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||

Quote:

Originally Posted by

the link is between two ms-sql servers

Set up a "Linked Server", check the documentation for that. It's really
easy to do :)

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||1. is there a statement in ms sql, what creates a sequence? cant find

Quote:

Originally Posted by

Quote:

Originally Posted by

anything in web :-(
-oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE
CACHE 20;
-ms sql: ?

2. hwo do i create a link to another ms-sql database

thx a lot need help, urgend :-)

>
The only other databases that have abilities similar to those of
an Oracle SEQUENCE are DB2 and Informix (only the most recent release).

And InterBase, Firebird, PostgreSQL, Mimer, ThinkSQL and probably some
others that
I don't know about :-)

btw, SEQUENCE is in the SQL 2003 standard.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||The only other databases that have abilities similar to those of

Quote:

Originally Posted by

an Oracle SEQUENCE are DB2 and Informix (only the most recent release).

What about ROWNUMBER() in SQL Server 2005 and PARTITION??

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1164904167.680393@.bubbleator.drizzle.com...

Quote:

Originally Posted by

ruediger.michels@.feltengmbh.de wrote:

Quote:

Originally Posted by

>hi,
>>
>1. is there a statement in ms sql, what creates a sequence? cant find
>anything in web :-(
> -oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE
> CACHE 20;
> -ms sql: ?
>>
>2. hwo do i create a link to another ms-sql database
>>
>thx a lot need help, urgend :-)

>
The only other databases that have abilities similar to those of
an Oracle SEQUENCE are DB2 and Informix (only the most recent release).
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

|||Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>The only other databases that have abilities similar to those of
>an Oracle SEQUENCE are DB2 and Informix (only the most recent release).

>
What about ROWNUMBER() in SQL Server 2005 and PARTITION??

Totally different capabilities.

A sequence is not tied to a table: It is an independent object.

One can use a sequence to number count by any increment positive or
negative, assign the values to one table or to multiple tables, and
to repeatedly cycle through a fixed set of numbers (min to max and
back to min), and much more.

Maybe in SQL Server 2009?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||Oh - you mean SQL Server 2007? Remember Mark Souza committed themselves to a
2 year release cycle...

If I sat down and worked it out then what we have now in terms of CTE's,
table structures, triggers I could do it - sadly, no time [at mo].

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1164990743.577953@.bubbleator.drizzle.com...

Quote:

Originally Posted by

Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>The only other databases that have abilities similar to those of
>>an Oracle SEQUENCE are DB2 and Informix (only the most recent release).

>>
>What about ROWNUMBER() in SQL Server 2005 and PARTITION??

>
Totally different capabilities.
>
A sequence is not tied to a table: It is an independent object.
>
One can use a sequence to number count by any increment positive or
negative, assign the values to one table or to multiple tables, and
to repeatedly cycle through a fixed set of numbers (min to max and
back to min), and much more.
>
Maybe in SQL Server 2009?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

|||Ruediger,

How is the sequence used in the app?
While it is true that sequences are divorced from tables teh majority of
usages that I know of is for one of two purposes:
* Generate unique value across the database. If that's the case I would
look at GUID.
* Generate an abstract primary key for a specific table (or many tables,
but without actual requirement for x-database uniqueness).
In that case IDENTITY columns are the way to go.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:

Quote:

Originally Posted by

Oh - you mean SQL Server 2007? Remember Mark Souza committed themselves
to a 2 year release cycle...

Mark Souza? You are probably thinking of Paul Flessner.

Quote:

Originally Posted by

If I sat down and worked it out then what we have now in terms of CTE's,
table structures, triggers I could do it - sadly, no time [at mo].

Itzik Ben-Gan has come up with an idea where you us an table with a single
identity column. You have a stored procedure that either starts a
transaction, or if a transaction is already in progress, it issues a
SAVE TRANSACTION. The procedure then inserts a row into the table and
retrieves the identity value with scope_identity(). Finally it rolls
back the transaction, either entirely or to the savepoint. Thus, table
is always empty, but it still produces a growing sequence. Since the table
is locked for only short duration, the concurrency is good.

A fairly convoluted solution, and likely to be less efficient than what
they have on Oracle.

Then again, in many cases a plain IDENTITY column will do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Itzik Ben-Gan has come up with an idea where you us an table with a single
identity column. You have a stored procedure that either starts a
transaction, or if a transaction is already in progress, it issues a
SAVE TRANSACTION. The procedure then inserts a row into the table and
retrieves the identity value with scope_identity(). Finally it rolls
back the transaction, either entirely or to the savepoint. Thus, table
is always empty, but it still produces a growing sequence. Since the table
is locked for only short duration, the concurrency is good.
>
A fairly convoluted solution, and likely to be less efficient than what
they have on Oracle.
>

Itzik's article is here:
http://www.sqlmag.com/Article/Artic...rver_48165.html
It does overcome some of the disadvantages of an IDENTITY column and
I've used variations of it very successfully. I am looking forward to
the day when we get an engine-level implementation of sequences and I
never have to use an IDENTITY column again.

--
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/...US,SQL.90).aspx
--|||Good stuff.

Nope - it was Mark Souza at TechEd / IT Forum last year; there is a press
release somewhere.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns988EAF2CAD53EYazorman@.127.0.0.1...

Quote:

Originally Posted by

Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:

Quote:

Originally Posted by

>Oh - you mean SQL Server 2007? Remember Mark Souza committed themselves
>to a 2 year release cycle...

>
Mark Souza? You are probably thinking of Paul Flessner.
>

Quote:

Originally Posted by

>If I sat down and worked it out then what we have now in terms of CTE's,
>table structures, triggers I could do it - sadly, no time [at mo].

>
Itzik Ben-Gan has come up with an idea where you us an table with a single
identity column. You have a stored procedure that either starts a
transaction, or if a transaction is already in progress, it issues a
SAVE TRANSACTION. The procedure then inserts a row into the table and
retrieves the identity value with scope_identity(). Finally it rolls
back the transaction, either entirely or to the savepoint. Thus, table
is always empty, but it still produces a growing sequence. Since the table
is locked for only short duration, the concurrency is good.
>
A fairly convoluted solution, and likely to be less efficient than what
they have on Oracle.
>
Then again, in many cases a plain IDENTITY column will do.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Oracle reports using SQL Reporting Services

Hi! I am using SQL Reporting Services to create reports on data that is
stored on an Oracle server. I am unable to use query parameters when
accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
use in Reporting Services? Any help would be greatly appreciated.
Just to clarify, it's something similar to this (larger scale, of
course):
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = @.ID
Whenever I create a query parameter, Reporting Services tells me that I
am missing an expression...The managed Oracle provider (data source type: "Oracle") uses ":" instead of
"@." to identify parameters. Try this instead:
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = :ID
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nandan" <nandanrp@.gmail.com> wrote in message
news:1128455599.396906.96420@.g47g2000cwa.googlegroups.com...
> Hi! I am using SQL Reporting Services to create reports on data that is
> stored on an Oracle server. I am unable to use query parameters when
> accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
> use in Reporting Services? Any help would be greatly appreciated.
> Just to clarify, it's something similar to this (larger scale, of
> course):
> SELECT FirstName, LastName
> FROM EmployeeInformation
> WHERE EmployeeID = @.ID
> Whenever I create a query parameter, Reporting Services tells me that I
> am missing an expression...
>|||Yes that did it! Thanks!!!

Oracle Procedure with OUT Parameters

I get this error message when I try to create a DataSet for an Oracle
procedure that has an OUT Parameter.
PLS-00306: wrong number or types of parameters in call to 'procedure name'
The Error happens when I click on Refresh Fields.
I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
get this message when the procedure has other out types like DATE or CHAR.
Any help would be greatly appreciated.
FabianOnly out ref cursors are supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fabian" <Fabian@.discussions.microsoft.com> wrote in message
news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> I get this error message when I try to create a DataSet for an Oracle
> procedure that has an OUT Parameter.
> PLS-00306: wrong number or types of parameters in call to 'procedure name'
> The Error happens when I click on Refresh Fields.
> I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> get this message when the procedure has other out types like DATE or CHAR.
> Any help would be greatly appreciated.
> Fabian|||Thank you Robert. I wrote a wrapper.
Fabian
"Robert Bruckner [MSFT]" wrote:
> Only out ref cursors are supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Fabian" <Fabian@.discussions.microsoft.com> wrote in message
> news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> > I get this error message when I try to create a DataSet for an Oracle
> > procedure that has an OUT Parameter.
> >
> > PLS-00306: wrong number or types of parameters in call to 'procedure name'
> >
> > The Error happens when I click on Refresh Fields.
> >
> > I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> > get this message when the procedure has other out types like DATE or CHAR.
> >
> > Any help would be greatly appreciated.
> >
> > Fabian
>
>

Oracle Problems...

I get:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified

when trying to view a report that runs in Visual Studio .NET. After I deploy the report to the Reporting Services server I get the error. There must be a problem in the server's configuration, right? What can I do?

I seem to have gotten this to work. I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0. I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information. After not find anything, I decided to delete the key again and bounce the server. PRESTO!

Oracle OBJECTs

Hello,

Has any one used object-oriented features of PL/SQL?

I know you can do something like the following.

CREATE TYPE employee AS OBJECT (...)

My biggest concern is performance: Do the objects in Oracle negatively affect performance? Anything else I should be aware of?

Thanks in advance,
EdwardHello Edward,

when you use TYPE for accessing or handling datas the performance will not change. Thats what we find out in our projects.
The benefit of using types is accesing complex datas very handy and often with one select.

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
SQL Server Enterprise Manager
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David
|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>
|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> > We are attempting to create a linked server to an Oracle database and it
> > isn't working.
> >
> > We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> > installed the Oracle 9.2.0.1 client and am able to successfully connect to
> > the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> > creating an ODBC DSN using the Oracle driver provided by Oracle, and
> > hitting
> > the "test connection" button. Both ways work, so I am fairly certain I
> > have
> > the tnsnames.ora file configured correctly.
> >
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I hav
e
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection
"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:

> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David

Oracle 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see an
y
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
ThanksYou can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see a
ny
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
>|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>

Oracle 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see any
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
Thanks
You can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see any
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks
|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>

Oracle 9i Drivers for Link Server?

Hi All,
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>

Oracle 9i Drivers for Link Server?

Hi All,
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.
http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>