November 4, 2012 at 10:47 pm
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
November 5, 2012 at 12:02 am
Concept to create a proc & exec the proc in an in-line table function!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply