×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

run a stored procedure from a vb script

run a stored procedure from a vb script

run a stored procedure from a vb script

(OP)

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

RE: run a stored procedure from a vb script

(OP)
THanks Alex

RE: run a stored procedure from a vb script

(OP)
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.

CODE

'**********************************************************************
'  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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close