DTS Log/Executing DTS from stored procedure

DTS Log/Executing DTS from stored procedure

Post by Sean Sampe » Sat, 05 Oct 2002 06:28:11



I'm trying out the syntax for a stored procedure in a QA window...

* I'm using the code found at
http://www.sqldts.com/default.aspx?6,104,210,0,1, but ain't nuthin
happening. As a test, I'm just trying to change the value of a global
variable. I run the code in the link above (I'll include it below too), but
the value isn't changing. I'm not getting an error message from Query
Analyzer -- it just reports the commands completed successfully. I'm signed
on using the sa account to be sure privileges aren't a problem. I can
execute the DTS directly from EM, and the value does change.

* Assuming I can get this to work, how do I view the results? Where does SQL
Server store the log? I'm trying to display the results in ASP.

As always, thanks in advance to the souls who monitor this board.

Sean
----------------------------------------------------------------------------
----------------



BEGIN
    PRINT '***  Create Package object failed'

    RETURN
END

 -- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256

  'LoadFromSQLServer("SQLTEST", "", "", 256, , , , "remote exec")',
  NULL

BEGIN
    PRINT '***  Load Package failed'

    RETURN
END



BEGIN
    PRINT '***  Execute failed'

    RETURN
END



BEGIN
    PRINT '***  Destroy Package failed'

    RETURN
END

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

 
 
 

DTS Log/Executing DTS from stored procedure

Post by Allan Mitchel » Sun, 06 Oct 2002 18:19:48


Have a look at this alternate code.  The thing about sp_OA** is that
if you don't trap the errors then you don't see them. They do not
auto display as in most other things.

http://www.allisonmitchell.com/forums/showthread.php?s=3427acf3a
ae17e8c21d52c464d4e6640&threadid=122
--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

1. Executing DTS from stored procedure / missing master db procedures

We recently upgraded from version 7 to 2000 using the upgrade option. We
execute DTS packages from stored procedures using the following code:



SET NOCOUNT ON






/* Load package */



process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



/* execute package */


process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,




process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



Everything works great with the converted server, but when we created our
first named instance of SQL Server 2000, that was not un upgrade, this
stored procedure would not work on the new instance.

After looking at the master database. we discovered that procedure
dt_displayoaerror did not exist.  On further analysis, there were 30 dt_
procedures that did not exist in the pure named-instance SQL Server 2000
installation.

I restored all of our user databases from the converted server to the new
named instance.  All of these user databases contained the 30 dt_
procedures.  Now here is the real mystery.  On Monday morning at 10:04 a.m.,
the 30 dt_  procedures suddenly popped into existence in the master
database.  Neither I or any other member of the staff had anything to do
with it.

Does anyone know why these procedures would not be present on install, but
suddenly pop into existence at a later time?  Are there newer replacements
that should be used instead?

2. 42 and counting

3. executing DTS in stored procedure / missing master db procedures

4. NetDir Setting

5. dts SQL task executing stored procedure

6. seek issue

7. Executing a DTS package from within a stored procedure

8. Install 4GLRT without shutting down informix Instance.

9. execute DTS package from Stored Procedure

10. executing a DTS Package trhu a Stored Procedure

11. Error in Executing DTS package with stored procedure

12. execute dts from a stored procedure?

13. Executing a DTS package within a stored Procedure