Gespeicherte Prozedure unter Access 2010

Question

Hallo Leute!

Bin mit meinem Latein am Ende. Ich hab hier eine SP gebaut welche unter SQL Managementstudio wunderbar läuft.
Sie schreibt die Daten in eine temporäre Tabelle und wirft den Inhalt danach mittels eine Select Statement aus.
Soweit wunderbar

Wenn ich Sie unter Access abrufen will kommt "Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
Wenn ich die SP am SQL laufen lassen und eine tempärere Tabelle befülle und dann in der SP alles außer das SQL Statement zum abrufen aus der temporären Tabelle auskommentiere kann ich sie unter Access starten und es wirft mir die Inhalte aus.

Das heißt:
Wenn ich nur über Management Studio erzeugte temp. Tabelle auslese (alles andere auskommentiert): keine Problem
Wenn ich die SP komplett über access laufe lassen: Fehlermeldung siehe oben.

Hat jemand eine Idee.  Vielen Dank im voraus

LG Christian

 

ACCESS Codeunit

Sub test3()

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim intcount As Integer

cmd.Parameters.Append cmd.CreateParameter("p_art", adVarChar, adParamInput, 20, "SB")

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Monatsbericht"
cmd.CommandType = adCmdStoredProc

rs.ActiveConnection = CurrentProject.Connection
Set rs = cmd.Execute

Do While Not rs.EOF
    Debug.Print rs(0)
    rs.MoveNext
Loop
End Sub

 

Stored Procedure

if @p_art = 'SB'

if OBJECT_ID('tempdb..##sb') is not null

DROP TABLE tempdb..##sb

CREATE TABLE ##sb(

[kgname] varchar(30) NULL,

[wert1] [int] NULL,

[wert2] [int] NULL,

[wert3] [int] NULL,

)

declare @kgname varchar(30), @wert1 int, @wert2 int, @wert3 int

declare rs cursor for SELECT kgname as kgn FROM Basis_KGLISTE GROUP BY kgname for read only

open rs

while (0 = 0)

begin

fetch next from rs into @kgname

if (@@fetch_status <>0) break

set @wert1 = (SELECT COUNT(bw.WOHID)

FROM Basis_OBJEKT as obj INNER JOIN

Basis_WOHNUNG as bw ON obj.OBJ_KZ = bw.OBJ_KZ INNER JOIN

Basis_KGLISTE as kg ON obj.KGNUMMER = kg.kgnummer INNER JOIN

BPH_Gutachten_SPP as bph_ga ON bw.WOHID = bph_ga.WOHID inner JOIN

SPP_Status w_status ON bw.WOHID = w_status.wohid

where (CASE WHEN bph_ga.Manahmen LIKE '%SB%' THEN '1' ELSE '0' END) = 1 and kg.kgname = @kgname)

set @wert2 = (SELECT COUNT(bph_ra.RAIndex)

FROM Basis_OBJEKT AS obj INNER JOIN

Basis_WOHNUNG AS bw ON obj.OBJ_KZ = bw.OBJ_KZ INNER JOIN

Basis_KGLISTE AS kg ON obj.KGNUMMER = kg.kgnummer INNER JOIN

BPH_Gutachten_SPP AS bph_ga ON bw.WOHID = bph_ga.WOHID INNER JOIN

BPH_Rume as bph_ra ON bph_ga.[Index] = bph_ra.[Index] RIGHT OUTER JOIN

SPP_Status AS w_status ON bw.WOHID = w_status.wohid

WHERE (CASE WHEN Manahmen LIKE '%SB%' THEN '1' ELSE '0' END) = 1 and bph_ga.[Gutachten abgeschlossen] =1 and kg.kgname = @kgname)

 

set @wert3 = (SELECT COUNT(bw.WOHID)

FROM Basis_OBJEKT AS obj INNER JOIN

Basis_WOHNUNG AS bw ON obj.OBJ_KZ = bw.OBJ_KZ INNER JOIN

Basis_KGLISTE AS kg ON obj.KGNUMMER = kg.kgnummer INNER JOIN

BPH_Gutachten_SPP AS bph_ga ON bw.WOHID = bph_ga.WOHID INNER JOIN

SPP_Status AS w_status ON bw.WOHID = w_status.wohid

WHERE w_status.Generalplaner_SB <>'' and kg.kgname = @kgname)

 

insert into ##sb (kgname,wert1,wert2,wert3) values (@kgname, @wert1, @wert2,@wert3)

end

close rs

deallocate rs

select * from ##sb

if @p_art = 'SB' return;

Answer 1

Hallo Christian,

da die Prozedur mehrere Anweisungen enthält sollte zu Beginn der Prozedur ein SET NOCOUNT ON stehen.
Denn ansonsten bekommst Du Rückgaben von jeder Anweisung auch den INSERTs usw. -
was die sind bewußten geschlossenen (leeren) Recordsets sind.
Die man in ADO in dem Fall verarbeiten müsste, was hier aber keinen Mehrwehrt hat.
Mehr siehe: Gewusst wie: verarbeiten mehrere Recordsets und Nachrichten in ADO

Hier nicht erforderlich, aber möglich wäre vor dem abschliessenden SELECT am Ende einzufügen

 

SELECT NOCOUNT OFF;
select * from ##sb;

 

um die Anzahl zu erhalten. Nicht erforderlich, weil das Lesen eines Forwardonly Cursor auch ohne funktioniert.

Einige andere Dinge zu Deiner Prozedur:
Du solltest besser eine lokale temporäre Tabelle (mit einem #) verwenden, sonst bekommst Du Probleme
wenn die Prozedur von zwei Plätzen (oder mehr aufgerufen wird.

Der Cursor könnte entfallen, wenn Du - ungetestet zusammgestöpselt -, etwas verwenden würdest wie:

 

		INSERT INTO #sb (kgname,wert1,wert2,wert3)
		SELECT 
			(SELECT COUNT(bw.WOHID)
				FROM Basis_OBJEKT as obj 
				INNER JOIN as bw ON obj.OBJ_KZ = bw.OBJ_KZ 
				INNER JOIN Basis_KGLISTE as kg ON obj.KGNUMMER = kg.kgnummer 
				INNER JOIN BPH_Gutachten_SPP as bph_ga ON bw.WOHID = bph_ga.WOHID 
				inner JOIN SPP_Status w_status ON bw.WOHID = w_status.wohid
				-- Konvertiert zuviel '1' oder immer Zahl
				where (CASE WHEN bph_ga.Manahmen LIKE '%SB%' THEN 1 ELSE 0 END) = 1 
					and kg.kgname = kgliste.kgname),
		
			(SELECT COUNT(bph_ra.RAIndex)
				FROM Basis_OBJEKT AS obj 
				INNER JOIN Basis_WOHNUNG AS bw ON obj.OBJ_KZ = bw.OBJ_KZ 
				INNER JOIN Basis_KGLISTE AS kg ON obj.KGNUMMER = kg.kgnummer 
				INNER JOIN BPH_Gutachten_SPP AS bph_ga ON bw.WOHID = bph_ga.WOHID 
				INNER JOIN BPH_Rume as bph_ra ON bph_ga.[Index] = bph_ra.[Index] 
				RIGHT OUTER JOIN SPP_Status AS w_status ON bw.WOHID = w_status.wohid
				WHERE (CASE WHEN Manahmen LIKE '%SB%' THEN '1' ELSE '0' END) = 1	
					and bph_ga.[Gutachten abgeschlossen] = 1 
					and kg.kgname = kgliste.kgname)

			(SELECT COUNT(bw.WOHID)
				FROM Basis_OBJEKT AS obj 
				INNER JOIN Basis_WOHNUNG AS bw ON obj.OBJ_KZ = bw.OBJ_KZ 
				INNER JOIN Basis_KGLISTE AS kg ON obj.KGNUMMER = kg.kgnummer 
				INNER JOIN BPH_Gutachten_SPP AS bph_ga ON bw.WOHID = bph_ga.WOHID 
				INNER JOIN SPP_Status AS w_status ON bw.WOHID = w_status.wohid
				WHERE w_status.Generalplaner_SB <>'' and kg.kgname = kgliste.kgname)
		FROM (SELECT DISTINCT 
				kgname 
			FROM Basis_KGLISTE) AS kgliste

 

Und weitere Optimierungen wären dort machbar, da die gejointen Tabellen auf den ersten Blick
sehr ähnlich sind und teilweise anscheinden nicht einmal genutzt.
Benötigtist Du die Kombination häufiger, so wäre eine Sicht oder eine (Inline-)Tabellen-Funktion
günstiger (nicht nur das sie es leichter lesbarer macht) - siehe Entwerfen von benutzerdefinierten Funktionen

Die unterschiedliche Behandlung in der WHERE Klausel könnte wiederum durch ein
SUM(CASE WHEN ... THEN 1 ELSE 0 END)  anstatt des COUNT erfolgen.

Auch das (CASE WHEN Manahmen LIKE '%SB%' THEN '1' ELSE '0' END) = 1
kann verkürzt werden auf manahmen LIKE '%SB%'

Alles wäre optional, aber zu empfehlen, wenn die Laufzeit höher ausfällt.

Gruß Elmar

 

Answer 2

Hallo Elmar!

SELECT NOCOUNT ON war genau richtig.

Jetzt funktioniert's!

Vielen, vielen Dank! auch für die restlichen Tipps.

Werde ich mir Montag gleich genauer ansehen.

Christian