Re: DTS Runs from Designer not from JOB or DMO

#1

Have a look at this

http://support.microsoft.com/?kbid=269074

DMO has next to nothing to do with DTS. The DMO error is from the actual
job. DTS has it's own library.

Remember DTS is executed on the client. Therefore everything is as per the
person running things DSNs, Drivers, Location etc etc etc

--
--

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

"Shaun Farrugia" <far!!!!ugia!!!s@dte!!!!ener!gy.com> wrote

Quote:

When i run a DTS package from the designer on the server it runs without
any problem.

When I schedule the package and execute as a job or if i use SQL DMO/COM
to execute the Package2 object i get the following error on the step.

The step is a data transfer from an oracle database to SQL Server
cluster. The oracle connection is the Microsoft ODBC for Oracle ODBC
connection and the SQL connection is the standard SQL OLE DB connection.

MDAC 2.8 is installed on the SQL Server.

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_ENV failed
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

#2

This error here
80004005 says to me that you have either

1. A permissions error
2. You are trying to access something that does not exist.

Do you have the Oracle drivers on the Server ?

--
--

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

"Shaun Farrugia" <far!!!!ugia!!!s@dte!!!!ener!gy.com> wrote

Quote:

I should clarify - we are using SQL DMO/COM from sp_OA TSQL Functions on
the server. So this is still running on the server space.

Here is the code.

select @in_varServerName = @@SERVERNAME
-- ,@in_varPackageName = 'ZainetHeader'

-- LOAD A VARIABLE WITH A NEEDED PROCEDURE CALL..
SELECT @val = 'LoadFromSQLServer("' + @in_varServerName + '", "XXXXXX",
"XXXXX", 0, , , , "' + @in_varPackageName + '")';
-- CREATE THE OBJECT.
EXEC @hr = master..sp_OACreate 'DTS.Package2', @object OUT;

IF @hr <> 0
BEGIN
-- Trapping error if object not created
EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src,
Description=@desc
RETURN
END

-- GET THE DTS PACKAGE REFRENCE.
EXEC @hr = master..sp_OAMethod @object, @Val, NULL;

IF @hr <> 0
BEGIN
-- Trapping error if Execute failed
EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), @src Source, @desc Descr
RETURN
END

-- EXECUTE THE DTS PACKAGE.
EXEC @hr = master..sp_OAMethod @object, 'Execute'
IF @hr <> 0 or @hr IS NULL
BEGIN

-- Trapping error if Execute failed

EXEC master..sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

EXEC @hr = master..sp_OAMethod @object, 'Steps', @stepObject OUT

select @StepObject 'Steps Collection ID'

EXEC @hr = master..sp_OAMethod @stepObject, 'Item', @stepObject2
OUT, '1'

select @stepObject2 'Step Object'
EXEC @hr = master..sp_OAMethod @stepObject2, 'Name', @stepName OUT

select @Stepname 'Step Name'

EXEC @hr = master..sp_OAMethod @stepObject2,
'GetExecutionErrorInfo', @someNum, @errNum OUT, @errCode OUT, @errDesc
OUT, NULL, NULL, NULL

select @errcode, @errNum, @errDesc ' Error from Step Object is', @somenum

exec sp_OADestroy @object
exec sp_OADestroy @Stepobject
exec sp_OADestroy @stepobject2

Allan Mitchell wrote:
Have a look at this

http://support.microsoft.com/?kbid=269074

DMO has next to nothing to do with DTS. The DMO error is from the
actual
job. DTS has it's own library.

Remember DTS is executed on the client. Therefore everything is as per
the
person running things DSNs, Drivers, Location etc etc etc

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: DTS Runs from Designer not from JOB or DMO

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: DTS Runs from Designer not from JOB or DMO - 10-08-2003 , 04:40 PM

Re: DTS Runs from Designer not from JOB or DMO - 10-09-2003 , 10:34 AM