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 |
sql | SQL Server > sqlsrv | T-SQL >