Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
varlo
Member

Откуда:
Сообщений: 82
Есть такой код

CREATE PROCEDURE ccccon.MakeMigration15
AS
	DECLARE @RecurringGroupID uniqueidentifier
	DECLARE @InitialMeetingID uniqueidentifier
	DECLARE @MeetingID uniqueidentifier
	DECLARE @MeetingSeriesId uniqueidentifier
	DECLARE @ParticipationRoleId uniqueidentifier
	DECLARE @MemberId uniqueidentifier
	DECLARE @MeetingParticipantId uniqueidentifier
	
	--select all RecurringGroups
	DECLARE RecurringGroups_Cursor CURSOR FOR
		SELECT RecurringGroupID,InitialMeetingID FROM ccccon.RecurringGroups
	OPEN RecurringGroups_Cursor
	FETCH NEXT FROM RecurringGroups_Cursor INTO @RecurringGroupID, @InitialMeetingID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		--select all meetings inside of recurring group order by time
		DECLARE RecurringGroupMeetingsXRef_Cursor CURSOR FOR
			SELECT m.MeetingID,ms.MeetingSeriesId FROM ccccon.RecurringGroupMeetingsXRef rgmr 
			INNER JOIN ccccon.Meetings m ON rgmr.MeetingID=m.MeetingID 
			INNER JOIN ccccon.MeetingSeries ms ON ms.MeetingID=m.MeetingID 
			WHERE RecurringGroupID=@RecurringGroupID AND m.Status <> 'Cancelled'
			ORDER BY ms.StartTime

			DECLARE @FirstMeetingID uniqueidentifier
			DECLARE @FirstParticipationRoleId uniqueidentifier
			DECLARE @FirstMemberId uniqueidentifier

			OPEN RecurringGroupMeetingsXRef_Cursor
			FETCH NEXT FROM RecurringGroupMeetingsXRef_Cursor INTO @MeetingID, @MeetingSeriesId
			SET @FirstMeetingID=@MeetingID --store first meeting id from group

			--assign all meetings series to the first group meeting
			UPDATE ccccon.MeetingSeries SET MeetingID=@FirstMeetingID WHERE MeetingID IN 
			(SELECT MeetingID FROM ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID=@RecurringGroupID)

			UPDATE ccccon.Pins SET MeetingID=@FirstMeetingID FROM ccccon.Pins p INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON p.MeetingID=rgmf.MeetingID
			WHERE p.MeetingID!=@FirstMeetingID AND RecurringGroupID=@RecurringGroupID

			--analyze first group meeting: store  ParticipationRoleId, MemberId for the first group meeting
			SELECT @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId 
			FROM ccccon.MeetingParticipants WHERE MeetingID=@FirstMeetingID

			IF @@ROWCOUNT > 1 -- there are some rows
				SELECT @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId 
				FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID AND ParticipationRoleId='221C26BB-44E0-430A-BF4D-CDA999FD3395' --Presenter
			ELSE --no presenter
				SELECT TOP 1 @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID --take the first Participant

			DECLARE @LeaveOnlyFirstMeetingParticipant BIT	
			SET @LeaveOnlyFirstMeetingParticipant=1

			--compare meetings inside of recurring group with the first group meeting
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--obtain the next meeting in the group
				FETCH NEXT FROM RecurringGroupMeetingsXRef_Cursor INTO @MeetingID,@MeetingSeriesId
				--analyze group meeting: store  ParticipationRoleId, MemberId and MeetingID for the group meeting
				SELECT @MeetingParticipantId=MeetingParticipantId, @ParticipationRoleId=ParticipationRoleId, @MemberId=MemberId 
				FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID
				--compare them with the first ones
				IF @MeetingParticipantId IS NOT NULL
				BEGIN
					IF @ParticipationRoleId!=@FirstParticipationRoleId OR @MemberId!=@FirstMemberId
					BEGIN
						--if they don't match then leave for 
						SET @LeaveOnlyFirstMeetingParticipant=0
						--transfer this records to MeetingSeriesParticipants
						INSERT ccccon.MeetingSeriesParticipants(MeetingSeriesId,MeetingParticipantId,Include) VALUES (@MeetingSeriesId,@MeetingParticipantId,1)
						--delete transfered record from MeetingParticipants 
						DELETE ccccon.MeetingParticipants WHERE MeetingParticipantId=@MeetingParticipantId
						--BREAK
					END
				END --IF @MeetingParticipantId IS NOT NULL
				--reassign all records from Notes to the first group meeting
				UPDATE ccccon.Notes SET MeetingID=@FirstMeetingID WHERE MeetingID=@MeetingID		
			END --WHILE @@FETCH_STATUS = 0 FOR RecurringGroupMeetingsXRef_Cursor
		  	--if they match then and set IncludeByDefault=Y for the first
			IF @LeaveOnlyFirstMeetingParticipant=1
			BEGIN
				--delete for all except the first
				DELETE ccccon.MeetingParticipants WHERE MeetingID!=@FirstMeetingID AND MeetingID IN 
				(SELECT m.MeetingID FROM ccccon.RecurringGroupMeetingsXRef rgmr 
				INNER JOIN ccccon.Meetings m ON rgmr.MeetingID=m.MeetingID 
				WHERE RecurringGroupID=@RecurringGroupID)
				--set IncludeByDefault=Y for the first
				UPDATE ccccon.MeetingParticipants SET IncludeByDefault =1 WHERE MeetingID=@FirstMeetingID
			END

			CLOSE RecurringGroupMeetingsXRef_Cursor
			DEALLOCATE RecurringGroupMeetingsXRef_Cursor

		--delete template meeting
		DELETE ccccon.Pins WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
		DELETE ccccon.MeetingParticipants WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
		DELETE ccccon.MeetingSeries WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
		DELETE ccccon.Meetings WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
		
		UPDATE ccccon.MeetingParticipants SET MeetingID=@FirstMeetingID WHERE MeetingID IN 
		(SELECT MeetingID FROM ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID=@RecurringGroupID)

		DELETE ccccon.Meetings FROM ccccon.Meetings m INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON m.MeetingID=rgmf.MeetingID
		WHERE m.MeetingID!=@FirstMeetingID AND RecurringGroupID=@RecurringGroupID

		--delete all meeting groups
		DELETE ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID=@RecurringGroupID
		DELETE ccccon.RecurringGroups WHERE RecurringGroupID=@RecurringGroupID

		FETCH NEXT FROM RecurringGroups_Cursor INTO @RecurringGroupID, @InitialMeetingID
	END --WHILE @@FETCH_STATUS = 0 FOR RecurringGroups_Cursor

	CLOSE RecurringGroups_Cursor
	DEALLOCATE RecurringGroups_Cursor
	
RETURN
GO

прошел отладчиком в SQL до первого падения. Упало на строке
--assign all meetings series to the first group meeting
UPDATE ccccon.MeetingSeries SET MeetingID=@FirstMeetingID WHERE MeetingID IN
(SELECT MeetingID FROM ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID=@RecurringGroupID)

Запрос реальный там был такой
UPDATE ccccon.MeetingSeries SET MeetingID='9D0A003D-8CA4-46F9-81AD-58D21CE3554F' WHERE MeetingID IN
(SELECT MeetingID FROM ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID='F724AE69-3862-4B8C-9CAC-0012D0511CB9')

Написало такую ошибку:
Server: Msg 547, Level 16, State 1, Procedure MakeMigration15, Line 36
[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_MeetingSeries_Meetings'. The conflict occurred in database 'ccccdev', table 'Meetings', column 'MeetingID'.


The statement has been terminated.

@RETURN_VALUE = N/A

Когда же потом просто в QA выполнил эту сроку - все прошло нормально.
(3 row(s) affected)

Есть идеи? Думаю, может курсор как-то выборочно блокирует внешние ключи.
2 июл 09, 14:21    [7368473]     Ответить | Цитировать Сообщить модератору
 Re: Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
varlo
Member

Откуда:
Сообщений: 82
Но и внешний ключ удалять нельзя.
3 июл 09, 10:08    [7371825]     Ответить | Цитировать Сообщить модератору
 Re: Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
varlo
Member

Откуда:
Сообщений: 82
Можно ли как-то уйти от курсоров или хотя бы от одного из них, чтобы не создавался курсор в цикле другого?
3 июл 09, 11:01    [7372153]     Ответить | Цитировать Сообщить модератору
 Re: Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
Glory
Member

Откуда:
Сообщений: 104760
varlo
Можно ли как-то уйти от курсоров или хотя бы от одного из них, чтобы не создавался курсор в цикле другого?

Откуда же это может быть известно, если вы ни одним словом не обмолвились о той задаче, которую вы решаете ?

ЗЫ
И чудес не бывает. Если сервер пишет, что ваш запрос нарушает ссылочную целостность, то значит он таки нарушает ее
3 июл 09, 15:36    [7374213]     Ответить | Цитировать Сообщить модератору
 Re: Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
йцу1
Guest
Как стабильно падает? На каком шаге?
Есть ли триггеры на таблицах?
3 июл 09, 16:26    [7374674]     Ответить | Цитировать Сообщить модератору
 Re: Падает в курсоре по ключу, но если выполнить отдельно - но нормально  [new]
varlo
Member

Откуда:
Сообщений: 82
Триггеров нету. Сейчас слил 2 курсора воедино

ALTER PROCEDURE ccccon.MakeMigration15
AS
	DECLARE @RecurringGroupID uniqueidentifier
	DECLARE @OldRecurringGroupID uniqueidentifier
	DECLARE @InitialMeetingID uniqueidentifier
	DECLARE @MeetingID uniqueidentifier
	DECLARE @MeetingSeriesId uniqueidentifier
	DECLARE @ParticipationRoleId uniqueidentifier
	DECLARE @MemberId uniqueidentifier
	DECLARE @MeetingParticipantId uniqueidentifier
	DECLARE @FirstMeetingID uniqueidentifier
	DECLARE @FirstParticipationRoleId uniqueidentifier
	DECLARE @FirstMemberId uniqueidentifier
	DECLARE @LeaveOnlyFirstMeetingParticipant BIT	
	
	--select all RecurringGroups and all meetings inside of recurring group order by time
	DECLARE RecurringGroupMeetingsXRef_Cursor CURSOR FOR
		SELECT rg.RecurringGroupID,rg.InitialMeetingID,m.MeetingID,ms.MeetingSeriesId 
		FROM ccccon.RecurringGroups rg
		INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmr ON rg.RecurringGroupID=rgmr.RecurringGroupID
		INNER JOIN ccccon.Meetings m ON rgmr.MeetingID=m.MeetingID 
		INNER JOIN ccccon.MeetingSeries ms ON ms.MeetingID=m.MeetingID 
		WHERE m.Status <> 'Cancelled'
		ORDER BY rg.RecurringGroupID,ms.StartTime

		OPEN RecurringGroupMeetingsXRef_Cursor
		FETCH NEXT FROM RecurringGroupMeetingsXRef_Cursor INTO @RecurringGroupID,@InitialMeetingID,@MeetingID,@MeetingSeriesId
		SET @FirstMeetingID=@MeetingID --store first meeting id from group
		SET @OldRecurringGroupID=@RecurringGroupID --store old recurring group

		--compare meetings inside of recurring group with the first group meeting
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			IF @OldRecurringGroupID != @RecurringGroupID
			BEGIN
				--set MeetingParticipants.MeetingID=@FirstMeetingID for old recurring group				
				UPDATE ccccon.MeetingParticipants SET MeetingID=@FirstMeetingID FROM ccccon.MeetingParticipants mp
				INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON mp.MeetingID=rgmf.MeetingID
				WHERE rgmf.RecurringGroupID=@OldRecurringGroupID

				--set MeetingSeries.MeetingID=@FirstMeetingID for old recurring group				
				UPDATE ccccon.MeetingSeries SET MeetingID=@FirstMeetingID FROM ccccon.MeetingSeries ms
				INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON ms.MeetingID=rgmf.MeetingID
				WHERE rgmf.RecurringGroupID=@OldRecurringGroupID

				--set Pins.MeetingID=@FirstMeetingID for old recurring group				
				UPDATE ccccon.Pins SET MeetingID=@FirstMeetingID FROM ccccon.Pins p
				INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON p.MeetingID=rgmf.MeetingID
				WHERE rgmf.RecurringGroupID=@OldRecurringGroupID

				--delete Pins,MeetingParticipants,MeetingSeries for template meeting
				DELETE ccccon.Pins WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
				DELETE ccccon.MeetingParticipants WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
				DELETE ccccon.MeetingSeries WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID
				DELETE ccccon.Meetings WHERE MeetingID=@InitialMeetingID AND @InitialMeetingID!=@FirstMeetingID

				--delete meetings from old recurring group except the first one			
				DELETE ccccon.Meetings FROM ccccon.Meetings m 
				INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON m.MeetingID=rgmf.MeetingID
				WHERE m.MeetingID!=@FirstMeetingID AND RecurringGroupID=@OldRecurringGroupID
		
				--delete this meeting group and its references
				DELETE ccccon.RecurringGroupMeetingsXRef WHERE RecurringGroupID=@OldRecurringGroupID
				DELETE ccccon.RecurringGroups WHERE RecurringGroupID=@OldRecurringGroupID
			END --@OldRecurringGroupID != @RecurringGroupID

			--assign all meetings series to the first group meeting
			UPDATE ccccon.MeetingSeries SET MeetingID=@FirstMeetingID FROM ccccon.MeetingSeries ms 
			INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON ms.MeetingID=rgmf.MeetingID
			WHERE ms.MeetingID!=@FirstMeetingID AND rgmf.RecurringGroupID=@RecurringGroupID
	
			UPDATE ccccon.Pins SET MeetingID=@FirstMeetingID FROM ccccon.Pins p 
			INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON p.MeetingID=rgmf.MeetingID
			WHERE p.MeetingID!=@FirstMeetingID AND rgmf.RecurringGroupID=@RecurringGroupID
	
			--analyze first group meeting: store  ParticipationRoleId, MemberId for the first group meeting
			SELECT @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId 
			FROM ccccon.MeetingParticipants WHERE MeetingID=@FirstMeetingID

			IF @@ROWCOUNT > 1 -- there are some rows
				SELECT @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId 
				FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID AND 
				ParticipationRoleId='221C26BB-44E0-430A-BF4D-CDA999FD3395' --Presenter
			ELSE --no presenter
				SELECT TOP 1 @FirstParticipationRoleId=ParticipationRoleId, @FirstMemberId=MemberId 
				FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID --take the first Participant
	
			SET @LeaveOnlyFirstMeetingParticipant=1

			--analyze group meeting: store  ParticipationRoleId, MemberId and MeetingID for the group meeting
			SELECT @MeetingParticipantId=MeetingParticipantId, @ParticipationRoleId=ParticipationRoleId, @MemberId=MemberId 
			FROM ccccon.MeetingParticipants WHERE MeetingID=@MeetingID
			--compare them with the first ones
			IF @MeetingParticipantId IS NOT NULL
			BEGIN
				IF @ParticipationRoleId!=@FirstParticipationRoleId OR @MemberId!=@FirstMemberId
				BEGIN
					--if they don't match then leave for 
					SET @LeaveOnlyFirstMeetingParticipant=0
					--transfer this records to MeetingSeriesParticipants
					INSERT ccccon.MeetingSeriesParticipants(MeetingSeriesId,MeetingParticipantId,Include) VALUES (@MeetingSeriesId,@MeetingParticipantId,1)
					--delete transfered record from MeetingParticipants 
					DELETE ccccon.MeetingParticipants WHERE MeetingParticipantId=@MeetingParticipantId
					--BREAK
				END
			END --IF @MeetingParticipantId IS NOT NULL
			--reassign all records from Notes to the first group meeting
			UPDATE ccccon.Notes SET MeetingID=@FirstMeetingID WHERE MeetingID=@MeetingID		
	  	--if they match then and set IncludeByDefault=Y for the first
			IF @LeaveOnlyFirstMeetingParticipant=1
			BEGIN
				--delete for all except the first
				DELETE ccccon.MeetingParticipants FROM ccccon.MeetingParticipants mp
			  INNER JOIN ccccon.RecurringGroupMeetingsXRef rgmf ON mp.MeetingID=rgmf.MeetingID
				WHERE mp.MeetingID!=@FirstMeetingID AND rgmf.RecurringGroupID=@RecurringGroupID
				--set IncludeByDefault=Y for the first
				UPDATE ccccon.MeetingParticipants SET IncludeByDefault =1 WHERE MeetingID=@FirstMeetingID
			END

			SET @OldRecurringGroupID=@RecurringGroupID --store old recurring group
			--obtain the next meeting in the group
			FETCH NEXT FROM RecurringGroupMeetingsXRef_Cursor INTO @RecurringGroupID,@InitialMeetingID,@MeetingID,@MeetingSeriesId

		END --WHILE @@FETCH_STATUS = 0 FOR RecurringGroupMeetingsXRef_Cursor

		CLOSE RecurringGroupMeetingsXRef_Cursor
		DEALLOCATE RecurringGroupMeetingsXRef_Cursor

RETURN
GO
3 июл 09, 17:20    [7375127]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить