Hello,
can any one tell me about the difference between the following queries.
1. SELECT * FROM Symp_User ORDER BY
2. SELECT * FROM Symp_User ORDER BY ASC
I don't think there is any difference in the above queries. kinldy make me clear on this.
thnkx,
rahul jhaServer: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'BY'.
I think you forgot to mention which field you want to order the result set by ;)
ASC/DESC are optional operators used in conjunction with the ORDER BY clause. If omitted ASC is assumed.|||sorry for the miss out. here is the actual query......
1. SELECT * FROM Symp_User ORDER BY LoginName
2. SELECT * FROM Symp_User ORDER BY LoginName ASC
My question is, whether will there be any Execution Plan difference for the above queries?
thnkx
rahul jha|||If omitted ASC is assumed.
See above :D|||thnkx george|||negated? don't you mean omitted?|||Yes, yes I do... :o
Posts edited|||My question is, don't you acces to SQL Server?
I mean it would be very easy to find out if you do, and if you don't, then download the dev version for free
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Friday, March 23, 2012
ORDER BY changing resulting row count
I have three queries that only differ in their ORDER BY clause but
return three different result sets. In the examples below "Takedown"
is a table, "CommitID" is an int column, and "TakedownDate" is a
datetime column, and neither of these columns have NULL values.
select * from Takedown where CommitID = 1006204
-- this returns 34 rows
select * from Takedown where CommitID = 1006204 order by TakedownDate
-- this returns 33 rows!
select * from Takedown where CommitID = 1006204 order by TakedownDate
desc
-- this returns 4 rows!
I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
on XP Pro SP2. I have restarted SQL Server, updated statistics, and
even tried to order by the column's ordinal position instead of name
all with the exact same results.
Anyone have any ideas how this could happen or where I might look for
more info?
Thanks.
TedFirst thing you need to do is install SP3. You are still running the
RTM version, which is practically ancient history. Aside from the known
security vulnerabilities you are exposed to there are also some fixes
that might be relevant to your problem.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Post again if you still experience problems in SP3.
David Portas
SQL Server MVP
--|||Try,
exec sp_updatestats @.resample = 'resample'
AMB
"Ted O'Connor" wrote:
> I have three queries that only differ in their ORDER BY clause but
> return three different result sets. In the examples below "Takedown"
> is a table, "CommitID" is an int column, and "TakedownDate" is a
> datetime column, and neither of these columns have NULL values.
> select * from Takedown where CommitID = 1006204
> -- this returns 34 rows
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> -- this returns 33 rows!
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> desc
> -- this returns 4 rows!
> I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
> on XP Pro SP2. I have restarted SQL Server, updated statistics, and
> even tried to order by the column's ordinal position instead of name
> all with the exact same results.
> Anyone have any ideas how this could happen or where I might look for
> more info?
> Thanks.
> Ted
>|||If it does not help, try also:
UPDATE STATISTICS table_name WITH FULLSCAN, ALL
AMB
"Alejandro Mesa" wrote:
> Try,
> exec sp_updatestats @.resample = 'resample'
>
> AMB
> "Ted O'Connor" wrote:
>|||Addtional info...
I have found that there is a float column named BaseToLocal and that
when its excluded from the SELECT the result set is always 38 rows
(which is correct). There appears to be a record where the BaseToLocal
float value = -1.#INF and this is throwing everything off. This table
was populated using DTS with a Paradox table as the source.|||I upgraded my service pack and statistics but the results are the same
when run to grid.
However when I run the results to text I get the partial result set and
"[Microsoft][ODBC SQL Server Driver]Numeric value out of range" which I
assume is do to the -1.#INF value.
My question is how could a float column be storing an out of range
value? Should that have failed somehow during the DTS import?sql
return three different result sets. In the examples below "Takedown"
is a table, "CommitID" is an int column, and "TakedownDate" is a
datetime column, and neither of these columns have NULL values.
select * from Takedown where CommitID = 1006204
-- this returns 34 rows
select * from Takedown where CommitID = 1006204 order by TakedownDate
-- this returns 33 rows!
select * from Takedown where CommitID = 1006204 order by TakedownDate
desc
-- this returns 4 rows!
I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
on XP Pro SP2. I have restarted SQL Server, updated statistics, and
even tried to order by the column's ordinal position instead of name
all with the exact same results.
Anyone have any ideas how this could happen or where I might look for
more info?
Thanks.
TedFirst thing you need to do is install SP3. You are still running the
RTM version, which is practically ancient history. Aside from the known
security vulnerabilities you are exposed to there are also some fixes
that might be relevant to your problem.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Post again if you still experience problems in SP3.
David Portas
SQL Server MVP
--|||Try,
exec sp_updatestats @.resample = 'resample'
AMB
"Ted O'Connor" wrote:
> I have three queries that only differ in their ORDER BY clause but
> return three different result sets. In the examples below "Takedown"
> is a table, "CommitID" is an int column, and "TakedownDate" is a
> datetime column, and neither of these columns have NULL values.
> select * from Takedown where CommitID = 1006204
> -- this returns 34 rows
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> -- this returns 33 rows!
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> desc
> -- this returns 4 rows!
> I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
> on XP Pro SP2. I have restarted SQL Server, updated statistics, and
> even tried to order by the column's ordinal position instead of name
> all with the exact same results.
> Anyone have any ideas how this could happen or where I might look for
> more info?
> Thanks.
> Ted
>|||If it does not help, try also:
UPDATE STATISTICS table_name WITH FULLSCAN, ALL
AMB
"Alejandro Mesa" wrote:
> Try,
> exec sp_updatestats @.resample = 'resample'
>
> AMB
> "Ted O'Connor" wrote:
>|||Addtional info...
I have found that there is a float column named BaseToLocal and that
when its excluded from the SELECT the result set is always 38 rows
(which is correct). There appears to be a record where the BaseToLocal
float value = -1.#INF and this is throwing everything off. This table
was populated using DTS with a Paradox table as the source.|||I upgraded my service pack and statistics but the results are the same
when run to grid.
However when I run the results to text I get the partial result set and
"[Microsoft][ODBC SQL Server Driver]Numeric value out of range" which I
assume is do to the -1.#INF value.
My question is how could a float column be storing an out of range
value? Should that have failed somehow during the DTS import?sql
Wednesday, March 21, 2012
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
> >
> >
> >
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
> >
> >
> >
Monday, March 12, 2012
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?
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?
Friday, March 9, 2012
oracle pl/sql multiple spool help required
Now Im not an expert or anything with SQL so bear with me...
I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:
set blah
set blah
spool /blah/blah.rpt
script
spool off
I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?
I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...
Any help would be great. Thanks.Yes, you can. For example,
spool a1.txt
select count(*) from tab;
spool off;
spool a2.txt
select sysdate from dual;
spool off;
spool a3.txt
select 'x' dummy from dual;
spool off;
will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...
I have the three spools and also I have a title for each of the three files:
e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2
I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.
I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.
SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
SELECT COUNT (*) FROM tab;
SPOOL off;
SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.
I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:
set blah
set blah
spool /blah/blah.rpt
script
spool off
I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?
I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...
Any help would be great. Thanks.Yes, you can. For example,
spool a1.txt
select count(*) from tab;
spool off;
spool a2.txt
select sysdate from dual;
spool off;
spool a3.txt
select 'x' dummy from dual;
spool off;
will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...
I have the three spools and also I have a title for each of the three files:
e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2
I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.
I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.
SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
SELECT COUNT (*) FROM tab;
SPOOL off;
SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.
Oracle parameterized queries to update Oracle table do not work
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.|||
Phil Brammer wrote:
Load your updates to a table and then perform an Execute SQL task in the control flow to perform a batch update instead of trying to update row by row.
Thanks Phil,
Your post is really helpful, however, I have a couple of more questions. 
1. You said load the updates to a table, this has to be a temp table I guess?
2. Could you please write in more details on how to "perform a batch update" in an Execute SQL task, such as what the SQL statement will look like? Do I still need to provide parameters, such as "update dest_table set xxx=? where yyy=?"
Thanks a lot!
Wenbiao
|||Yes, it is a temp table. But not a #temp table. It's just a physical table that you store the updates in. When you're done, you can issue an Execute SQL task to truncate that table.As far as the batch update goes: (Something like this, I believe)
UPDATE myTable a INNER JOIN stagingTable b ON a.key = b.key
SET a.field1 = b.field1, a.field2 = b.field2|||
here is an example of the update syntax:
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
Wednesday, March 7, 2012
Oracle Linked Servers
I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Oracle linked server tables
I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David
|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen
|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen
|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen
|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen
|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen
|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David
|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen
|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen
|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen
|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen
|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen
|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>
Oracle linked server tables
I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "thi
s
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they cou
ld
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, b
ut
> I don't see how they help me in my need. I can't see anything that gets m
e
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Orac
le
> 9.2 databases. I want to be able to view the Oracle tables in SS Manageme
nt
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can
't
> get any farther with the resources at hand, and MS docs, as I said, come u
p
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "thi
s
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they cou
ld
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, b
ut
> I don't see how they help me in my need. I can't see anything that gets m
e
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Orac
le
> 9.2 databases. I want to be able to view the Oracle tables in SS Manageme
nt
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can
't
> get any farther with the resources at hand, and MS docs, as I said, come u
p
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>
Oracle linked server tables
I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are placed
>> in the MASTER table (if not all..). Furthermore you can look them up in
>> Books On Line where you can get the syntax for executing them as well.
>> Regards
>> Steen
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Thanks Steen.
>> I am not exactly impressed with Books Online. As I said in the previous
>> post, I tend to find a lot of "what you need to do" but not very much
>> "this is how you do it", especially where fundamental details are
>> concerned. That's extremely frustrating to someone trying to learn. Too
>> many assumptions made on the parts of the doc guys. At the very least
>> they could provide links to how-to info within those high level docs. I
>> provide that feedback every chance I get.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up
>> until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are
>> placed in the MASTER table (if not all..). Furthermore you can look them
>> up in Books On Line where you can get the syntax for executing them as
>> well.
>> Regards
>> Steen
>>
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>|||Randall Arnold wrote:
> Steen,
> The problem is, as I said in another post, that I don't know what to do with
> these stored procedures. I can't see how to execute them. I'm used to
> running SQL Server stored procedures from an Access data project, and that's
> really straight-forward. I see no way to run an sp in Management Studio.
> The Help was no help whatsoever. Again: the docs say what to do, or what
> something is, but not how I go about using it.
> Very, very exasperating for someone trying to learn.
> I also don't have direct access to the Oracle server. IT was reluctant to
> even allow the read-only access I have. We are very bureaucratic here, and
> developers are frowned upon.
> I've determined, though, that I don't actually need to view the tables in SS
> Management Studio-- I just need to link them into a new Access Data Project.
> However, there is a known bug in Access that keeps DSNs from working, so
> Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
> server fails to save the connection information for the linked Oracle server
> into its properties sheet. And for reasons I absolutely cannot fathom, MS
> refuses to allow those property sheets to be edited once they're created!
> So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
> I am supposed to create and deploy this aggregated database, and the usual
> MS nonsense keeps it from happening. I'm just a wee bit irritated about
> that situation...
> I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think you
>> can expect to get a "full blown" and easy overwiev of the table structure
>> of a linked server. If you need more than this, I think you'll have to use
>> the administration interface for the ORACLE server.
>> Regards
>> Steen
>
Sorry...I never understood that you didn't knew how to actually run the
sp's. You simple have to open a query window and then type in e.g.
EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5.
This will excute the query.
Regards
Steen|||Thanks for your patience with my ignorance Steen.
At first I couldn't find a query window, but stumbled across the Database
Engine Query icon on the toolbar and that does the trick.
After some more digging, I realized that all I need to do is change the
connection properties of the linked servers that the Microsoft Access linked
table wizard creates. But as I said before, Microsoft for reasons that
completely mistify me locks the property sheet after the linked server is
created. I'd love to find a way to edit it but I haven't so far.
What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
*claims* will enable the admin to change the logins for those linked
servers. I ran it, and it did pull up a table of every linked server and
its properties. What the sp did NOT enable, however, was (once again)
editing of those properties! Despite the fact that KB article 280106
implies that it does.
So, I remain stuck. I could navigate the Oracle tables IF I could get them
into my existing Access data Project. But the adp can't see my existing
linked servers and every time its wizard creates one the new linked server
lacks the proper connection values and I'm unable to edit them.
: (
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:eQiJgJFRGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Steen,
>> The problem is, as I said in another post, that I don't know what to do
>> with these stored procedures. I can't see how to execute them. I'm used
>> to running SQL Server stored procedures from an Access data project, and
>> that's really straight-forward. I see no way to run an sp in Management
>> Studio. The Help was no help whatsoever. Again: the docs say what to do,
>> or what something is, but not how I go about using it.
>> Very, very exasperating for someone trying to learn.
>> I also don't have direct access to the Oracle server. IT was reluctant
>> to even allow the read-only access I have. We are very bureaucratic
>> here, and developers are frowned upon.
>> I've determined, though, that I don't actually need to view the tables in
>> SS Management Studio-- I just need to link them into a new Access Data
>> Project. However, there is a known bug in Access that keeps DSNs from
>> working, so Microsoft recommends using an ODC. Well, that doesn't work,
>> either: SQL server fails to save the connection information for the
>> linked Oracle server into its properties sheet. And for reasons I
>> absolutely cannot fathom, MS refuses to allow those property sheets to be
>> edited once they're created! So, due to this simple act of stupidity, I
>> am just flat stuck in a catch-22. I am supposed to create and deploy this
>> aggregated database, and the usual MS nonsense keeps it from happening.
>> I'm just a wee bit irritated about that situation...
>> I really appreciate your efforts, though. Thanks to MS, I guess I'm
>> doomed.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored
>> procedures, but I don't see how they help me in my need. I can't see
>> anything that gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact
>> I've been looking, but I can't find one that covers this subject at the
>> level I need.
>> Bottom line, I've been using SQL Server at a basic level for a few
>> years (and using Access as a front end for tables and queries) and I'm
>> having to ramp up my level of involvement. I have to create a SQL
>> server database that ties together tables spanning 3 or 4 domains, at
>> least 2 of them Oracle 9.2 databases. I want to be able to view the
>> Oracle tables in SS Management Studio the same way I do native tables.
>> Importing doesn't do me any good because I can't see a way to actually
>> dynamically link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I
>> said, come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think
>> you can expect to get a "full blown" and easy overwiev of the table
>> structure of a linked server. If you need more than this, I think you'll
>> have to use the administration interface for the ORACLE server.
>> Regards
>> Steen
>>
> Sorry...I never understood that you didn't knew how to actually run the
> sp's. You simple have to open a query window and then type in e.g.
> EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5. This
> will excute the query.
> Regards
> Steen|||Randall Arnold wrote:
> Thanks for your patience with my ignorance Steen.
> At first I couldn't find a query window, but stumbled across the Database
> Engine Query icon on the toolbar and that does the trick.
> After some more digging, I realized that all I need to do is change the
> connection properties of the linked servers that the Microsoft Access linked
> table wizard creates. But as I said before, Microsoft for reasons that
> completely mistify me locks the property sheet after the linked server is
> created. I'd love to find a way to edit it but I haven't so far.
> What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
> *claims* will enable the admin to change the logins for those linked
> servers. I ran it, and it did pull up a table of every linked server and
> its properties. What the sp did NOT enable, however, was (once again)
> editing of those properties! Despite the fact that KB article 280106
> implies that it does.
> So, I remain stuck. I could navigate the Oracle tables IF I could get them
> into my existing Access data Project. But the adp can't see my existing
> linked servers and every time its wizard creates one the new linked server
> lacks the proper connection values and I'm unable to edit them.
> : (
> Randall Arnold
Hi Randall
You're right that the article says that you can use
sp_helplinkedsrvlogin to set the login info, but that's not correct.
If you look up sp_helplinkedsrvlogin in Books On Line, it says that it
"Provides information about login mappings defined against a specific
linked server used for distributed queries and remote stored procedures".
I'm not sure where it is you can't change the properties for the linked
server. I'm not familiar with this MS Access Linked Server wizard, but
you you use EnterpriseManager (SQL 2000) or Microsoft SQL Server
Management Studio (SQL2005) you can the possibility to manage linked
servers. In Enterprise Manager is under "Security" -> "Linked Servers".
In Management Studio you'll find it under "Server Object" ->" Linked
Servers".
There are also a number of stored procedures that can be used -
sp_linkedservers
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_droplinkedsrvlogin
sp_dropserver
You can look them up in Books On Line where you can find the syntax and
description.
Linked servers might not always be the easiest thing to work with, and
also I'm not quite sure if you can get all the info you're looking for
in this case.
Regards
Steen
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are placed
>> in the MASTER table (if not all..). Furthermore you can look them up in
>> Books On Line where you can get the syntax for executing them as well.
>> Regards
>> Steen
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Thanks Steen.
>> I am not exactly impressed with Books Online. As I said in the previous
>> post, I tend to find a lot of "what you need to do" but not very much
>> "this is how you do it", especially where fundamental details are
>> concerned. That's extremely frustrating to someone trying to learn. Too
>> many assumptions made on the parts of the doc guys. At the very least
>> they could provide links to how-to info within those high level docs. I
>> provide that feedback every chance I get.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up
>> until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are
>> placed in the MASTER table (if not all..). Furthermore you can look them
>> up in Books On Line where you can get the syntax for executing them as
>> well.
>> Regards
>> Steen
>>
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>|||Randall Arnold wrote:
> Steen,
> The problem is, as I said in another post, that I don't know what to do with
> these stored procedures. I can't see how to execute them. I'm used to
> running SQL Server stored procedures from an Access data project, and that's
> really straight-forward. I see no way to run an sp in Management Studio.
> The Help was no help whatsoever. Again: the docs say what to do, or what
> something is, but not how I go about using it.
> Very, very exasperating for someone trying to learn.
> I also don't have direct access to the Oracle server. IT was reluctant to
> even allow the read-only access I have. We are very bureaucratic here, and
> developers are frowned upon.
> I've determined, though, that I don't actually need to view the tables in SS
> Management Studio-- I just need to link them into a new Access Data Project.
> However, there is a known bug in Access that keeps DSNs from working, so
> Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
> server fails to save the connection information for the linked Oracle server
> into its properties sheet. And for reasons I absolutely cannot fathom, MS
> refuses to allow those property sheets to be edited once they're created!
> So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
> I am supposed to create and deploy this aggregated database, and the usual
> MS nonsense keeps it from happening. I'm just a wee bit irritated about
> that situation...
> I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think you
>> can expect to get a "full blown" and easy overwiev of the table structure
>> of a linked server. If you need more than this, I think you'll have to use
>> the administration interface for the ORACLE server.
>> Regards
>> Steen
>
Sorry...I never understood that you didn't knew how to actually run the
sp's. You simple have to open a query window and then type in e.g.
EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5.
This will excute the query.
Regards
Steen|||Thanks for your patience with my ignorance Steen.
At first I couldn't find a query window, but stumbled across the Database
Engine Query icon on the toolbar and that does the trick.
After some more digging, I realized that all I need to do is change the
connection properties of the linked servers that the Microsoft Access linked
table wizard creates. But as I said before, Microsoft for reasons that
completely mistify me locks the property sheet after the linked server is
created. I'd love to find a way to edit it but I haven't so far.
What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
*claims* will enable the admin to change the logins for those linked
servers. I ran it, and it did pull up a table of every linked server and
its properties. What the sp did NOT enable, however, was (once again)
editing of those properties! Despite the fact that KB article 280106
implies that it does.
So, I remain stuck. I could navigate the Oracle tables IF I could get them
into my existing Access data Project. But the adp can't see my existing
linked servers and every time its wizard creates one the new linked server
lacks the proper connection values and I'm unable to edit them.
: (
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:eQiJgJFRGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Steen,
>> The problem is, as I said in another post, that I don't know what to do
>> with these stored procedures. I can't see how to execute them. I'm used
>> to running SQL Server stored procedures from an Access data project, and
>> that's really straight-forward. I see no way to run an sp in Management
>> Studio. The Help was no help whatsoever. Again: the docs say what to do,
>> or what something is, but not how I go about using it.
>> Very, very exasperating for someone trying to learn.
>> I also don't have direct access to the Oracle server. IT was reluctant
>> to even allow the read-only access I have. We are very bureaucratic
>> here, and developers are frowned upon.
>> I've determined, though, that I don't actually need to view the tables in
>> SS Management Studio-- I just need to link them into a new Access Data
>> Project. However, there is a known bug in Access that keeps DSNs from
>> working, so Microsoft recommends using an ODC. Well, that doesn't work,
>> either: SQL server fails to save the connection information for the
>> linked Oracle server into its properties sheet. And for reasons I
>> absolutely cannot fathom, MS refuses to allow those property sheets to be
>> edited once they're created! So, due to this simple act of stupidity, I
>> am just flat stuck in a catch-22. I am supposed to create and deploy this
>> aggregated database, and the usual MS nonsense keeps it from happening.
>> I'm just a wee bit irritated about that situation...
>> I really appreciate your efforts, though. Thanks to MS, I guess I'm
>> doomed.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored
>> procedures, but I don't see how they help me in my need. I can't see
>> anything that gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact
>> I've been looking, but I can't find one that covers this subject at the
>> level I need.
>> Bottom line, I've been using SQL Server at a basic level for a few
>> years (and using Access as a front end for tables and queries) and I'm
>> having to ramp up my level of involvement. I have to create a SQL
>> server database that ties together tables spanning 3 or 4 domains, at
>> least 2 of them Oracle 9.2 databases. I want to be able to view the
>> Oracle tables in SS Management Studio the same way I do native tables.
>> Importing doesn't do me any good because I can't see a way to actually
>> dynamically link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I
>> said, come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think
>> you can expect to get a "full blown" and easy overwiev of the table
>> structure of a linked server. If you need more than this, I think you'll
>> have to use the administration interface for the ORACLE server.
>> Regards
>> Steen
>>
> Sorry...I never understood that you didn't knew how to actually run the
> sp's. You simple have to open a query window and then type in e.g.
> EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5. This
> will excute the query.
> Regards
> Steen|||Randall Arnold wrote:
> Thanks for your patience with my ignorance Steen.
> At first I couldn't find a query window, but stumbled across the Database
> Engine Query icon on the toolbar and that does the trick.
> After some more digging, I realized that all I need to do is change the
> connection properties of the linked servers that the Microsoft Access linked
> table wizard creates. But as I said before, Microsoft for reasons that
> completely mistify me locks the property sheet after the linked server is
> created. I'd love to find a way to edit it but I haven't so far.
> What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
> *claims* will enable the admin to change the logins for those linked
> servers. I ran it, and it did pull up a table of every linked server and
> its properties. What the sp did NOT enable, however, was (once again)
> editing of those properties! Despite the fact that KB article 280106
> implies that it does.
> So, I remain stuck. I could navigate the Oracle tables IF I could get them
> into my existing Access data Project. But the adp can't see my existing
> linked servers and every time its wizard creates one the new linked server
> lacks the proper connection values and I'm unable to edit them.
> : (
> Randall Arnold
Hi Randall
You're right that the article says that you can use
sp_helplinkedsrvlogin to set the login info, but that's not correct.
If you look up sp_helplinkedsrvlogin in Books On Line, it says that it
"Provides information about login mappings defined against a specific
linked server used for distributed queries and remote stored procedures".
I'm not sure where it is you can't change the properties for the linked
server. I'm not familiar with this MS Access Linked Server wizard, but
you you use EnterpriseManager (SQL 2000) or Microsoft SQL Server
Management Studio (SQL2005) you can the possibility to manage linked
servers. In Enterprise Manager is under "Security" -> "Linked Servers".
In Management Studio you'll find it under "Server Object" ->" Linked
Servers".
There are also a number of stored procedures that can be used -
sp_linkedservers
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_droplinkedsrvlogin
sp_dropserver
You can look them up in Books On Line where you can find the syntax and
description.
Linked servers might not always be the easiest thing to work with, and
also I'm not quite sure if you can get all the info you're looking for
in this case.
Regards
Steen
Subscribe to:
Posts (Atom)