Execute stored procedure or SQL task from script.

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)

Execute stored procedure or SQL task from script.

Author

Topic

Jason22
Starting Member

3 Posts

Posted - 2008-11-13 : 13:33:53

Hi
Ive been searching everywhere on how to do this, but with no luck.
Basically I have a SQL function that determines whether or not a row must be copied.
It takes 1 parameter and returns another.

Can I call this function from within my transformation script?

I tried adding the function to a 'Execute SQL Task' like this : 'Execute MyFunction ?' which seems to work fine (I will use global parameters for input and output) but how do I execute this SQL tasl from my script?

Any help will be greatly appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 23:23:43

didnt understnad why you want to execute sql task from script task. can you elaborate?

Jason22
Starting Member

3 Posts

Posted - 2008-11-14 : 00:04:41

Hi
How else do I execute my stored proc from within the script?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 00:21:27

quote:
Originally posted by Jason22

Hi
How else do I execute my stored proc from within the script?

something like below will do

Dim cn As OleDbConnection = New OleDbConnection()
            Dim cmd As OleDbCommand = New OleDbCommand()
            cn.ConnectionString = Dts.Variables("User:DBConnection").Value
            cn.Open()
            cmd.Connection = cn
            cmd.CommandText = "yourspname"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("parameter1", DbType.String).Value = "what ever"
            cmd.Parameters.AddWithValue("dateparameter", DbType.DateTime).Value = DateTime.Now
            cmd.Parameters.AddWithValue("parameter_OUT", DbType.Boolean).Direction=               ParameterDirection.Output

            cmd.ExecuteNonQuery()

Jason22
Starting Member

3 Posts

Posted - 2008-11-15 : 01:39:58

Thanks, I will try that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 04:47:15

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources