How to use Excel VBA program to execute SQL SERVER stored procedur
I have some sample code that executes sp_dboption to set quoted identifiers
on for Pubs, then displays all set options for Pubs database. Hope it helps
some.
Sub testSP()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "ServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
cmd.ActiveConnection = cn
cmd.CommandText = _
"sp_dboption ('pubs','quoted identifier','on')"
cmd.CommandType = adCmdStoredProc
Set rs = cmd.Execute
cmd.CommandText = "sp_dboption ('pubs')"
Set rs = cmd.Execute
rs.MoveFirst
Do While Not rs.EOF
For Each f In rs.Fields
Debug.Print f.Name & vbTab & f.Value
Next
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
--
urkec
"Arron" wrote:
Dear all,
I want to use Excel VBA program to execute SQL SERVER stored procedure.
But error occurs."Error sql statement ,except Insert update delete procedure"
How can I correct this ? thanks
code:
sqlstr = "exec stored_proc_name('Parameter')"
Sqldb.Execute sqlstr
|