OPENROWSET in 2012 vs 2008: rollback or not

  • Hello everyone,

    This is Anibal from Taiwan,

    I found something interesting, but not sure if this is correct.

    So I post my article here, and please tell me if it is correct or not...

    ==========================================================

    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, because the record in II is updated back to 1 in openquery and not been rollbacked

  • http://www.aisowhat.com/sql-server/sql-t-sql/tsql-openrowset-in-2012-vs-2008/openrowset-advanced-openrowset

    Concept to create a proc & exec the proc in an in-line table function!!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply