Post reply

OpenDataSource query

  • December 27, 2016 at 2:46 am

    #333329

    INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=D:\Book1.xls;Extended Properties=Excel 12.0')... [sheet1$]

    SELECT * FROM dbo.tbl_webform

    The above query is to export data from SQL table to spreadsheet which is already created and this query is working fine.. But when I execute the query like below, I am getting an error: Msg 7390, Level 16, State 2, Line 74

    The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

    INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=D:\Book1.xls;Extended Properties=Excel 12.0')... [sheet1$]

    EXEC dbo.usp_test

    The procedure usp_test is nothing but the Sproc for single SELECT query

    CREATE PROCEDURE [dbo].[usp_test]

    AS

    BEGIN

    SELECT * FROM dbo.tbl_webform

    END

    Anyone please help me how to solve this error..

  • March 7, 2017 at 1:42 pm

    #1932166

    I'm not sure the ACE drivers are going to support executing a sproc.  Given that it's a simple select, using a sproc seems totally unnecessary.  If it were a more complex sproc, I'd probably insert the output of the sproc into a temp table and select from that for the insert.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 24, 2024 at 2:59 pm

    This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply