December 27, 2016 at 2:46 am
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
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)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy