Re: T-SQL stored procedure vs. DTS package scripts

#1

I don't know what planet people come from when they say this stuff is easy.
Of course calling DTS from a stored procedure may be easy relative to many
things such as doing a back flip while in a wheelchair but as far as getting
it working reliably in an acceptable amount of time it is not very simple.
Of course I have little experience with DTS and SQL but figuring out why
things go wrong is a time consumming nightmare that I would not like to
perscribe to anyone.

With all that said what the heck is a "EXECUTE permission denied on object
'sp_OACreate', database 'master', owner 'dbo'." error? and why is in showing
up when I duplicated how it was suggested I execute a stored procedure?

"Nigel Rivett" <sqlnr (AT) hotmail (DOT) com> wrote

Quote:

How are you calling the package from the SP?
If it's using the sp_oa SPs then here is an example of
setting the parameters

http://www.nigelrivett.net/sp_oacrea...TSpackage.html

If it's via dstrun then you can just code the parameters
in the call.
Both pretty simple.

You might want to look at moving the dts functionality
into the SP as much as possible though - often things are
put into the client app just because the developer doesn't
know sql server.

-----Original Message-----
I'm working on a project where I need to have a series of
actions performed
on the server initiated by software on a client. A DTS
package has already
been written to do one of the main steps. I started off
by trying to write a
stored procedure using T-SQL to make various changes on
multiple records in
the database and then call the DTS package. I've been
running into problems
calling the DTS package which takes two global variables
from the T-SQL
stored procedure.

Now after looking at some examples I see that this would
be possible but it
looks like a big pain compared to the code needed in c#
to just call the DTS
package directly. So now I'm considering doing just this
and calling the DTS
package from my code as opposed to a stored procedure
which then calls the
package. One problem is that I don't know much about the
abilities offered
in DTS task scripts. Are there simple constructs for
making queries on a
database and iterating through rows of result sets from
within these
scripts? Is there any performance difference from doing
these things using
T-SQL stored procedure?

TIA

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: T-SQL stored procedure vs. DTS package scripts

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: T-SQL stored procedure vs. DTS package scripts - 07-01-2003 , 03:05 PM