[MSSQL2005] Procedura składowana nie wykonuje się z aplikacj

Cześć, trafiłem na bardzo dziwny problem.
Otóż, mam tabelę:

TAB1
-------------
ID bigint primary key identity
uid bigint not null
did bigint not null
s varchar(max)

Z poziomu aplikacji tworzę sobie tabelę tymczasową #tTab1 - ma te same pola, co tab1 poza ID.
Następnie do tej tabeli tymczasowej robię kilka insertów.

I wywołuję procedurę składowaną, która generalnie przepisuje rekordy z tabeli #tTab1 do tab1. Sprawdzając, czy już istnieją i jeśli istnieje, to wykonuje UPDATE, w innym razie INSERT. Rekord istnieje, jeśli równe są sobie pola UID i DID.

I teraz co się dzieje:

1. Mam uruchomiony program w trybie debugowania. Na serwerze mogę wykonać zapytanie SELECT * FROM TAB1 tylko do momentu wywołania z aplikacji procedury składowanej. Potem gdy to próbuję zrobić, sql się mieli, mieli i nie kończy mielić. Ale gdy zamknę aplikację, normalnie już mogę select puścić, jednak wygląda, że tabela TAB1 jest pusta.
2. Gdy z poziomu serwera utworzyłem tabelę tymczasową i powrzucałem do niej jakieś dane, a potem wywołałem procedurę, wszystko poszło ok. I zobaczyłem coś dziwnego. Kolumna ID była numerowana od 20. Wcześniej robiłem kilka prób z aplikacji, czyli wygląda na to, jakby zwiększany był licznik.

O co tu może chodzić?

Kod procedury składowanej:

CREATE PROCEDURE [dbo].[SetDeptRights]
AS
BEGIN
	SET NOCOUNT ON

	declare @uid bigint
	declare @deptID bigint
	declare @rights varchar(max)
	declare @recID bigint

	declare tdrCur
		cursor for
			select uid, deptID, rights from #tDeptRights

	open tdrCur
	fetch next from tdrCur into @uid, @deptID, @rights

	while @@FETCH_STATUS = 0 
	begin
		-- sprawdzam, czy taki rekord istnieje
		SELECT @recID = ID
		from deptRights 
		where uid = @uid and deptID = @deptID

		if @recID is null
			insert into deptRights(uid, deptID, rights) values(@uid, @deptID, @rights)
		else
			update deptRights SET
				rights = @rights
			where ID = @recID

		fetch next from tdrCur into @uid, @deptID, @rights
	end

	close tdrCur
	deallocate tdrCur
END

[DODANE]
Po głębszej analizie dochodzę do wniosku, że to jest coś nie tak z aplikacją. Ale też coś dziwnego, bo rzeczy, które działały do tej pory nie działają teraz :| Zapytanie niby się wykonuje, nie ma żadnego błędu, a tak naprawdę efektem jest jakby blokada tabeli :|

Nie możesz podejrzeć rekordów, ponieważ SQL zakłada blokadę chroniącą przed "brudnym odczytem".

Upewnij się, że usuwasz tabelę tymczasową po wykonaniu kodu, a jeśli nie chcesz jej usuwać to użyj zwykłej tabeli i czyść ją na końcu procki).

A abstrahując od Twojego problemu proponuję zmienić kursor na:

insert deptRights(uid, deptID, rights)
select tdr.uid, tdr.deptID, tdr.rights from #tDeptRights tdr
left join deptRights dr on tdr.uid = dr.uid and tdr.deptID = dr.deptID 
where dr.uid is null

update deptRights set rights = tdr.rights
from #tDeptRights tdr
inner join deptRights dr on tdr.uid = dr.uid and tdr.deptID = dr.deptID 

czyszczenie tabeli nie ma tutaj nic do znaczenia.
Na poczatek:
a) jesli tworzysz jakikolwiek obiekt bazodanowy to nie mozesz tego robic w transakcji (blokujesz wtedy dostep do wszystkich obiektow systemowych np sysobjects, syscolumns itp)
b) nigdy nie rob update w klauzuli from. Microsoft nie zaleca takiej skladni. Do czasu commit transakcji wszystkie obiekty biorace udzial w operacji beda blokowane (cale obiekty a nie strony)

hmmm...
pierwszy raz słyszę taką opinię - ciekawe po co w takim razie całe blogi i artykuły MS o tym, żeby szerokim łukiem omijać kursory...

Przeciez nie napisal, ze kursory lepsze. Rownie dobrze mozesz zrobic normalny update z where.

@Johny
nie przekomarzaj się;)

Jak korzysta z danych z innej tabeli to na samym wherze nie zrobi.

@crowa
Nie wiem jaki kolega ma pomysł jak to zrobić bez from żeby było wydajnie?? Cursor? Podzapytanie dla każdego seta?

A swoją drogą "Microsoft nie zaleca" - wtf?!? - podaj choć jeden artykuł.

Fucktycznie, nie zauwazylem tego "tdr.rights". Tez mi sie nie wydaje, zeby ta konstrukcja byla zakazana...

ja bym to rozbil na dwie sekcje z klauzula exists (jedna insert druga update).

Jesli wykonujesz operacje w sekcji UPDATE FROM
to sql server dokonuje blokady na obiekcie za FROM.

Tam jest jedna tabela tymczasowa - ogolnie dobra praktyka jest operowanie na tabelach "stalych".
Dostep do tabel # lub ## jest mniej ciekawy. Nalezy zawsze pamietac ze tworzenie obiektow bazodanowych powinno byc beztransakcyjne (rowniez tabele temporarne utworzone w transakcji powoduja blokowanie sysobjects)

Artykulu teraz nie moge znalezc (poszukam) ale juz raz czytalem o update z from (nie bylo to zalecane ale tez nie bylo zabronione :) )