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

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 12:13 pm

  #342055

  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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 13, 2017 at 12:27 pm

  #1972013

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

  Sue

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 12:34 pm

  #1972017

  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

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 12:39 pm

  #1972018

  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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 13, 2017 at 1:04 pm

  #1972023

  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
  

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 1:09 pm

  #1972024

  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

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 1:18 pm

  #1972025

  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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 13, 2017 at 1:24 pm

  #1972026

  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

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 1:26 pm

  #1972027

  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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 13, 2017 at 1:53 pm

  #1972029

  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

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  December 13, 2017 at 1:59 pm

  #1972031

  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