Post reply

Execute DTS from a Stored Procedure???

  • March 16, 2004 at 7:51 am

    #59246

    Is it possible to execute a DTS package from a stored procedure without using dbo.xp_cmdshell to execute dtsrun? I have a developer that wants to have his package fire off from an application and we do not want to open up (or in most cases reinstall xp_cmdshell) on the server for security reasons.

    Any help on this one will be greatly appreciated.

  • March 16, 2004 at 8:23 am

    #498963

    You could consider executing from the command line using dtsrunui or dtsrun straight from the command line.

    Books online:

    How to execute a DTS package using the DTS Run utility (Command Prompt)

    To execute a DTS package using the DTS Run utility

    1. Open a command prompt window and type dtsrunui without any command switches.
    2. In the DTS Run and Advanced DTS Run dialog boxes, enter any information for connection settings and logging.
    3. Click OK when you are ready to execute the package.

  • March 16, 2004 at 9:47 am

    #498980

    Thanks for the quick response.

    This would work if the client machine has the SQL Server Client suite installed, but at this time there is no desire to do so.

  • March 16, 2004 at 9:55 am

    #498982

    Not a pretty solution but:

    you could have a table on your sql server that takes an insert statement from the application via ODBC.  Have a job step check the value of the table (if true or false) and depending on the value proceed to the next step of the job which is execute the DTS package.  The job would check every 5 minutes (whatever interval).  This controls the access of the application or developer down to a one table insert.

    ...like I said not pretty, but a solution if you don't get any other suggestions.

  • March 17, 2004 at 5:47 am

    #499096

    Check out http://www.sqldts.com.  This web site shows you how to use the DTS object model to execute packages (using Visual Basic).  We use the methods shown here to kick off packages right from our VBScript applications.  I'm not sure about other languages, but the site may have information on those also.  We switched to this method for the same reasons you are; don't want to give the users access to xp_cmdshell.

    Steve

  • March 17, 2004 at 7:11 am

    #499111

    I wanted to do the same thing, but I didn;t want to use the DTS object model because I wanted to ensure that the DTS package would execute on the database server, not a web server or the client machine.

    After a lot of trial and error with several different methods, someone on this site suggested raising a custom error number and creating an alert that kicks off the DTS package on the server. This has worked out great for me.

    Check out http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=19112#bm90876 for more info...

     

    Chris

  • March 17, 2004 at 9:19 am

    #499144

    You can use some of the extended stored procedures such

    sp_OACreate to create the DTS package, run methods with sp_OAMethod and then dynamically set global variables with sp_OASetProperty 

    This has been successful for me in having a template DTS package then dynamically setting items via a stored proc

     

  • March 17, 2004 at 11:06 am

    #499170

    Another option is to schedule this DTS package to run using the Enterprise Manager. You then disable the job that got created. Then use sp_start_job to run the job.

  • March 17, 2004 at 11:46 am

    #499178

    Is there any way to have the job run on demand by a nonsysadmin without using a proxy account?

    How do you raise an error which starts the job?

  • March 17, 2004 at 12:48 pm

    #499187

    Question 1)

    When you state job you mean a Sql Agent Tasks correct?  i.e. something created with t-sql sp_add_job.

    Not that I'm aware of.

    Check out Gert Drapers presentation to PNWSSQL from July 2003 in the presentation archives of http://groups.msn.com/pnwsql There's a good PPT of how to trouble shoot sql 2k and sql 7.0 sql server agent permissions and job contexts. 

    When you say nonsysadmin do you mean job owner?  A key point is that the job needs to be visible to the caller via the sysjobs_view check 

    ?

    CREATE VIEW sysjobs_view

    AS

    SELECT * FROM msdb.dbo.sysjobs

    WHERE (owner_sid = SUSER_SID())

    OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

    OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)

    Question 2)

    I think the general consensus on this is to have a polling job check data values in a table.  I have heard of some one doing something or another with profiler events, but I haven't tried it. 

    The other problem with this is that it will runas however polling job was invoked.  This is a common request and I don't recall if anybody had a great solution at the meeting.

    Peter Evans (__PETER Peter_)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply