2

A job, in which a proc is reading excel file using openrowset function and then exporting a file using bcp command. It was working perfectly until recently. When I run job it gives me this error:-

NT AUTHORITY\LOCAL SERVICE. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". [SQLSTATE 42000] (Error 7303) Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Error 15457) Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Error 15457) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412). The step failed.

Here is stored procedure code:-

ALTER PROCEDURE [dbo].[Read_Excel]
     @ExcelFilePath varchar(500)
    ,@OutPutFilePath nvarchar(500)
    ,@ServerName nvarchar(500)
    ,@DatabaseName nvarchar(100)
    ,@UserName nvarchar(50)
    ,@Password nvarchar(50)
    ,@Delimiter char(1)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Query nvarchar(1000)
    DECLARE @Cmd varchar(1000)
    DECLARE @FileExists int

    -- Check File Existence
    EXEC master..xp_fileexist @ExcelFilePath, @FileExists OUTPUT --returns 1 if exists, 0 if file is not there
    if @FileExists <> 1
        BEGIN PRINT 'There is no excel file available: ' + @ExcelFilePath RETURN END

    -- Allow Ad hoc Distributed Queries in order to run OpenRowset Function
    EXEC SP_CONFIGURE 'show advanced options', 1
    RECONFIGURE
    EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
    RECONFIGURE

    -- Clear tbl_excel Table
    TRUNCATE TABLE tbl_Excel
    --Read EXCEL File using OPENROWSET Function 
    SET @Cmd = 'INSERT INTO tbl_Excel
                SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database=' + @ExcelFilePath + ''',
                ''SELECT * FROM [Sheet1$]'')'
    EXEC(@Cmd)
    -- Allow Ad hoc Distributed Queries in order to run OpenRowset Function
    EXEC SP_CONFIGURE 'show advanced options', 1
    RECONFIGURE
    EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 0
    RECONFIGURE

    --Query View
    SET @Query = 'SELECT id1, name1, name2, address1, address2 FROM [' + @DatabaseName + '].[dbo].[tbl_Excel]'
    SET @Cmd = 'bcp "' + @Query + '" queryout "' +  @OutPutFilePath +
                 '" -c -S' + @ServerName + ' -U' + @UserName + ' -P' + 
                @Password + ' -t' + @Delimiter + ''
    EXEC master..xp_cmdshell @Cmd
END

Thanks in advance.

User13839404
  • 1,803
  • 12
  • 37
  • 46
  • Looks to me like you were either dynamically populating the Linked Server name, nd it's failed to be passed to the Proc - or, the Linked server has ceased to be linked? – Widor Oct 18 '11 at 15:32
  • Widor, check the proc, if that gives any clue – User13839404 Oct 18 '11 at 15:36
  • 3
    Holly crap. I just changed the SQL Server Agent Service Account to NetworkService from localservice. And it worked. – User13839404 Oct 18 '11 at 15:42
  • Oh well, post that as an answer to your question in that case. – Widor Oct 18 '11 at 15:53
  • Be aware that you can't use Jet if your SQL Server is running as a 64-bit process. That doesn't seem to be the case for this particular error, though. – Simon Oct 18 '11 at 16:13

1 Answers1

0

Have you tried to change SQL Server Agent Log on? (Services->SQL Server Agent, right click Log On) to your user account? or try to not use JET instead use ACE

SET @Cmd = 'INSERT INTO tbl_Excel
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;HDR=YES;IMEX=1; 'Database=' + @ExcelFilePath + ', 'SELECT * FROM [Sheet1$]')'
Edvinas
  • 1
  • 3