Re: Running a DTS Job from a stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked
    ... I would still try to import the data into your SQL Server instance instead of running linked server queries as you are doing. ... I can query it with no problem and view tabs in Studio. ... EXEC master.dbo.sp_addlinkedserver ...
    (microsoft.public.sqlserver.programming)
  • 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)