Can I execute SP in ActiveX within DTS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Can I execute SP in ActiveX within DTS?

Author

Topic

dogli
Starting Member

32 Posts

Posted - 2002-10-01 : 12:54:25

Hi,

I want to use stored procedures in my ActiveX script task within DTS. But when I parse the code, i get the error message:

Error Code: 0
Error Description: invalid characters

Here's part of the my code which results error:

myDestSQL ="sp_insert_participant" @empid, @classid, @hradminID, @registeredNum
myDestRS.open parSQL, mySourceConn, adOpenKeyset 'Insert participant to table, see stored procedures for detail information

Can anybody help me with this?

I appreciate your help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-01 : 13:03:51

Why not execute your stored procedure as a Execute SQL Task? Unless you need output from the SP in your ActiveX code, it's much easier and more reliable to use Execute SQL tasks.

dogli
Starting Member

32 Posts

Posted - 2002-10-01 : 13:08:26

Thanks, robvolk!

Yes, I do want to get output from the stored procedures, and I also have other block of code to execute some other tasks.

It seems that I can run sp without input parameters, but cannot run it with parameters @....

IT'S BAD IF this is true, I have to use regular sql ...

Again, tks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-01 : 13:58:35

You just need to modify your SQL string to include the values being passed to the procedure:

myDestSQL ="sp_insert_participant @empid=" & empid & ", @classid=" & classid & ", @hradminID=" & hradminID & ", @registeredNum=" & registerdNum

When that gets executed, the values of the parameters will be substituted and the actual string would look like:

sp_insert_participant @empid=1, @classid=2, @hradminID=3, @registeredNum=4

You can also use an ADO Command object and set the parameters using its Parameters collection. There are some examples of that on SQL Team, but if you go to some ASP sites you'll find more:

www.4guysfromrolla.com
www.15seconds.com
www.asp101.com
www.learnasp.com
www.aspalliance.com

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-02 : 03:23:21

This is part of an article I sent to graz about dts - don't know whether it will get puty on this site or not

-- Returning data from a stored procedure
dim objCN
dim objCMD
dim objRS
strConnectionString = "Provider=SQLOLEDB.1;Integrated Security =SSPI;Persist Security Info=False;Initial Catalog=" & DTSGlobalVariables("DatabaseName").Value & ";Packet Size=4096;Data Source=" & DTSGlobalVariables("ServerName").Value
set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open strConnectionString
objCMD.ActiveConnection = objCN
objCMD.CommandText = "spMySp"
objCMD.CommandType = 4 'adCmdStoredProc
objCMD.Parameters.Append objCMD.CreateParameter("OutParmInt",3,2)
objCMD.Parameters.Append objCMD.CreateParameter("InParmStr1",200,1,10,DTSGlobalVariables("StrData1"))
objCMD.Parameters.Append objCMD.CreateParameter("InParmStr2",200,1,255,DTSGlobalVariables("StrData2"))
Set objRS = objCMD.Execute
set objRS = nothing

DTSGlobalVariables("SPOutParm").Value = objCMD.Parameters("OutParmInt")
set objCMD = nothing
set objCN = nothing

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources