I have a SQL Server stored procedure I need to run from within an excel
spreadsheet, if that's possible.

The stored procedure takes about three minutes to run, and the end result is
10 columns worth of data. Ideally, I'd like for those 10 columns to be my
spreadsheet's 10 columns.

Are there code snippets out there I can look at to learn:

1. how to connect to a SQL Server database via excel (hopefully using a
DSN-less connection-- I have a connection string), and
2. how to call a SQL Stored procedure from Excel, and snag the resulting
data.

TIA,

-Jim

Hi Jim,

"Jim Bancroft" <bobbgambles_at_nospam.msn.com wrote in message
...

I have a SQL Server stored procedure I need to run from within an excel
spreadsheet, if that's possible.

The stored procedure takes about three minutes to run, and the end result
is
10 columns worth of data. Ideally, I'd like for those 10 columns to be my
spreadsheet's 10 columns.

Are there code snippets out there I can look at to learn:

1. how to connect to a SQL Server database via excel (hopefully using a
DSN-less connection-- I have a connection string), and
2. how to call a SQL Stored procedure from Excel, and snag the resulting
data.

I've pasted some code below that shows you how to execute a SP from Excel.
It's not that hard, it just involves a few steps. If you don't have any
parameters in your SP, you can take out the 2 lines related to that. And
you'll want to add error handling that cleans up when you hit an error.
Most errors will occur when connecting (if the connection is down or the
string is incorrect) or when executing the SP via the Command object (this
will fail if anything you've done up to that point is incorrect or missing).

BTW, 3 minutes for a SP to execute seems like a long time - do you have a
_ton_ of data, or is the query very complex? Just wondering if you can't
optimize the query a bit.

Regards,

Jake Marx
MS MVP - Excel



---------sample code----------
Sub PutRecordsetInRange()
'/ you must set a reference to the Microsoft ActiveX Data Objects 2.x
Library
'/ (where x is the highest # you or your users will have)
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rsData As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.ConnectionString = "<your cxn string"
.Mode = adModeRead
.Open
End With

Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "dbo.<your sp name_sp"
.CommandTimeout = 180 '/ set this higher if you need to

.Parameters.Append .CreateParameter("@starttime", _
adDBTimeStamp, adParamInput, , CLng(Now()) - 2)
.Parameters.Append .CreateParameter("@endtime", adDBTimeStamp, _
adParamInput, , Now())

Set rsData = .Execute
End With

With rsData
If .State = adStateOpen Then
If Not (.BOF And .EOF) Then
'/ got records - put them in range
Range("A1").CopyFromRecordset rsData
End If
.Close
Else
'/ error
End If
End With

Set rsData = Nothing
Set cd = Nothing
cn.Close
Set cn = Nothing
End Sub