Dear experts,
i'm trying to call a stored procedure from VBS / VBA using ADO and though a connection establishes, the query always fails.
Details below.
Error-message
"Syntaxerror oder access denied" in VBS
"Operation is not allowed when the object is closed" in VBA
(can be seen in watch-window)
Code-examples
VBA ...
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As New ADODB.Recordset
Dim strRS As String
sPro = "Provider=SQLOLEDB.1;"
sUID = "UID=nnnn;"
sPW = "PWD=xxxx;"
sDsn = "Initial Catalog=dbTest;"
sSer = "Data Source=TestMachine"
sCon = sPro + sUID + sPW + sDsn + sSer
Set cn = New ADODB.Connection
cn.Open sCon
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
If rs.State = adStateOpen Then rs.Close
strRS = "EXEC [dbo].[test_SP] " + "'" + param1 + "', '" + param2 + "', '" + param3 + "'"
cmd.CommandText = strRS
Set rs = cmd.Execute
If rs.State = 1 Then
If Not rs.EOF = True Then
While Not rs.EOF = True
cCol.Add Trim(rs.Fields(0))
rs.MoveNext
Wend
End If
End If
SQL ...
CREATE PROCEDURE [dbo].[test_SP]
@param1
@param2
@param3
AS
CREATE TABLE #RetTbl
(
...
...
)
....
SELECT *
FROM #RetTbl
When i change my SQL-string to "Select * from " eigther within VBS or VBA it works fine, but i need to use the sproc.
The sproc also works fine within query analyser of SQL Server 2000 (SP3).
what is it i'm not seeing - could you pleas help.
thanls in advance