Missing fields in sql server linked tables

avoorheis
avoorheis used Ask the Experts™
on
Guess I've never needed the fields that are missing. Seems that they are all keys or indexes. The account, through which the connection is made is read only.
Is that just the way it is?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Guess I've never needed the fields that are missing. Seems that they are all keys or indexes. The account, through which the connection is made is read only.
Is that just the way it is? >>

  Way it is.  You won't see design changes on the SQL side until you either delete and re-link the table or refresh the existing table link (use linked table manager under database/tools or do it in code).

JimD.

Commented:
You can also hit F5 while in the database window - this will update the references in the front end

Author

Commented:
hmmm, there hasn't been any changes on the sql side since I've linked the table. It's that the primary key is just not showing at all in the linked table.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<hmmm, there hasn't been any changes on the sql side since I've linked the table. It's that the primary key is just not showing at all in the linked table. >>

  I just re-read your question given your response and see that I missed the point of the question.

  I can't understand why that would be the case, even with read-only on the tables.  I would try deleting the link and re-linking and see if the PK field shows up.

JimD.
I figured it out. We are using custom data types for our primary and foreign key fields to make sure they are all exactly the same. Custom data types, apparently, are not viewable to linked tables for permission other than owner, unless it has been specifically granted, something like:
grant execute on type::[dbo].[data_type_name] to public;
to see a list of user defined data type use:
select * from sys.types
where is_user_defined = 1

Author

Commented:
found the answer myself

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial