none
Gespeicherte Prozedure unter Access 2010

    Frage

  • 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;

    Samstag, 16. Oktober 2010 14:50

Antworten

  • 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

     

    • Als Antwort markiert Peter DoeringMVP, Moderator Sonntag, 17. Oktober 2010 13:34
    • Tag als Antwort aufgehoben FiC5020 Dienstag, 19. Oktober 2010 08:37
    • Als Antwort markiert FiC5020 Dienstag, 19. Oktober 2010 08:49
    Samstag, 16. Oktober 2010 16:53

Alle Antworten

  • 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

     

    • Als Antwort markiert Peter DoeringMVP, Moderator Sonntag, 17. Oktober 2010 13:34
    • Tag als Antwort aufgehoben FiC5020 Dienstag, 19. Oktober 2010 08:37
    • Als Antwort markiert FiC5020 Dienstag, 19. Oktober 2010 08:49
    Samstag, 16. Oktober 2010 16:53
  • 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

    Samstag, 16. Oktober 2010 18:42