DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.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 +'", "' + SUSER_SNAME() + '", "' + @.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

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTS and Interbase/Firebird

I have written several DTS pakages to copy data from an Interbase/Firebird db to SQL Server. They execute under Enterrpise Manager perfectly but when scheduled return an error.

I am aware that permissions are a normal source of these problems but I have hopefully excluded them from teh equation by using a local admin/domain/sysadmin user throughout for SEM, SQLServerAgent and even the SQLServerAgentProxy.

The problem appears to be related to the interaction between the Microsoft OLE DB Provider for ODBC Drivers and the Interbase/Firebird Driver. I have checked several forums and the MSDN website but few people seem to have experienced this type of problem in this environment.

Error msgs:

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Easysoft][InterBase]unavailable database
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

OR

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0

My Setup:

MS SQL Server 2000 with SP3
MDAC 2.8 RTM
Interbase v6 /Firebird 1.03 db Server

Have tried the following driver/connectors:

Firebird ODBC v1.02.00.41
Easysoft ODBC v2.01.00.01
XTG Interbase6 ODBC v1.00.00.15

I have even tried OLE DB providers for Interbase/Firebird but they won't schedule either giving me an SQLCODE=-904

Any insight/help/sympathy appreciated. I have struggled with this single issue for nearly a week.I finally resolved the issue and wanted to post it so that someone else might benefit.

There is a difference between the way Enterprise Manager 'interfaces' with the ODBC connections setup on the machine and the way SQL Server Agent does when a scheduled job is executed. Not entirely sure what the difference is I think that the difference is that SQL Enterprise Manager assumes / defaults to a TCP/IP connection whereas the SQL Server Agent assumes / defaults to a named pipe connection.

So if you are using Interbase make sure you specify a TCP/IP connection in the ODBC setup, e.g. 127.0.0.1:C:\Interbase\db\test.gdb