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