[SQL]透過OPENROWSET新增SQL SERVER資料的問題

2013-01-21

4978
0
SQL

在網路上看有人在討論「openrowset 執行SP出現的奇怪問題」，測試資料如下，


CREATE table test ( id int identity(1,1), StrA nvarchar(100), nowtime datetime )
GO 
CREATE PROC usp_test @val nvarchar(50)=NULL AS 
--新增資料
INSERT INTO test (StrA,nowtime) VALUES ( @val,getdate());
--將資料SELECT出來
SELECT * FROM test;
GO

使用OPENROWSET之中請先設定「Ad Hoc Distributed Queries」值為1，如下，


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

不然會有「Msg 15281」的錯誤哦!

實際測試了一下，


SELECT * from openrowset('SQLNCLI11','server=.;uid=sa;pwd=SAPWD;database=ASPNETDB;','SET NOCOUNT ON;SET FMTONLY OFF; EXEC usp_test ''hello'' ' );
SELECT * FROM test;

資料真的無法新增進去，在SP中資料確實有新增，IDENTITY_FIELD欄位值也有增加，但外面去SELECT資料表，卻是沒有資料。

這表示資料應該是有被新增進去，最後被ROLLBACK。

透過SQL PROFILER來看，Transaction ID欄位是有資料的，如下圖所示，

再來，在SP中加入以下的SQL,

SELECT XACT_STATE() AS xstate, @@TRANCOUNT AS txcnt;

回傳值都是1，表示在交易之中。

所以筆者判斷，應該在OPENROWSET中，如果沒有加COMMIT的話，就會自動ROLLBACK。

所以要解這個問題的話，就在最後判斷，如果有在交易之中，就COMMIT這筆交易，如下，


ALTER PROC [dbo].[usp_test] @val nvarchar(50)=NULL AS 
INSERT INTO test (StrA,nowtime) VALUES ( @val,getdate());
SELECT * FROM test;
IF XACT_STATE() = 1  
BEGIN
	COMMIT;
END
GO

那是只有透過SP才有這種問題嗎? 筆者直接下INSERT後再SELECT資料出來，還是一樣的問題，如下，


SELECT * from openrowset('SQLOLEDB','server=RM-NB;Trusted_Connection=yes;database=ASPNETDB;','SET FMTONLY OFF;SET NOCOUNT ON;INSERT INTO dbo.test( StrA, nowtime ) VALUES  ( N''ASBC'', GETDATE() ); SELECT * FROM test; ' );
SELECT * FROM test;

所以還是要加入COMMIT(IF XACT_STATE() = 1 COMMIT;) 才可以哦! 如下，


SELECT * from openrowset('SQLOLEDB','server=RM-NB;Trusted_Connection=yes;database=ASPNETDB;','SET FMTONLY OFF;SET NOCOUNT ON;INSERT INTO dbo.test( StrA, nowtime ) VALUES  ( N''ASBC'', GETDATE() ); SELECT * FROM test; IF XACT_STATE() = 1 COMMIT;' );
SELECT * FROM test;