Problem with Store Procedure - SQL Server Forums

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

Transact-SQL (2000)

Problem with Store Procedure

Author

Topic

viscocesco
Starting Member

5 Posts

Posted - 2005-05-30 : 07:05:31

Hallo, I need to execute a DTS from Stored Procedure in SQL Server 2000.
The DTS has only a task (ActiveX) with a message box.
The code of Stored Procedure is:

CREATE procedure prova_run as
declare @hr1 int, @hr2 int, @hr3 int, @hr4 int,
@oPKG int

--create package
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT

--Load package
EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("localhost", "sa", "sa", 256, , , , "DTS1")', NULL

--execute package
EXEC @hr3 = sp_OAMethod @oPKG, 'exec'

--cleaning
EXEC @hr4 = sp_OADestroy @oPKG
GO

If I run the DTS from Enterprise manager, it goes but not from Stored Procedure.
There are something to set in SQL Server 2000 to run a DTS from stored procedure?

Please Help me!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-31 : 11:11:44

this may help you...

http://www.sqlteam.com/item.asp?ItemID=19595

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources