Unable to view list of tables of a linked server

Question

Unable to view list of tables of a linked server

Pyman

SSChampion

Points: 13830
More actions
April 20, 2012 at 8:13 am

#255221

Hello Everyone,
Hope all is well.
I was able to setup a linked server between sql server 2008 R2 and a mas90 database and I am able to pull records out of an AR_Customer table. But the issue is I am not able to see a list of tables in the mas 90 database. If I am able to see the tables I need to find out which table a specific column belongs to based on my application.
Can you please help.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Is there system documentation? I am not sure what you mean by you can't see the tables. I have a feeling you are talking about intellisense. Intellisense does not work through a linkserver. You have to know what tables and columns you need to reference.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Pyman SSChampion Points: 13830 More actions · Answer 2

Sean Lange (4/20/2012)
Is there system documentation? I am not sure what you mean by you can't see the tables. I have a feeling you are talking about intellisense. Intellisense does not work through a linkserver. You have to know what tables and columns you need to reference.

No..I want to view the tables(metadata) of the mas90 database as a list under the LinkedServers ...Like in my SSMS I want to SQLServer>ServerObjects>Linked Servers>MyLinkServer>Catalogs>should see the list of tables of this linked server database.

If I am still not clear please check the image attached.

Thanks for your help

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 3

Sapan i thought that the catalog gets populated with only the tables/objects of the default database assigned to the login you are using to connect with...so for example if my login has a default database of master, i see hardly anything, just the tables in master, but my login would be able to select * from myLinkedserver.ProductionDB.sys.tables, for example, to query one of several databases my login has access to.

so your option is to either change the login 's default database, or directly query metadata like mylinkedserver.Databasename.information_schema.tables or the sys.objects stuff like my example above

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

I don't think you can if the target is not sql server. I don't have any link servers setup to a non sql database so I can't confirm that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 5

i thought the catalog view he's refering to in the object Explorer returns the results of sp_tables_ex linkedservername , so whether the linked server is SQL Server, Oracle, mysql, a folder of text files, etc, yoiu get whatever that proc returns, which might not be every object you have access to, just the results of the proc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 6

Lowell (4/20/2012)
i thought the catalog view he's refering to in the object Explorer returns the results of sp_tables_ex linkedservername , so whether the linked server is SQL Server, Oracle, mysql, a folder of text files, etc, yoiu get whatever that proc returns, which might not be every object you have access to, just the results of the proc.

The exec sp_tables_ex LINK1 returned 0 records

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Pyman SSChampion Points: 13830 More actions · Answer 7

Lowell (4/20/2012)
Sapan i thought that the catalog gets populated with only the tables/objects of the default database assigned to the login you are using to connect with...so for example if my login has a default database of master, i see hardly anything, just the tables in master, but my login would be able to select * from myLinkedserver.ProductionDB.sys.tables, for example, to query one of several databases my login has access to.
so your option is to either change the login 's default database, or directly query metadata like mylinkedserver.Databasename.information_schema.tables or the sys.objects stuff like my example above

The login I am using to connect to linked server is sql server authentication login with sysadmin permissions on my sql server 2008 R2 and has the highest level of privileges in the mas90 database

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 8

Sapen (4/20/2012)
Lowell (4/20/2012)
i thought the catalog view he's refering to in the object Explorer returns the results of sp_tables_ex linkedservername , so whether the linked server is SQL Server, Oracle, mysql, a folder of text files, etc, yoiu get whatever that proc returns, which might not be every object you have access to, just the results of the proc.
The exec sp_tables_ex LINK1 returned 0 records

exactly my point. the same proc is used to populate the catalog view you posted the screenshot of.

so if the ODBC driver does not return any results, that is the reason you don't see results in the object explorer.;

there's a few articles out there under "mas90 linked server" that might help;

this is the first i thought was interesting;

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/05/10/connecting-to-a-sage-mas-erp-90-database-as-a-linked-server-within-ms-sql-server-2008.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 9

Lowell (4/20/2012)
Sapen (4/20/2012)
Lowell (4/20/2012)
i thought the catalog view he's refering to in the object Explorer returns the results of sp_tables_ex linkedservername , so whether the linked server is SQL Server, Oracle, mysql, a folder of text files, etc, yoiu get whatever that proc returns, which might not be every object you have access to, just the results of the proc.
The exec sp_tables_ex LINK1 returned 0 records
exactly my point. the same proc is used to populate the catalog view you posted the screenshot of.
so if the ODBC driver does not return any results, that is the reason you don't see results in the object explorer.;
there's a few articles out there under "mas90 linked server" that might help;
this is the first i thought was interesting;
https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/05/10/connecting-to-a-sage-mas-erp-90-database-as-a-linked-server-within-ms-sql-server-2008.aspx

Thanks for the reply Lowell..the screen shots in the url however shows the tables of the mas90...I followed the same steps mentioned in that url for setting up the linked server with the mas90 database. But I am able to query on a bunch of tables that I know of in this database but atleast I should see these tables. I dont get what I am missing.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams