Get data from a SQL Server Stored Procedure (Without Parameters) into Excel

Other SQL Server Topics
1

Hi,

I wish to Get data from a SQL Server Stored Procedure (Without Parameters) into Excel

Useful links as guidance >

http://codebyjoshua.blogspot.in/2012...er-stored.html
http://www.sqlservercentral.com/Foru...54-2799-1.aspx

However, I am unable to get the data to be returned.

Below are the necessary details -

Database Name – ZZ_Common

SP Name – Update_Vol

Can anyone help me with this?

Just wanted to know what changes should I do to the ?,? part when I have no parameters at all?

Cheers!
Deep Dave

2

Hi, One method I use is Powershell to move data from SQL Server. the main advantage is the granular control over Excel objects. This post offers an explanation and sample script http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html

3

HI Jack,

Thank you for the reply.

However, I am trying to get the Stored Proc to work through Excel only.. I am avoiding VBA or any other codes for that matter..

Can you suggest how my SQL Statement should look like? I guess that should do it.. :smile:

Cheers!

4

I've done it using an ODBC connection to the database and just linking a SHEET in Excel to the SQL Sproc. User can press REFRESH on that linked tab and then cut & paste data that they want (or directly reference the cells from other sheets).

We use parameters too - in a separate "parameters" sheet

Ages since I've done it hence I'm a bit hazy on the details, but it might point you in the right direction. If not ask again and I'll see if I can dig out some details. Its definitely a "string and chewing gum" solution though.

1 Like
5

@deepDave - do you want to create a new worksheet each time or keep adding data to a worksheet?

1 Like
6

Thank you for the help..

I have managed to get it working.. :slight_smile:

7

This is a example, I hope it helps you:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oActiveWorkbook As Workbook, oNewWorkbook As Workbook
Dim conn, cmd, rs, str As String, i As Integer, strLenguajeAccess As String
Dim strTD As String, strWS As String, intLastCol As Integer, cColName, strDA As String

Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = "DSN=YourOBC;Description=YourDescription;Trusted_Connection=Yes;APP=YourApp;"
conn.Open

Set rs = CreateObject("ADODB.Recordset")
str = "exec [dbo].[YourStoreProcedure] @Parameter1=N'" & Replace(Trim(Target.Value), "'", "''") & "', @Parameter2=2"

rs.Open str, conn, adOpenStatic, adLockReadOnly

If Not IsEmptyRecordset(rs) Then
    Set oActiveWorkbook = ActiveWorkbook
    Workbooks.Add
    Set oNewWorkbook = ActiveWorkbook
    oNewWorkbook.Activate
    oNewWorkbook.Application.Visible = False
    Sheets(ActiveSheet.Name).Name = "Data"
    Sheets(ActiveSheet.Name).Select
    ActiveSheet.Range("A1").Select
    
    rs.MoveFirst
    i = 0
    For Each fld In rs.Fields
        oNewWorkbook.ActiveSheet.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
        i = i + 1
    Next fld
    oNewWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rs

    With oNewWorkbook
        .Application.Visible = True
        .Activate
    End With
Else
    MsgBox "Don´t open store procedure.", _
       vbCritical,
End If

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub