Can't edit Linked Server General Properties after saving

[Crazy Cat]

Hi,

When I click on the properties of a linked server, all the General
properties are read - only, which means that if I want to edit any
general properties I have to delete the linked server and recreate it.
Needless to say this is a pain. Is this by design or some kind of bug?
Is there a workaround?

Thanks

[Crazy Cat]

Oops, forgot to mention SQL Server 2005 Express Edition -- sorry, my
brain is kind of fried messing with this thing

Thanks

[Crazy Cat]

OK, I need to be more specific still,

When I click on the properties of a linked server, all the General
properties are read - only, which means that if I want to edit any
general properties I have to delete the linked server and recreate it.
Needless to say this is a pain. Is this by design or some kind of bug?
Is there a workaround?

I'm using SQL Server 2005 Management Studio Express CTP, the latest
edition with SQL Server 2005 Express.

Thanks

[Razvan Socol]

Hello, Crazy Cat

Considering that this behaviour was also in Enterprise Manager (for SQL
Server 2000), I'd say that it is "by design". And seeing that the
relevant procedures are only sp_addlinkedserver and
sp_droplinkedserver, I'd say that there isn't any workaround (except
the one that you mentioned: drop the linked server and recreate it).

However, it is interesting to notice that for certain OLEDB Providers,
some properties are not read-only in the General tab (for example, for
the "Microsoft Jet 4.0 OLE DB Provider", the "Product name", "Data
source" and "Provider String" can be modified). Management Studio (and
Enterprise Manager) accomplishes this by directly updating the
sysservers system table. However, I do not recommend trying to update
the system tables yourself. This is unsupported and if they didn't do
it in Management Studio, there are very good reasons why this should
not be done.

Razvan

[Razvan Socol]

After looking at this thing a little bit more, I noticed that the
"Product name", "Data
source", etc properties can always be modified for any OLE DB Provider,
including "Microsoft OLE DB Provider for SQL Server". So, if it's
important for you to modify those properties (without dropping and
recreating the linked server), you shoud try creating a linked server
using this OLE DB Provider (select "Other data source", instead of "SQL
Server"). However, I guess that there may be some other differences
between the two methods.

Razvan

[Crazy Cat]

Thanks Razvan,

However from what I'm seeing it is not the case that I can modify the
General properties for an OLE DB provider -- my linked server uses the
OLE DB Provider for Oracle. I also experienced the same frustration
with the OLE DB provider for ODBC sources. As soon as I exit the Linked
Server dialog after creating the Linked Server the General Properties
become read-only. Guess it's something I'll just have to live with.

Thanks again

[Razvan Socol]

Hmm... you are right. Earlier, I tried using Management Studio Express
(Nov CTP) on a SQL Server 2000 server and I was able to modify the
properties of a linked server, if it is using a OLE DB Provider
(including the OLE DB Provider for Oracle and for ODBC Drivers).
However, if I connect to a server running SQL Server 2005, the
properties are read-only.

Razvan

[Crazy Cat]

Hi All,

I came up with a workaround -- if you right click on the link server
then select 'Script Linked Server As/ CREATE to/ New Query Window'
Management Studio will generate the code for creating the linked server
in a Query Window. You can then edit the code to change the desired
properties, delete the existing Linked Server, then execute the code to
recreate the linked server. If you then click the refresh selection for
the linked server folder's popup menu you will then see your newly
edited linked server. I found that this pretty much served the same
need as some of the features I'm surprised are missing from Management
Studio, such as an option to copy and paste a linked server or edit the
general properties. Nonetheless, I found that doing it this way is just
as popular, though not as obvious.

Thanks all

[Erland Sommarskog]

Razvan Socol (rs****@gmail.com) writes:

Hmm... you are right. Earlier, I tried using Management Studio Express
(Nov CTP) on a SQL Server 2000 server and I was able to modify the
properties of a linked server, if it is using a OLE DB Provider
(including the OLE DB Provider for Oracle and for ODBC Drivers).
However, if I connect to a server running SQL Server 2005, the
properties are read-only.

That sonds reasonable. I got a bit puzzled when you talked about direct
update to the syservers table, as in SQL 2005, there are no system tables
visible for direct update - sysservers and all the others are now views
only.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx