run a stored procedure from a vb script

[washaw]

Hi,

I am designing a DTS package and in my DTS package I have an activeX script task, I am thinking of writing a vb script,
my script is excuting a stroed procedure 'IsAllDataloaded' wich has input parameter and output parameter of integer type,

my question is how am I able to invoke this procedure from the vbscript

sample code is greatly appreciated

Thanks

 

[AlexCuse]

This is for ASP, but you ought to be able to see what's relevant.

http://www.15seconds.com/issue/010718.htm

You might need a visit to

http://www.connectionstrings.com

as well.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[washaw]

THanks Alex

 

[washaw]

Can any body tell me what is wrong with this script

I spent the whole day to figure it out

error

Error Description: Procedure 'PROC_CHECK_ARCHIVED_DATA' expects parameter '@numDate', which was not supplied.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

   Dim stpContinuePkg
   Dim stpExitPkg
   Dim varNumber
   

   Set pkg = DTSGlobalVariables.Parent
   varNumber = DTSGlobalVariables("numDays").Value

   SET stpContinuePkg = pkg.Steps("DTSStep_DTSExecuteSQLTask_5")
   SET stpExitPkg = pkg.Steps("DTSStep_DTSActiveScriptTask_2")

   Set objCommand = CreateObject("ADODB.Command")
   Set objParam = CreateObject("ADODB.Parameter")
   Set rsYourRecordSet = CreateObject("ADODB.Recordset")
 
   ' set the connection properties to point to the

   objCommand.ActiveConnection = "provider=SQLOLEDB.1;data source=myServer;database=myDatabase;Integrated Security=SSPI;"
   objCommand.CommandTimeout = 300

   objCommand.commandtext = "PROC_CHECK_ARCHIVED_DATA" 
   objCommand.CommandType = 4 
   Set objParm =  objCommand.CreateParameter("@numDate", 3,1, 4,varNumber) 
   objCommand.Parameters.Append objParm      
   Set objParm =  objCommand.CreateParameter("@retVal",3,2,4)
    objCommand.Parameters.Append objParm 

   objCommand("@numDate") = varNumber

   set rsYourRecordSet = objCommand.Execute

   'to access the output, assign to a variable'

       intSprocValue = objCommand.Parameters("@retVal").value


   if intSprocValue = 0 then
      stpContinuePkg.DisableStep = True
      stpExitPkg.DisableStep = False
   Else
      stpContinuePkg.DisableStep = False
      stpExitPkg.DisableStep = True
   end if
	Main = DTSTaskExecResult_Success
End Function

help please for crying out loud


Thanks