July 11, 2007 at 10:47 am
Hello,
I found sample in msn that allows Dynamic Execution with OpenQuery http://support.microsoft.com/kb/270119 but in my case it does not work.
Error:OLE DB provider "SQLNCLI" for linked server "cunet" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1 The operation could not be performed because OLE DB
provider "SQLNCLI" for linked server "cunet" was unable to begin a distributed transaction.
begin
tran
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
declare @cmd varchar(2500),@ID varchar(100)
DECLARE Pickup_cursor CURSOR
FOR SELECT distinct id FROM table where PickUp=1
OPEN Pickup_cursor
FETCH NEXT FROM Pickup_cursor
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = "update openquery(remotesvr,'select ID from Table1
where ID =''" + @ID + "'' ' ) Set Picked=1"
FETCH NEXT FROM Pickup_cursor INTO @id
END
CLOSE Pickup_cursor
DEALLOCATE Pickup_cursor
exec ( @cmd )
commit tran
July 11, 2007 at 12:09 pm
your query references "remotesvr" - how is that defined? The name is "cunet", no? If it's not fully qualified, that could explain. Also, if there's more than one record, you will only pick up the last record found, which suggests there may be a flaw in your logic unless you guarantee only one record in the cursor. In which case, why create a cursor to begin with?
IF there are no records in the cursor, do you get a differnt error message?
July 11, 2007 at 12:20 pm
You are getting the error because SQL Server 2005 is more ANSI SQL compliant FETCH per ANSI SQL definition is an implicit transaction and 2005 implemented it. There is a database setting you can use to enable implict transaction but that may not cover the distributed transaction part because for that you need MSDTC. That is something most companies would not want you to run. Hope this helps.
Kind regards,
Gift Peddie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply