DTS File Created After Stored Proc executed now what?

I'm trying to fire a DTS package through a stored procedure.

After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.

How would I use the DTS file to update my data.

Thanks,

RB

RB,

There is no direct way to do this via a SP. In my experience, I've seen three ways to work around this:

1) Use xp_cmdshell. This simply executes a command in the command shell. Usefull if you already have a batch script running your DTS.

2) Use OLE. You can create and work with Com objects via the sp_OA* methods. You will need to create the DTS objects and run properties from them.

3) Use a scheduled Job. Saw this recently, the idea is to create a new scheduled job that runs your DTS, then run the job, then delete the scheduled job.

None are pretty, but they all work.

-- Alex

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Mo
you can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>
sql

DTS fails on Stored Procedure warning

Hi,
I am calling a stored proc from a DTS package in SQL 2000 using Execute SQL
step & when I try to execute it from DTS, I get 'failed on execution', but
when I run the stored proc manually, it runs fine, but gives a couple of
warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any ideas why this happens?
Thanks,
Moyou can setup an option when you call a dbcc command
generally there is a "no_msg" (or an option like this; read the BOL) which
disabled output except in case of errors.
"Mo" <Mo@.discussions.microsoft.com> wrote in message
news:72063C0B-8791-4666-90A0-8C7C645C2ADF@.microsoft.com...
> Hi,
> I am calling a stored proc from a DTS package in SQL 2000 using Execute
> SQL
> step & when I try to execute it from DTS, I get 'failed on execution', but
> when I run the stored proc manually, it runs fine, but gives a couple of
> warnings:
> Warning: Null value is eliminated by an aggregate or other SET operation.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Any ideas why this happens?
> Thanks,
> Mo
>

DTS DataPump from a Stored Procedure

Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.

Paulo

Originally posted by andrewsc
Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?

DTS

I need to get a list of tables from a tabl, then loop through the table list and execute a stored proc feeding it the table name as a parameter...

How could this best be done in DTS?

Jim

JimDi wrote:

I need to get a list of tables from a tabl, then loop through the table list and execute a stored proc feeding it the table name as a parameter...

How could this best be done in DTS?

Jim

I think this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possible.
When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possible.When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possibl
e.When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.