Generate Excel Files in MsSQL 2008R2

• iashurova

  Ten Centuries

  Points: 1032

  More actions

  January 24, 2013 at 1:15 pm

  #269723

  Hi All,

  I am trying to automate Excel file generation through either SSIS or T-SQL code directly.

  Unfortunately my data source for Excel always have different number of fields and fieldnames.

  It is simply a stored procedure execution result.

  In SSIS `Excel file destination` requires set number of fields and types.

  So I had looked and using JET and Openrowset, see below:

  insert into openrowset

  (

  'Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=C:\\template.xls',

  'select * from [Sheet1$]'

  )

  select 'blabla', 'USA', 4;

  this fails with: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

  I have to mention that MsSQL R2 installation is a cluster one and JET simply does not exists in 64 bit flavor.

  I am looking for a working solution.

  OpenXML document format and CLR code is the option that I have in mind.

  But, Do I really have to go that route ???

  Thanks.

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  January 25, 2013 at 12:52 am

  #1580849

  Maybe you can use the ACE OLE DB provider, this one is available in 64-bit.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP