openquery apears to be rolled back when done

Question

I'm using the following query.

select * from OPENQUERY(EXITWEB,N'SET NOCOUNT ON;
                       declare @result table (id int);
                           insert into [system_files] ([is_public], [file_name], [file_size], [content_type], [disk_name], [updated_at], [created_at])
                    output inserted.id into @result(id)
                    values (N''1'',N''7349.jpg'',N''146921'',N''image/jpeg'',N''5799dcc8a1eb1413195192.jpg'',N''2016-07-28 10:22:00.000'',N''2016-07-28 10:22:00.000'')

                       declare @id int = (select top 1 id from @result)
                       select * from system_files where id = @id
                       insert into linkToExternal (id, id_ext) values(@id, 47)
                       --select @id
                       ')

when I perform a select from within the query it works just fine:

But when I go to check my database when the call has finished, the record is no longer there.

So I'm suspecting a transaction is rolled back. My question is: why. What can I do to prevent the transaction to be rolled back if that's the case.

score 4 · Accepted Answer · answered Jul 28 '16 at 10:43

Well, as always, after days of struggling and me post a question on stackoverflow I find the solution: http://www.sqlservercentral.com/Forums/Topic1128997-391-1.aspx#bm1288825

I was having the same problem as you and almost gave up on it but have finally found an answer to the problem. Reading an article about sharing data between stored procedures I discovered that OPENQUERY issues an Implicit Transaction and that it was Rolling back my insert. So I had to add an explicit Commit to my stored procedures, in additional I discovered that if I use it in a query that has a Union it has to be Commited twice. Since I'm doing my insert inside a BEGIN TRY I can always just commit twice and not worry about whether it is being used in a UNION. I'm returning different values if there is an error but that was just apart of my debugging.

SELECT TOP 5 *
FROM mm
JOIN OPENQUERY([LOCALSERVER], 'EXEC cms60.dbo.sp_RecordReportLastRun ''LPS'', ''Test''') RptStats ON 1=1


ALTER PROCEDURE [dbo].[sp_RecordReportLastRun] 
-- Add the parameters for the stored procedure here
@LibraryName varchar(50),
@ReportName varchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY
INSERT INTO cms60.dbo.ReportStatistics (LibraryName, ReportName, RunDate) VALUES (@LibraryName, @ReportName, GETDATE())
--
COMMIT; --Needed because OPENQUERY starts an Implicit Transaction but doesn't commit it.    
COMMIT; --Need second Commit when used in a UNION and although it throws an error when not used in a UNION doesn't cause a problem.
END TRY
BEGIN CATCH
SELECT 2 Test
END CATCH
SELECT 1 Test 
END

In my case, adding a ;COMMIT; after the inserts solved it, and made sure it got written into the database.

openquery apears to be rolled back when done

1 Answers1