Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Running Stored Procedures in Excel

    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'

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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.

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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:
        rsDW.Open cmdSP.Execute(stProcName)
    My Error message...

    [Microsoft][SQL Native Client] Syntax Error, Permission Violation or Other
    NonSpecific Error

    Where am I going wrong?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •