Help getting list of tabs (Tables) in Excel. sp_tables_ex returning no results.

  • Below is what I have run. I can query the workbook if I already know the sheet name. And insert into a sql table.

    This will be done with tsql not ssis. The sheet names may vary as well as quantity so I need to be able to query them in order to build a dynamic range.
     

             EXEC sp_addLinkedServer
                @server= N'ImportTestLinkedServer',
                @srvproduct = N'Excel',
                @provider = N'Microsoft.ACE.OLEDB.12.0',
                @datasrc = N'g:\UnZip\importsamplexls.xlsx',
                @provstr = N'Excel 12.0; HDR=YES';
    GO

    exec sp_tables_ex ImportTestLinkedServer
    exec sp_columns_ex ImportTestLinkedServer

    select * from sys.servers where is_linked = 1

    The image shows the results.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Does anything show up when you expand the linked server in SSMS? Any tables under default?

    Sue

  • Sue_H - Wednesday, December 13, 2017 12:27 PM

    Does anything show up when you expand the linked server in SSMS? Any tables under default?

    Sue

    Well that is a good question. I did not know that you can expand the linked server view. that said I am not clear on what you are asking me. Can you give me an example of how you expand the view of the linked server?

    I am only aware of ( select * from sys.servers where is_linked = 1)

    I am trying to view the tables (Tabs) and columns (Cells) by way of:
    exec sp_tables_ex ImportTestLinkedServer
    exec sp_columns_ex ImportTestLinkedServer

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Jeffery Williams - Wednesday, December 13, 2017 12:34 PM

    Sue_H - Wednesday, December 13, 2017 12:27 PM

    Does anything show up when you expand the linked server in SSMS? Any tables under default?

    Sue

    Well that is a good question. I did not know that you can expand the linked server view. that said I am not clear on what you are asking me. Can you give me an example of how you expand the view of the linked server?

    I am only aware of ( select * from sys.servers where is_linked = 1)

    I am trying to view the tables (Tabs) and columns (Cells) by way of:
    exec sp_tables_ex ImportTestLinkedServer
    exec sp_columns_ex ImportTestLinkedServer

    Brain fart sorry.. No actually. Odd..

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

  • Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Jeffery Williams - Wednesday, December 13, 2017 1:09 PM

    Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    And I installed 64 bit.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Jeffery Williams - Wednesday, December 13, 2017 1:18 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:09 PM

    Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    And I installed 64 bit.

    If everything is configured correctly and G: is a local drive to the server with permissions set, then I have no idea why it's not working.

    Sue

  • Sue_H - Wednesday, December 13, 2017 1:24 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:18 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:09 PM

    Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    And I installed 64 bit.

    If everything is configured correctly and G: is a local drive to the server with permissions set, then I have no idea why it's not working.

    Sue

    G is a local connected drive with wide open permissions. 

    Thank you Sue for trying to help. Yeah I tried everything and Googled like mad trying to figure it out. And I CAN query the workbook and insert sheet data into sql, providing I know the name of the sheet. This is strange.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Jeffery Williams - Wednesday, December 13, 2017 1:26 PM

    Sue_H - Wednesday, December 13, 2017 1:24 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:18 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:09 PM

    Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    And I installed 64 bit.

    If everything is configured correctly and G: is a local drive to the server with permissions set, then I have no idea why it's not working.

    Sue

    G is a local connected drive with wide open permissions. 

    Thank you Sue for trying to help. Yeah I tried everything and Googled like mad trying to figure it out. And I CAN query the workbook and insert sheet data into sql, providing I know the name of the sheet. This is strange.

    Yeah...that is very odd. I am guessing you tried creating another linked server and got the same results - saw the other one in your screen shot. Maybe try a linked server with a different Excel file to see if it's something weird with the file itself.
    These kind of things drive me nuts. Please post back if you get it figured out - I'd really like to know what caused it.

    Sue

  • Sue_H - Wednesday, December 13, 2017 1:53 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:26 PM

    Sue_H - Wednesday, December 13, 2017 1:24 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:18 PM

    Jeffery Williams - Wednesday, December 13, 2017 1:09 PM

    Sue_H - Wednesday, December 13, 2017 1:04 PM

    For the provider, have you enabled Dynamic Parameter and Allow in Process?
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    The other thing to check is if you install the correct version of the driver as in 32 bit vs 64 bit

    Sue

    Did that. In fact here is what I ran:

    USE [MSDB]
    GO

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    GO

    USE [master]
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO

    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    EXEC sp_addLinkedServer
      @server= N'XLSX_2010',
      @srvproduct = N'Excel',
      @provider = N'Microsoft.ACE.OLEDB.12.0',
      @datasrc = N'g:\UnZip\importsamplexls.xlsx',
      @provstr = N'Excel 12.0; HDR=Yes';
    GO

    And I installed 64 bit.

    If everything is configured correctly and G: is a local drive to the server with permissions set, then I have no idea why it's not working.

    Sue

    G is a local connected drive with wide open permissions. 

    Thank you Sue for trying to help. Yeah I tried everything and Googled like mad trying to figure it out. And I CAN query the workbook and insert sheet data into sql, providing I know the name of the sheet. This is strange.

    Yeah...that is very odd. I am guessing you tried creating another linked server and got the same results - saw the other one in your screen shot. Maybe try a linked server with a different Excel file to see if it's something weird with the file itself.
    These kind of things drive me nuts. Please post back if you get it figured out - I'd really like to know what caused it.

    Sue

    I will try that and update here.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

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

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