Running Stored Procedures in Excel

JezLisle · #1 (**permalink**) 09-17-08, 09:23

Quick question,

I have Stored Procedures in my SQL Server database and need to query from Excel, is it the same way as I would when creating a standard query?

This is how my standard query looks,

Code:

cnnDW.Open strDWFilePath
    Set rsDW = New ADODB.Recordset
    sQRY = "SELECT     TOP (100) PERCENT [Weeks Range Title] AS WeeksWaiting, COUNT(*) AS Count " & _
            "FROM jez.DermAdults " & _
            "GROUP BY [Weeks Range], [Weeks Range Title] " & _
            "ORDER BY [Weeks Range] "
    rsDW.CursorLocation = adUseClient
    rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
    Application.ScreenUpdating = False
    Sheet1.Range("B4").CopyFromRecordset rsDW
    rsDW.Close
    Set rsDW = Nothing

this is my Execute code from SQL Server, would this fit after sQRY= "

Code:

EXEC sp_WLName_Report 'September', '2008', '17-09-2008', 'T2DEA'

gvee · #2 (**permalink**) 09-17-08, 10:26

There's that TOP 100 PERCENT again!

You're right in thinking there'd be a change; have a google for parameterized queries and see where that gets you.

If you are struggling, post back and I'll see if I can dig out some of my old code

JezLisle · #3 (**permalink**) 09-17-08, 10:44

Thanks....

I changed sQRY =

to sQRY= EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'T2DEA'"

and all works well.

I use the TOP 100 PERCENT as that brings back the records I need, if I change to anything else it doesnt work.

JezLisle · #4 (**permalink**) 09-18-08, 08:53

I have changed my Stored procedure as the sQRY in the thread above was great for only particular results on the 4th section of my Exec Statement.

It now looks like this below, but my problem is that once I run the code it brings back this error message, I dont quite understand what it means, can someone explain to me where I gone wrong as the Exec Statement works as it should in SQL Server

Error : Operation is not allowed when the object is closed. 3704

Code:

Sheet1.Range("E4:F9").ClearContents
    Set rsDW = New ADODB.Recordset
    sQRY = "DECLARE @wlvals varchar(100) " & _
                "SET @wlvals =  char(39) + 'T2DEA' + char(39) + ',' + char(39) +  'T2DEP' + char(39) " & _
                "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals"
    rsDW.CursorLocation = adUseClient
    rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
    Application.ScreenUpdating = False
    Sheet1.Range("E4").CopyFromRecordset rsDW
    rsDW.Close
    Set rsDW = Nothing

JezLisle · #5 (**permalink**) 09-22-08, 08:37

I'm still trying to sort this problem...

The code below is where I am upto now. theres an error message now as below too, I cant see where I am going wrong??

Code:

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
Dim cmdSP As ADODB.Command
Dim strDWFilePath As String
Dim stProcName As String

Sub GetData()

    strDWFilePath = "Driver={SQL Native 
Client};Server=CISSQL1;Database=CORPINFO;Trusted_Connection=Yes"
    Set cnnDW = New ADODB.Connection
    Application.ScreenUpdating = False
    cnnDW.Open strDWFilePath
    Set cmdSP = New ADODB.Command
    Set rsDW = New ADODB.Recordset
    
    stProcName = "DECLARE @wlvals varchar(100) " & _
                                                    "SET @wlvals =  char(34) 
+ 'T2DEA' + char(34) + ',' + char(34) +  'T2DEP' + char(34);" & _
                                                    "EXEC sp_WLName_Report 
'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure 
to execute."
    cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command
    cmdSP.ActiveConnection = cnnDW 'Set the command connection string
    cmdSP.CommandText = stProcName 'Define Stored Procedure to run
   
    Sheet1.Range("E4:F9").ClearContents
    rsDW.Open cmdSP.Execute(stProcName)
    Application.ScreenUpdating = False
    Sheet1.Range("E4").CopyFromRecordset rsDW
    rsDW.Close
    Set rsDW = Nothing
    MsgBox "Import Complete", vbInformation, "SQL Connection"
    cnnDW.Close
    Set cnnDW = Nothing
    Exit Sub
End Sub

It now has moved my errror message from the Exec Statement to the line
below, but I am still baffled as to why it doesnt work...

Code: