Post reply

Linked Server Excel Sheet (tab) names

  • November 13, 2009 at 7:09 am

    #99959

    I create temporary linked servers to Excel Workbooks to gather data, but the hand-edited tab Names are not consistent. Is there a way to dynamically query the tab names? (SSMS can get and display them, can I from a query?)

    Alternately, is there a way to reference the tab I need to access by its ordinal position? (I always want the first tab.)

    Thanks!

  • November 13, 2009 at 7:25 am

    #1078916

    yes...

    exec sp_tables_ex LinkedServerName

    that will show all the sheets(tables) that exist in the Excel spreadsheet...or any other linked server for that matter. that retrieves the metadata from whatever linked server it is...whether text/access/excel/mysql/oracle/whatever.

    TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS

    NULL NULL Sheet1$ TABLE NULL

    NULL NULL Sheet2$ TABLE NULL

    NULL NULL Sheet3$ TABLE NULL

    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!

  • November 13, 2009 at 7:31 am

    #1078920

    Thanks very much Lowell -- exactly what I needed!

Viewing 3 posts - 1 through 2 (of 2 total)

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