Click here to Skip to main content
14,766,658 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 29-Aug-11 6:55am
v2
Comments
Elina Blank 29-Aug-11 23:05pm
   
Did you set CommandType? I think, default is text, which will explain why select * works. You need to set CommandType to be storedProc

1 solution

Don't use ADODB.Command, try it directly from connection.
cn.Execute("CALL test_SP('" & param1 & "','" & param2 & "'," & param3 & ");")

Ref:http://www.developerbarn.com/visual-basic-programming/2130-call-stored-procedure-vbscript.html[^]
   
Comments
d-as_2010 30-Aug-11 18:23pm
   
Thank You Elina Blank, Prerak Patel

in my first post i forgot to say, that changing the commandtype to "4 := adCmdStoredProc" does not work.

In the mean time i have tested the "direct-way" - with some success. But it only works when commandtype is set "1 := adCmdText".

However leaving the commandtype on "1" seems to work in VBS - i now can get the recordsets i need - but the connection still somtimes fail.

Please forgive my poor english and thanks again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900