SQL Server 2014 - Error 15281 on Linked Excel File

Question

I'm upgrading our reporting server from 2008 R2 32-bit to 2014 32-bit.

We have linked servers that are excel files. These are .xls files (although the problem persists when they are updated to .xlsm). The connects work fine (select & insert into); however, when we run a stored procedure to create a new tab in the excel file.

USE [SupplementaryReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spExecute_ADODB_SQL] 
    @DDL VARCHAR(8000), 
    @DataSource VARCHAR(500), 
    @Worksheet VARCHAR(200)=NULL, 
    @ConnectionString VARCHAR(255) = 'Provider=Microsoft.ACE.OLEDB.12.0; 
    Data Source=%DataSource; 
        Extended Properties=Excel 12.0' 
        AS 
        DECLARE 
            @objExcel INT, 
            @hr INT, 
            @command VARCHAR(255), 
            @strErrorMessage VARCHAR(255), 
            @objErrorObject INT, 
            @objConnection INT, 
            @bucket INT 

SELECT @ConnectionString =REPLACE (@ConnectionString, '%DataSource', @DataSource) 
IF @Worksheet IS NOT NULL 
    SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet) 
    SELECT @strErrorMessage='Making ADODB connection ', 
        @objErrorObject=NULL 
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT 
IF @hr=0 
    SELECT @strErrorMessage='Assigning ConnectionString property "' 
        + @ConnectionString + '"', 
        @objErrorObject=@objconnection 
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection, 'ConnectionString', @ConnectionString 
IF @hr=0 SELECT @strErrorMessage ='Opening Connection to XLS, for file Create or Append' 
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open' 
IF @hr=0 SELECT @strErrorMessage ='Executing DDL "'+@DDL+'"' 
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute', 
    @Bucket out , @DDL 
IF @hr<>0 
    BEGIN 
    DECLARE 
        @Source VARCHAR(255), 
        @Description VARCHAR(255), 
        @Helpfile VARCHAR(255), 
        @HelpID INT 

    EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output, 
        @Description output,@Helpfile output,@HelpID output 
    SELECT @strErrorMessage='Error whilst ' 
        +COALESCE(@strErrorMessage,'doing something')+', ' 
        +COALESCE(@Description,'') 
        RAISERROR (@strErrorMessage,16,1) 
END 
EXEC @hr=sp_OADestroy @objconnection 

GO

I get the following error

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 116

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 116

SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

This error often means that ad hoc distribtued queries are not enough. But they are (both 'show advanced options' and 'ad hoc distributed queries' are set to 1). We also have the provider Microsoft.ACE.OLEDB.12.0 with allow in process and dynamic paramteres enabled.

The excel files work perfectly with 2008 R2. The code itself hasn't been changed. All that's changed is upgrading to 2014. Does anyone have any suggestions? You know... besides not using SQL to connect to excel files since we don't have the resources to change that.

Much obliged!

David Rushton David Rushton · Accepted Answer · 2016-12-14T16:06:29

The most likely answer is Ole Automation Procedures is not turned on. You can check by running:

EXEC sp_configure 'Ole Automation Procedures';

This will return something along the lines of:

name                        minimum maximum config_value    run_value
Ole Automation Procedures   0       1       0               0

If the config_value is 0 you need to enable it.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

Source.

SQL Server 2014 - Error 15281 on Linked Excel File

1 Answers