Excel VBA to call SQL Stored procedure

I have problem using VBA to call. No error pop up and nothing is returned, Any idea ?


Tks
Result.pdf
AXISHKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
AXISHK, maybe you didn't see my last comment:
You need to specify  that you want to run a Stored Procedure so add the following line:
  Cmd.CommandType = adCmdStoredProc
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shouldn't you define the cmd and Rs variables?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also try Rs.Open cmd instead of the Execute method.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry but starting to get refreshed with ADO. For long that I don't use it.
You need to specify  that you want to run a Stored Procedure so add the following line:
 Cmd.CommandType = adCmdStoredProc
0
 
AXISHKAuthor Commented:
Pop up another message ...

actually, I follow the coding on the link below,,
http://mikesknowledgebase.azurewebsites.net/pages/SQLServer/RunStoredProcedureFromExcel.htm

Tks
Result1.png
0
 
Haris DjulicConnect With a Mentor Commented:
Hi,

in order to reuse the code for this purpose i prepare the function to open connection:

Private Cn As ADODB.Connection
Public Function ConnectionString() As ADODB.Connection

    Dim connstr As String

 connstr = "Provider=SQLOLEDB;Server=(local);Database=MYDB;Trusted_connection=yes"
    
    If Cn Is Nothing Then
    Set Cn = New ADODB.Connection
        With Cn
        .ConnectionString = connstr
        .CommandTimeout = 0
        .Open
        End With
    End If
    
   Set ConnectionString = Cn
End Function

Open in new window


then i have function to call the procedure with 1 parameter
Private rs As New ADODB.recordset
Private cmd As New ADODB.Command

Public Function sql_query(proc_name As String, param1 As String)
Set Cn = ConnectionString()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    With cmd
        .CommandText = proc_name
        .CommandType = adCmdStoredProc
        .ActiveConnection = Cn
        .Parameters.Refresh
        .Parameters(1).Value = param1
    End With
rs.Open cmd
Set sql_query= rs
End Function

Open in new window



and finally i have sub which will cal the function and display the results on the sheet..

sub display_proc_result

' call of the function
Set rs = sql_query("wbdisplaybook", "P000210584")

'display retrived dataset

Me.Range("A8:P1000").ClearContents
 col = 0
 Row = 8

      Do While Not rs.EOF
  
  If Row = 8 Then
      Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Name
      col = col + 1
    Loop
    End If
    
    Row = Row + 1
    col = 0
    
    Do While col < rs.Fields.Count
      Cells(Row, col + 1) = rs.Fields(col).Value
      col = col + 1
    Loop
  
    rs.MoveNext
  Loop
  
  Me.Range("A8:P1000").EntireRow.Columns.AutoFit
end sub

Open in new window

0
 
AXISHKAuthor Commented:
Yes, already added that but still don't retrieve any record...

Public Const ConPro As String = "Provider=sqloledb;Server=10.0.x.x;Database=HKG;Uid=report;Pwd=xxxxl"
Dim conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Even the commandtype?
What's the error now?
0
 
AXISHKAuthor Commented:
I have rewritten the code, it stop at ".Parameter(1),Value = stPar1

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

conn.Open ConPro
cmd.ActiveConnection = conn

Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenStatic
With cmd
    .CommandText = "wbdisplaybook"
    .ActiveConnection = conn
    .Parameters.Refresh
    .Parameters(1).Value = stPar1
End With

Rs.Open cmd
0
 
AXISHKAuthor Commented:
Try to use Haris's proposed macro to test the stored proc but it return with error. Any idea ?

Tks
StoredProc.xlsm
0
 
AXISHKAuthor Commented:
Find a root problem,

If i take away redirecting to a temporary table and select the records from a table directly, my code works fine.
However, whole store procedure need to use few temporary table in order to get a result. any idea how to solve it ? Tks


CREATE PROCEDURE [dbo].[sp_wbDisplayBook] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.THK_INVENTD2ID, D.THK_INVENTD3ID, D.THK_INVENTD4ID, D.THK_INVENTDGOLDTYPEID, THK_REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END AS KTL_STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
                  +SUBSTRING(A.COSTGROUPID,4,3)+CAST(CAST(SUM(OUTQTY) AS decimal(20,2)) AS NVARCHAR(10)) Else '' END AS Material
 into #TM1
 FROM DynamicsAX_PRO.dbo.THK_LASTWIPMATERIALTRANS A
INNER JOIN DynamicsAX_PRO.dbo.BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPID
INNER JOIN DynamicsAX_PRO.dbo.INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBATCHID AND C.DATAAREAID='OPR'
INNER JOIN DynamicsAX_PRO.dbo.BOMVERSION D ON C.THK_REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
inner join dbo.split(@INVENTBATCHID,',') as e on A.INVENTBATCHID COLLATE DATABASE_DEFAULT = e.Data COLLATE DATABASE_DEFAULT
GROUP BY SUBSTRING(A.COSTGROUPID,4,3),CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.KTL_STONETYPE END,
A.COSTGROUPID,A.INVENTBATCHID,D.ITEMID,D.THK_INVENTD2ID,D.THK_INVENTD3ID,D.THK_INVENTD4ID,D.THK_INVENTDGOLDTYPEID,
THK_REFBOMID

select * from #TM1
END
0
 
AXISHKAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.