Executing a stored procedure in ActiveX

#1

Hi,

Is it possible to execute a stored procedure in aa ActiveX Script task in DTS? If so, what's the syntax for this? I've looked in the books online and on the internet but can't find any examples or even topics about this.

Thnx.

Stanley

#2

If you MUST do it in activeX code then you will be using the ADO model.

Why do that though when you can use the ExecuteSQL task?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Stanley" <Stanley (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,

Is it possible to execute a stored procedure in aa ActiveX Script task in
DTS? If so, what's the syntax for this? I've looked in the books online and

on the internet but can't find any examples or even topics about this.

Quote:

Thnx.

Stanley

#3

ok, forget the part of the CurrentDB. There are more than one databases in de the CurrentDB/Local. So, logically specifying a databasename is obligated. I'll try with ADO thing for now, but any other solution is welcome.

Thnx,

Stanley

"Stanley" wrote:

Quote:

Well,
what i want to do actually is, executing a bulk insert query (which will be in a SP).

I cannot use the bulk insert task in DTS because there are no settings for using triggers. In a SQL query i can add this property, so that's why i'm using the bulk insert query and not the task. But before running the query i want to check first if the file (csv) exists. If exists i can execute the bulk insert otherwise don't. (Don't of it's possible to program that advanced in sql query to check for existing files)

Because i don't know how to call an SP in ActiveX i just create one DTS package with for each Bulk Insert an AxtiveX Task (for checking if file exists) and an Execute SQL task (for running the bulk insert query). So this means if i have to upload 15 files, i create 15 activex task and 15 execute sql tasks and run them in one sequence using the workflow. If i can call SP in ActiveX i can just create one ActiveX task and to run all 15 inserts.

I actually read something about using the ADO model, but than i believe you have to set a connection (set whole connectionstring...bla bla..). In Access for example, you can do something like:

set dbs = CurrentDB

This way you connect to the local db itself. And you can access all the queries and tables in code. Is there something like this in SQL Server?

Or is there perhaps an easier way to achieve what i want?

Thnx in advance.

Stanley

"Allan Mitchell" wrote:

If you MUST do it in activeX code then you will be using the ADO model.

Why do that though when you can use the ExecuteSQL task?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Stanley" <Stanley (AT) discussions (DOT) microsoft.com> wrote in message
news:5416E73B-AB24-4124-9876-BFAC5298262B (AT) microsoft (DOT) com...
Hi,

Is it possible to execute a stored procedure in aa ActiveX Script task in
DTS? If so, what's the syntax for this? I've looked in the books online and
on the internet but can't find any examples or even topics about this.

Thnx.

Stanley

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

Executing a stored procedure in ActiveX

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

Executing a stored procedure in ActiveX - 07-15-2004 , 05:48 AM

Re: Executing a stored procedure in ActiveX - 07-15-2004 , 06:28 AM

Re: Executing a stored procedure in ActiveX - 07-15-2004 , 07:38 AM