Mit VBA, um Ergebnisse von SQL Server gespeicherte Prozedur mit Variablen in ADODB
Arbeite ich an einer rec-Prozess, die ziehen die Daten aus einer SQL Server-Datenbank in Excel und aus einer anderen Anwendung zum vergleichen von zwei Quellen. Um dies zu tun habe ich angefangen mit den Grundlagen und geschrieben, einige code, ziehen Sie die Daten in ein ADODB-Datensatz und legen Sie es in eine Excel-Tabelle aus einer SQL Server-Sicht. Dies funktioniert wie erwartet.
Muss ich die Abfrage dynamischer und, da es ziemlich Ressourcen-intensiv in der realen Abfrage, ich denke, es wäre besser, es zu tun mit einer gespeicherten Prozedur, die übergabe von Parametern um die Abfrage und nur die Rückgabe des Ergebnis-sets, die ich brauche.
Ich möchte nicht die SQL-Abfrage zu kompliziert sein, im VBA aber das ist, warum ich kann nicht einfach definieren, die Abfrage dort.
Wenn ich den folgenden code ausführen, es gibt keine Datensätze, die den Datensatz und beendet. Ausführung der gespeicherten Prozedur im Management Studio unter Verwendung der gleichen parameter gibt die erwarteten Ergebnisse in SSMS.
Bin ich etwas fehlt dazu, wie die Rückkehr dieser Ergebnisse auf den Rekord?
Gespeicherte Prozedur:
CREATE PROCEDURE [dbo].[JoshTest]
@AccCode nvarchar(50)
AS
SET NOCOUNT ON;
SELECT
[Account_SKey], [Hierarchy_SKey], [Account_Code], [Leaf]
FROM
[dbo].[VW_DIM_ACCOUNT_LEAF]
WHERE
[Leaf] = @AccCode;
GO
VBA in Excel:
Sub test_stored_proc()
Dim AccountCodeVar As String
AccountCodeVar = Range("ACCCODESEL").Value
Dim osheet As String 'output sheet
Dim orange As String 'output range
osheet = "Output1"
orange = "A3"
Call ConnectSqlServerStoredProc(osheet, orange, AccountCodeVar)
End Sub
Function ConnectSqlServerStoredProc(osheet As String, orange As String, AccountCodeVar As String)
'Clear previous results
Sheets("Output1").Range("A3:D60000").Clear
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=CSIMCCS01; Initial Catalog=T_EXP_CPM; Integrated Security=SSPI;"
Debug.Print sConnString
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
Set cmd = New ADODB.Command
cmd.CommandText = "JoshTest"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn
'set parameter values
Set prm = cmd.CreateParameter(Name:="AccCode", Type:=adVarChar, Direction:=adParamInput, Size:=10)
cmd.Parameters.Append prm
cmd.Parameters("AccCode").Value = "'" & AccountCodeVar & "'"
Debug.Print cmd.Parameters("AccCode").Value
Set rs = cmd.Execute
' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(osheet).Range(orange).CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
MsgBox ("Done")
End Function
Ich habe mehrere Beispiele für andere Leute Fragen ähnliche Fragen, aber Sie alle scheinen zu versuchen, einen einzigen Wert, der zurückgegeben wird, wie z.B. status - /Benutzer-id etc eher als ein Daten-set.
Hinsichtlich
Du musst angemeldet sein, um einen Kommentar abzugeben.
Arbeitete er sich schließlich... der Probleme war, dass ich setzen Sie einfache Anführungszeichen beiden Seiten des Parameters Wert. Ich dachte, dies wäre korrekt, da SQL müssten Sie aber die Einnahme von VBA hat das Problem behoben.
@Mister 832 - vielen Dank für Ihre Hilfe im stepping, das durch.
Am Ende war die Lösung ändern:
zu
Hinsichtlich
Haben Sie versucht, indem eine @ an den parametername?
Ich habe es funktioniert auf meinem pc.
Entfernen Sie die Zeile
Set rs = New ADODB.Recordset
und ändern Sie die recordsetline zuSet rs = cmd.Execute