Calling Stored Procedure from an ActiveX task

I'm attempting to call a storedprocedure from within an ActiveX task in
a DTS and am getting a "Command Text was not set for the command
object" error. I have no problem if I replace the stored procedure call
with the actual SQL.

Is it possible to directly call a SP via an ActiveX task? If yes, could
somebody help me with the syntax please?

This is what I have currently have

Set Conn = CreateObject("ADODB.Connection")
SQL = "exec (MySP)"
set RLoop=conn.Execute(SQL)

do while (NOT RLoop.EOF)
msgbox RLoop(1)
RLoop.MoveNext

Loop

TIA

KarthikKarthik (karthiksmiles@.gmail.com) writes:
> I'm attempting to call a storedprocedure from within an ActiveX task in
> a DTS and am getting a "Command Text was not set for the command
> object" error. I have no problem if I replace the stored procedure call
> with the actual SQL.
> Is it possible to directly call a SP via an ActiveX task? If yes, could
> somebody help me with the syntax please?
> This is what I have currently have
> Set Conn = CreateObject("ADODB.Connection")
> SQL = "exec (MySP)"
> set RLoop=conn.Execute(SQL)

Cmd = CreateObject("ADODB.Command")
Cmd.ActiveConnection = Conn
Cmd.CommandType = adStoredProcedure
Cmd.CommandText = "MySP"
Set rs = cmd.Execute

Not that I have ever done it in DTS, but this should be like ADO anywhere
else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx