calling stored procedure from within VBS / VBA

See more:

SQL

VBA

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                   ' holds complete SQL-String


    sPro = "Provider=SQLOLEDB.1;"
    sUID = "UID=nnnn;"
     sPW = "PWD=xxxx;"
    sDsn = "Initial Catalog=dbTest;"
    sSer = "Data Source=TestMachine"

    ' Connection-String
    sCon = sPro + sUID + sPW + sDsn + sSer

    Set cn = New ADODB.Connection
    cn.Open sCon               ' Open Connection

    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = cn

    If rs.State = adStateOpen Then rs.Close

    ' SQL-String
    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
'''           rs.MoveFirst
           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