OpenDataSource query

  • RAKSHITHA

    SSC-Addicted

    Points: 462

    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..

  • sgmunson

    SSC Guru

    Points: 110433

    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)
    ‌:) 🙂 🙂
    Health & Nutrition

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

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