OpenDataSource query

• RAKSHITHA

  SSChasing Mays

  Points: 602

  More actions

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

• sgmunson

  SSC Guru

  Points: 110647

  More actions

  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)