Exporting to excel file with OPENROWSET

• jluis_hdezg

  SSC Enthusiast

  Points: 111

  More actions

  January 29, 2009 at 2:21 pm

  #128282

  This is the error I get when execute the sp:

  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

  [OLE/DB provider returned message: Unspecified error]

  OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

  This is my code:

  DECLARE @channel AS VARCHAR(10)

  DECLARE @feed AS VARCHAR(10)

  DECLARE @OriginalFile AS VARCHAR(100)

  DECLARE @FilePathName AS VARCHAR(100)

  DECLARE @VBSFilePathName AS VARCHAR(100)

  DECLARE @Msg AS VARCHAR(250)

  DECLARE @ReturnStatus INT

  DECLARE @DateRptAS VARCHAR(15)

  --Drop and Create temporal tables

  IF EXISTS (SELECT *

  FROM tempdb.dbo.sysobjects

  WHERE ID = object_id(N'tempdb.dbo.#TmpSpeIngest'))

  BEGIN

  DROP TABLE #TmpSpeIngest

  END

  CREATE TABLE #TmpSpeIngest (

  LogChannelNCHAR(15)NOT NULL,

  LogFeed NCHAR(20)NOT NULL,

  LogDate VARCHAR(50)NULL)

  --LogFiles Avaliable

  INSERT #TmpSpeIngest

  SELECT DISTINCT LogChannel, LogFeed, LogDate

  FROM tb_spe_ingest

  WHERE LogDate = @date

  ORDER BY LogChannel, LogFeed

  INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=C:\Program Files\Microsoft SQL Server\MSSQL\REPORTS\ReporteCombinado.xls',

  'SELECT * FROM [AvailableLogsForReport$]')SELECT * FROM #TmpSpeIngest

  Any help is greatly appreciated.

  Regards,

  JLHG

• jluis_hdezg

  SSC Enthusiast

  Points: 111

  More actions

  January 29, 2009 at 6:13 pm

  #935205

  Just to mention, this sp works for couple of times after restarting the service and then it stops working.

  I did follow some instructions from MS...site and it did made sense since I saw that it is writing to this local dir.. C:\TEMP

  http://support.microsoft.com/kb/814398

  Could anyone provide me some new guide to this issue.....?

  Regards,

  JLHG