Executing a DTS from ASP.NET w/ no Parameters

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)

Executing a DTS from ASP.NET w/ no Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-15 : 11:09:25

Phillip writes "I'm trying to execute a DTS package from an asp.net page. I read:
http://www.sqlteam.com/item.asp?ItemID=19595
and I was wondering if you can help me understand why it's not working. I have no parameters to pass into the stored procedure. The SP is only importing data from an xls to a table. I'm using Win2003 / Sql 2k / ASP.NET / .NET2. Everything is running on the same box from the OS to IIS to SQL. The SP is called 'delete' and this is the SP code I thought would work. Thanks in advance.
Phil
---------------
Create PROCEDURE usp_Delete

AS
declare @jid uniqueidentifier

-- Specify DTS to be executed
declare @dts varchar(128)
set @dts = 'delete'

-- Initialize command
declare @cmd varchar(4000)
set @cmd = '"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "' + @dts + '" /E '

-- Allow To Run Only One DTS Instance At A Time
Declare @jname varchar(128)
set @jname = 'AspDtsJob'

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'ASP',
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
--@job_name = 'Test',
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid

/* SET NOCOUNT ON */
RETURN"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-17 : 18:35:01

remove this line

@category_name = 'ASP',

pvong
Yak Posting Veteran

58 Posts

Posted - 2007-01-19 : 10:05:21

quote:
Originally posted by russell

remove this line

@category_name = 'ASP',

Russell,
Thanks so much!!! That did fix my problem. Now that I know it works, I noticed that it's just creating a job and then deleting the job after it's done. I have no problem with that, but I was wondering if you could help me expand on this SP.

1) I have some jobs that I have already created manually. How do I get the SP to just run a specific job that I created and NOT delete it after it's done?

2) If (1) is not possible, how do I add a second step to the job from this SP?

Thanks so much again!!!

------------------------------
Using VS2005 / Learning in VB.Net 2.0 / Win2003 / SQL 2k w SP4
Be kind to the newbies because you were once there.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 01:12:45

1. sp_start_job
2. sp_add_jobstep -- just as u are already doing.

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources