Re: Running a DTS Job from a stored procedure

On Mar 20, 7:37 am, Derek <D...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
For the last three years we have been running DTS jobs by executing a stored
proceder. We now want to upgrade our SQL Servers to Windows 2003 server on a
more powerfull server.

The DTS jobs will run manually but we can't get them to run using the stored
procedure method.

The error returned is "LoadFromSQLServer failed" which is row 41 in the
stored procedure listed below.

We are running SQL Server 2000. Does anyone know why we are experiencing
these problems?

CREATE PROC dbo.spExecDTS

@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@User varchar(50), -- User Name
@ServerPWD varchar(255), -- User Password
@IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '' -- Package Password

AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_DisplayOAErrorInfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @User + '", "' +
@ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD +
'", , , "' + @PkgName + '")'

EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_DisplayOAErrorInfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO

--
Derek

Have you tried including something like this?


declare @SQLCmd varchar(6000)

set @SQLCmd = 'dtsrun /S ServerName /U UserName /P Password /N
DTSPackageName'

EXEC xp_cmdshell @SQLCmd


Regards,

Enrique Martinez
Sr. SQL Server Developer

.

Relevant Pages

  • Re: No more indexing on text
    ... Looking for a SQL Server replication book? ... DROP FULLTEXT CATALOG CmpArchivorCat ... exec sp_fulltext_catalog 'CmpArchivorCat', 'create' ... both text/plain and text/html in my file type column. ...
    (microsoft.public.sqlserver.fulltext)
  • Running a DTS Job from a stored procedure
    ... We now want to upgrade our SQL Servers to Windows 2003 server on a ... EXEC sp_DisplayOAErrorInfo @oPKG, @hr ... PRINT '*** Execute failed' ...
    (microsoft.public.sqlserver.dts)
  • Re: Error 21776: After creating new login
    ... EXEC DATA0003.dbo.sp_addlogin 'test','pwd' ... > another SQL Server session is not visible to the SQL-DMO session. ... > Jasper Smith ... >> Users collection. ...
    (microsoft.public.sqlserver.security)
  • Re: Auto truncate a string to be inserted to SQL table
    ... EXEC p --Warning was not returned ... Tibor Karaszi, SQL Server MVP ... >> Tibor Karaszi, SQL Server MVP ... >>> message is given...i might need to just truncate the string without halting>>> the process... ...
    (microsoft.public.sqlserver.programming)
  • Re: Permissions!
    ... be aware that 'dbo' is a special user that has full permissions. ... Database permissions are not checked for the 'dbo' user so it serves no ... You will need individual logins/users so that SQL Server can identify users ... EXEC sp_addrole 'Role1' ...
    (microsoft.public.sqlserver.security)