Hi!

I hope you can help me.

My questions are:

1) How to run a stored proc from Excel VBA and get the data into Excel starting on A15?

2) How to give the user the flexibility to select the database to run (database is the parameter in the stored proc) and that would be passed onto the stored procedure?

3) What would happen if this Excel spreadsheet is used by 2 people at the same time selecting 2 different parameters? Would they get mixed up data?

I have a stored proc in SQL Server 2000 that has an input parameter. (databases)

Here is my stored proc:

CREATE PROCEDURE PREVIEW_TEST (@Matter varchar(100))
AS

SET NOCOUNT ON

-- 1) we get all the custodians names and id's into a temp table for ALL CLIENTS

declare @sql nvarchar(4000)
declare @db varchar(300)



set @db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name

CREATE TABLE #temp_custodian_name_id([Server Name] nvarchar(40),
[Database Name] varchar(100),
[custodian ID] int,
[Custodian Name] nvarchar(300)
)

open cDB
FETCH NEXT FROM cDB INTO @db
while (@@fetch_status = 0)
begin

SET @sql= 'SELECT @@SERVERNAME as ''[Server Name]'', ' +
'''' + @db + '''' + ' as ''[Database Name]'',' +
'[ID] as ''[custodian ID]'',' +
'name as ''[Custodian Name]''' +
'FROM ' + @db + '.dbo.filo_Owners WHERE ''' + @db + ''' like ''Client%'''

INSERT #temp_custodian_name_id

EXEC sp_executesql @sql

fetch cDB into @db
end
close cDB
deallocate cDB

select * from #temp_custodian_name_id order by [Custodian ID]
GO

Here is what I have as the VBA code:
Sub Preview_Report()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

Dim strServer As String, strDatabase As String

strServer = "dr-ny-sql001"
strDatabase = "master"

'Use the SQL Server ODBC Provider.

'strConn = "Driver={SQL Server};Server=" & strServer & ";Database=" & strDatabase & ";Uid=" & strUser & ";Pwd=" & strPassword & ";"

'Use the SQL Server OLE DB Provider.
'strConn = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=" & strDatabase & ";User Id=" & strUser & ";Password=" & strPassword & ";"
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=" & strServer & ";INITIAL CATALOG=" & strDatabase & ";"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cn.Open strConn

Range("A15:AZ500").ClearContents

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "master.dbo.usp_DR_Preview_test" 'Name of stored procedure

With cmd
.Parameters.Refresh
.Parameters.Append .CreateParameter("@Matter", adVarChar, adParamInput, 100, Range("D2")) ' Character value of no more than 255 bytes
' Other possible parameter types
'.Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, "Value")
'.Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
'.Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
'.Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
'.Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
End With

cmd.CommandType = adCmdStoredProc

Set rs = New ADODB.Recordset

'With rs
'.Source = "SET NOCOUNT ON"
'.ActiveConnection = cn
'.Open
'End With

Set rs = cmd.Execute()

'If Not rs.EOF Then
Worksheets("Sheet1").Range("A15").CopyFromRecordset rs
'Else
'MsgBox "No data returned from stored procedure: '" & "usp_DR_Preview_test" & "' using parameter value: " & Range("D2")
'End If

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub
I would really,really appreciate your help!

I hope I am clear enough on what I am looking for.

Thanks a lot from a newbie to VBA and to this forum.

Tammy :wave: