UPDATE via OPENROWSET within Transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATE via OPENROWSET within Transaction

Author

Topic

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-10-18 : 11:05:19

Hi all. I'm trying to execute the following code. The code works fine, until I try and execute in within a transaction. When I do that, I get the error:

Msg 8524, Level 16, State 1, Line 2
The current transaction could not be exported to the remote provider. It has been rolled back.

I presume the problem is that I'm making a change to an excel sheet, within a transaction, and should the transaction be rolled back, the provider will be unable to rollback the changes to the sheet. Ironically, I really don't care if the changes to the sheet get rolled back, it's the changes to the database I'm interested in.

I can't commit the transaction first, because the stored procedure this code resides in will be called from within another transaction.

Anyone got any ideas on a different way to write to an excel sheet from within a transaction?

Here's the code:

        SET @SQL = '
        INSERT INTO OPENROWSET (''Microsoft.Jet.OLEDB.4.0'', 
                ''Excel 8.0;Database=' + @ReportLocation + @ReportFile + ';'', 
                ''SELECT * FROM [Errors$]'')
        SELECT * FROM #tmpErrors'
        EXECUTE sp_executeSql @SQL

PS. TC, notice the last line of my code? :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 08:26:43

Try creating a linked server & then use OPENQUERY.

PBUH

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-10-19 : 16:52:26

quote:
Originally posted by Sachin.Nand

Try creating a linked server & then use OPENQUERY.

PBUH

Given the number of excel sheets this procedure will create, that's really not an option. Unless you're suggesting I create the link on the fly, populate the table, then remove the link? I wouldn't have thought that necessary.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources