sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | OPENROWSET in 2012 vs 2008

If you create a stored procedure and with DML/DDL in it, not only select,
at the beginning of an OPENROWSET query that you use the stored procedure,
you should put a "commit" to make sure every change wont be rollback.


------------- DEMO SP -------------
CREATE TABLE II(
ID INT PRIMARY KEY)
INSERT INTO II (ID) VALUES (1)

CREATE PROC PPP
@ID INT = 1
AS
BEGIN
SET NOCOUNT ON; SET FMTONLY OFF;
MERGE II AS DEST USING (SELECT 1 A) AS SRC ON DEST.ID = DEST.ID 
WHEN MATCHED THEN UPDATE SET DEST.ID = @ID; 

--You can use update instead of merge of course!!
-- In the fact I use the $action variable here, but it's much complex, so I remove it.

END

CREATE PROC WRAPPER
@ID INT = 1
AS
BEGIN
SET NOCOUNT ON; SET FMTONLY OFF;
declare @P1 int
DECLARE @EX NVARCHAR(MAX) = N'EXEC PPP ' + CAST (@ID AS NVARCHAR)
exec sp_prepare @P1 output, NULL, @EX, 1
exec sp_execute @P1
END


------------- works  in SQL Server 2008 R2 -------------

SET NOCOUNT ON
SET FMTONLY OFF
SET NO_BROWSETABLE OFF 
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=MASTER',
'SET NOCOUNT ON; SET FMTONLY OFF;
EXEC MASTER.DBO.WRAPPER 2
SELECT 1 B
')
GO

SELECT * FROM II
GO

-- the result would be 2, because the record in II is updated to 2 in openquery
-- but this wont work in SQL Server 2012
-- If you use a $action variable to view the merge result, 
-- there has happened update operation but there's still 1 in the table II,
-- no update succeed.

------------- works  in SQL Server 2012  -------------

SET NOCOUNT ON
SET FMTONLY OFF
SET NO_BROWSETABLE OFF 
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=MASTER',
'SET NOCOUNT ON; SET FMTONLY OFF; SET XACT_ABORT OFF
COMMIT
EXEC MASTER.DBO.WRAPPER 1 WITH RESULT SETS NONE;
SELECT 1 B
')
GO

SELECT * FROM II
GO

-- the result would be back to 1 now, because the record in II is updated back to 1 in openquery and not been rollbacked


Comments